A Fine Slice Of SQL Server
While working with some poorly performing code in T-SQL that used a PIVOT operator, I wondered if there was a more efficient way to get a result set of pivoted data. It may have been a fool’s errand, but I still wanted to try. It dawned on me that I could use the STRING_AGG() function to build a delimited list of pivoted column names and values. From there, I’d have to “shred” the delimited data to rows and columns. But how?
Read on to see how.
Comments closedAaron Bertrand splits a string:
I have long advocated avoiding splitting strings by using table-valued parameters (TVPs), but this is not always a valid option; for example, PHP drivers do not handle this feature yet. A new pattern I’ve seen emerging is to replace splitting comma-separated strings with the new OPENJSON functionality introduced in SQL Server 2016. I wanted to explore why this is not an improvement in the typical case, unless you are using a client application platform that doesn’t support TVPs and your application data already happens to be in JSON format.
Read on for the solution, which has a somewhat surprising outcome.
Comments closedDennes Torres compares several strategies for querying data stored in Azure Blob Storage:
In the third part of the series Querying Blob Storage with SQL, I will focus on the performance behaviour of queries: What makes them faster, slower, and some syntax beyond the basics.
The performance tests in this article are repeated, and the best time of the queries is recorded. This doesn’t mean you will always achieve the same timing. Many architectural details will affect the timing, such as cache, first execution, and so on. The timing exposed on each query is only a reference pointing to the differences of the query methods that can affect the time and the usual result for better or worse performance.
Click through to see which patterns perform well and which don’t.
Comments closedErik Darling unwraps a riddle:
I had a RABID FAN ask me an interesting question about a query. I can’t use theirs, but I can repro the question.
The question was: if using ISNULL in a where clause isn’t SARGable, how come I can use a FORCESEEK hint in this query?
Click through for the answer.
Comments closedDavid Fowler has a warning about stored procedures which use temp tables created by other processes:
Here’s an interesting issue that recently came up. We were seeing very high compilations and recompilations on a server to the point that it started causing us some very serious issues (admittedly this wasn’t the sole issue but it was certainly a contributing factor, the other factors were also very interesting so I might look at those in another post).
After looking in the plan cache we could see a very high number of single use plans for a particular stored procedure. Now as you probably know, SQL will usually cache an execution plan and use it over and over whenever a particular query runs. SQL’s lazy and it doesn’t want to bother compiling queries unless it really has to.
So what was going on, why wasn’t SQL able to reuse the cached plan?
Read on for the solution, as well as the impact of the problem and ways to work around it.
Comments closedErik Darling wants you to use a coaster:
What was puzzling them was that nothing in the stored procedure did anything with temporary objects, cursors, or any of the other obvious things that go to el baño público. And the query plan itself was a pretty simple little loopy thing.
It wasn’t until I grabbed a quick sample of actual execution plans with Extended Events that the problem became more obvious.
Despite the fact that the query was only passing around 7 rows, we were getting a one row estimate, and there was a spilling Sort operator.
Click through for an example of what happened, as well as how you can fix it.
Comments closedDespite the many metric tons of blog posts warning people about this stuff, I still see many local variables and optimize for unknown hints. As a solution to parameter sniffing, it’s probably the best choice 1/1000th of the time. I still end up having to fix the other 999/1000 times, though.
In this post, I want to show you how using either optimize for unknown or local variables makes my job — and the job of anyone trying to fix this stuff — harder than it should be.
Click through for two methods, both of which end up being the wrong answer.
Comments closedErik Darling has a compendium for us:
No one ever says a broken record is right twice a day, perhaps because DJs are far more replaceable than clock makers.
I say that only to acknowledge that I may sounds like a broken record when I say that when you’re tuning a query, it’s quite important to compare wall clock time and duration. Things you should note:
Click through for those things.
Comments closedChad Callihan shares some advice:
In my previous blog post, we saw how using INCLUDE to add a column to an index can make a difference compared to a key column. Let’s do a quick overview of INCLUDE and when it should be used.
Included columns are columns that can added to an index as non-key columns. They are only added to the leaf nodes of an index and have a bit more flexibility. Having trouble adding a particular data type to an index? Included columns can be data types unable to be added as key columns. Are you possibly maxed out on index key columns? Use INCLUDE to add any necessary columns.
Read on for an example and note the warning that you shouldn’t just add all of the columns to the INCLUDE clause.
Comments closedErik Darling wants you to perform a sanity check:
There are times when a single key column index can be useful, like for a unique constraint.
But for the most part, outside of the occasional super-critical query that needs to be tuned, single key column indexes either get used in super-confusing ways, or don’t get used at all and just sit around hurting your buffer pool and transaction log, and increasing the likelihood of lock escalation.
Read on for Erik’s full point. Sometimes that single-column non-clustered index really does do the trick—as in a unique key constraint, or a single column used in a really commonly-used EXISTS clause—but it’s worth thinking about whether that one column is really all there is.