'This post is part of a series on this blog that will explore SQL Server Service Broker, a native messaging and queueing technology built into the SQL Server Database Engine.
Previous posts:
I kind of left you hanging, didn’t I? I brought you right to the edge of having a working Service Broker implementation and then… nada. Yeah. Sorry ’bout that. But my OCD side has brought me back to finish what we started here. So, without further ado, let’s talk about Service Broker security.'...
http://colleenmorrow.com/2017/11/16/sql-server-service-broker-security/?utm_source=DBW&utm_medium=pubemail
'I came across this in a code review not long ago:
Three things pop into my head when I see this sort of thing:
It’s the last item in that list I want to talk about first, because the SQL standard specifies rules for comparing padded and unpadded strings that make that RTRIM unnecessary.
For one thing, the LEN function could care less about trailing spaces. It ignores them. Pays no mind whatsoever. Just watch...'...
http://www.sqlservercentral.com/blogs/the-monday-morning-dba-/2017/11/13/spaced-out-strings/
'I had someone ask me about this the other day. Specifically getting variable data out of a dynamic SQL statement. I should point out here that I really enjoy dynamic SQL. I find it fairly intuitive and always enjoy the challenge, so I’ve gotten pretty good at it. So I just kind of threw an answer out there. Unfortunately, that didn’t clear things up. So I had to find another way to explain the subject. Which brings us to the teaching/blogging advice of the day. This person is very senior, knows SQL far better than me in a lot of areas. Dynamic SQL just isn’t one of them. The moral being, never be afraid to blog about a subject no matter how easy it sounds to you. Just because it’s easy to you doesn’t mean it makes sense to someone else.
Getting back on topic, the discussion I had with them gave me an idea for (what I think) is a really easy way to understand how to use input and output variables with dynamic SQL.'...
http://www.sqlservercentral.com/blogs/sqlstudies/2017/11/16/variables-in-dynamic-sql/
'Usually, when people talk about the performance of JSON in SQL Server, they are starting with “JSON is not actually a native type, it is just a plain text” and imply that it is probably too slow. It is true that JSON is stored as NVARCHAR and that there is no special type like for XML or Spatial. However, it does not imply that performance is worse than performance of full-blown types. NVARCHAR is not a plain text as someone would assume – it is probably the most optimized non-scalar type in SQL Server. JSON leverages simplicity of NVARCHAR and this might be one of the biggest strengths and not weaknesses. In this post, I will compare performance of XML and JSON in a simple scenario – accessing a field on a specified path, and show that paring JSON plain text might be 10x faster than using native types.'...
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/11/13/json-parsing-10x-faster-than-xml-parsing/?utm_source=DBW&utm_medium=pubemail
'Indexes are a vast subject and well documented elsewhere on the web. But, it is still a very commonly asked topic. In order to improve your queries (applications) and databases performance, we create indexes.
But those created indexes may not be very useful over the period because of your application requirement changes, the queries against the data change, the structure of the database and tables changes, the data in the table change. Sooner or later you will find that Indexes that once helped performance now they are not so effective because of change in applications and databases.
Ultimately, SQL Server will start recommending you to create more indexes (missing indexes) to get the blazing performance of the queries.
There could be other scenarios like – your company is expanding the business/sites, they want to launch a couple of new applications, and those applications need more indexes on the table. Again you may end up with having more indexes.
Considering above the scenarios – if you don’t understand how to leverage the existing indexes by aligning them, you may end-up with over-indexing on the tables and an OLTP data tier.
How can over-indexing hurt your SQL server performance?
The reason that having too many indexes is a bad thing because it dramatically increases the amount of writing that needs to be done to the table.'...
http://www.sqlservercentral.com/blogs/sql-geek/2017/11/05/over-indexing-can-hurt-your-sql-server-performance/?utm_source=SSC&utm_medium=pubemail
'The last several releases of SQL Server have introduced a slew of new features, as well as improvements in existing functionality. One area of the engine which is easy to overlook is statistics. After all, statistics still get created the same way, they still tell you about the distribution of data, they’re still used by the Query Optimizer… what’s different? The basic function of statistics remains the same – but how they’re used by the Query Optimizer does change depending on the Cardinality Estimator you’re using. There are also several noteworthy changes related to updating statistics and new functionality has been added around viewing statistics information. Altogether, these changes across the latest releases can cause a variation in SQL Server behavior that you weren’t expecting.
Note: This post is most applicable to SQL Server 2012 and higher, but some detail for prior releases is included for reference (and fun).'...
https://sqlperformance.com/2017/10/sql-statistics/updates-to-statistics
'Welcome to PASS Summit 2017!
Today, we’re pleased to announce the release of a new version of Power BI Report Server. This release contains all of the features we first introduced in the August 2017 Preview, and several new features customers have been anxiously waiting for, including support for scheduled data refresh, Direct Query, and a new REST API for developers. This is an update to the June 2017 release and is supported for production workloads, so you should feel free to upgrade your production environment to take advantage of all of these new capabilities.
Download Power BI Report Server'...
https://powerbi.microsoft.com/en-us/blog/new-version-of-power-bi-report-server-now-available/?WT.mc_id=AID640961_EML_5313053
'Your boss or client came to you and said, “Give me a quick health check on this SQL Server.”
Step 1: Download & run sp_Blitz.
Go to our download page and get our First Responder Kit. There’s a bunch of scripts and white papers in there, but the one to start with is sp_Blitz.sql. Open that in SSMS, run the script, and it will install sp_Blitz in whatever database you’re in. (I usually put it in the master database, but you don’t have to – it works anywhere.)
Then, run it with these options:
|
|
EXEC sp_Blitz @CheckUserDatabaseObjects = 0, @CheckServerInfo = 1;
|
These two parameters give you a server-level check without looking inside databases (slowly) for things like heaps and triggers.
Here’s what the output looks like (click to zoom):
'Today at PASS Summit 2017, we are showcasing new advances across SQL Server 2017 and our Azure data services. And we’re demonstrating how these products – both on-premises and in the cloud – come together to form the ultimate hybrid data platform. Our recent announcements, here at PASS Summit and at Ignite in September are great examples of how we’re empowering data professionals – like our community here at PASS Summit 2017 – to do more and achieve more.
With the recent launch of SQL Server 2017, the first database with AI built-in, you can now run your production workloads on Linux, Windows, and Docker. SQL Server 2017 delivers a mission-critical database with everything built-in, on the platform of your choice. And, it can unlock seamless DevOps with Docker Enterprise Edition containers, bringing efficiency and simplicity to your innovation. New features enable analysis of graph data, and advanced analytics using Python and R. We have incorporated your feedback to add features that will make your day-to-day job easier like Adaptive Query Processing and Automatic Plan Correction by finding and fixing performance regressions automatically.'...
https://blogs.technet.microsoft.com/dataplatforminsider/2017/11/01/sql-server-2017-and-azure-data-services-the-ultimate-hybrid-data-platform/?MC=SQL&MC=MSAzure&MC=DataMgmt&MC=CloudPlat
