A Fine Slice Of SQL Server
Erik Darling shows a rare case in which a view can improve performance:
They may have read, of course, that SQL Server’s super-smart variable length data types only consume necessary space.
It’s free real estate.
Except it isn’t, especially not when it comes to query memory grants.
I’m so used to views messing up performance rather than improving them.
Comments closedItzik Ben-Gan shares some good advice:
A supporting index can potentially help avoid the need for explicit sorting in the query plan when optimizing T-SQL queries involving window functions. By a supporting index, I mean one with the window partitioning and ordering elements as the index key, and the rest of the columns that appear in the query as the index included columns. I often refer to such an indexing pattern as a POC index as an acronym for partitioning, ordering, and covering. Naturally, if a partitioning or ordering element doesn’t appear in the window function, you omit that part from the index definition.
But what about queries involving multiple window functions with different ordering needs? Similarly, what if other elements in the query besides window functions also require arranging input data as ordered in the plan, such as a presentation ORDER BY clause? These can result in different parts of the plan needing to process the input data in different orders.
Read on for six tips. By the way, if you see broken images on the page (which I saw at the time of posting), click the broken image icon to see the image. It appears that the problem is just with inline images.
Comments closedErik Darling tries something out:
I think it was sometime in the last century that I mentioned I often recommend folks turn on Forced Parameterization in order to deal with poorly formed application queries that send literal rather than parameterized values to SQL Server.
And then just like a magickal that, I recommended it to someone who also has a lot of problems with Local Variables in their stored procedures.
They were curious about if Forced Parameterization would fix that, and the answer is no.
Shot down by the third paragraph of the intro. That’s rough. Still, click through for the demo.
Comments closedAndrea Allred notes a potential performance issue:
Why is this query slow? If this query were to return 50 rows, it would run each query in the SELECT clause 50 times, and since there are two of them, that is 100 query runs. What if I returned 100,000 rows? That would be 200,000 query runs. How could I do this differently?
Read on for the answer.
Comments closedFrancisco looks at index spools:
When we are analyzing execution plans, we may come across different types of Spool operators – Table Spools, Row Count Spools, Window Spools or Index Spools – that the Query Optimizer chooses for specific purposes. In this post we are going to briefly look into the Index Spool, how it can sometimes lead to suboptimal query performance, and what can be done to easily fix it.
My favorite description of this is Erik Darling’s: spools are SQL Server’s passive-aggressive way of telling you “I’m not saying you need an index but you need an index.”
Comments closedErik Darling runs through an exercise:
The more columns you have in a table, the more potential column combinations there are for indexes. Much like columns, indexes tend to get added following the path of least resistance.
Very rarely does someone consider current indexes when deciding to add an index.
Erik’s process is a good one. The real pain comes when there are 40-50 indexes on a table (seriously…) and there are a lot of similar-but-not-quite-similar-enough indexes.
Comments closedPaul White continues a series on simple parameterization and trivial plans:
It’s more complicated than you might expect to tell from the information provided in execution plans if a SQL statement uses simple parameterization. It’s no surprise even highly experienced SQL Server users tend to get this wrong, given the contradictory information often supplied to us.
Let’s look at some examples using the Stack Overflow 2010 database on SQL Server 2019 CU 14, with database compatibility set to 150.
Read on for four classes of outcome and several ways you can determine into which your queries belong.
Comments closedItzik Ben-Gan has an interesting challenge for us:
The challenge is as follows:
Assuming partitioning based on the column grp and ordering based on the column ord, compute sequential row numbers starting with 1 within each consecutive group of rows with the same value in the val column. Following is the desired result for the given small set of sample data:
Click through for an example of this, a pair of “classic” solutions, and Itzik’s solution.
Comments closedTom Zika digs into a query hint:
Like Dwarves of Moria, I delved too greedily and too deep. I found the answer in a Plan Caching and Recompilation in SQL Server 2012 whitepaper before trying it out on my own. Relevant excerpt:
KEEP PLAN
The KEEP PLAN query hint changes the recompilation thresholds for temporary tables, and makes them identical to those for permanent tables. Therefore, if changes to temporary tables are causing many recompilations, this query hint can be used.
But since it doesn’t show the proof, I decided to test it out anyway.
And it’s a good thing, too, as it turns out this isn’t quite how things work anymore.
Comments closedPaul White continues a series on simple parameterization:
The details of how each data type is guessed are complex and incompletely documented. As a starting point, SQL Server infers a basic type from the textual representation of the value, then uses the smallest compatible subtype.
For a string of numbers without quotation marks or a decimal point, SQL Server chooses from
tinyint,smallint, andinteger. For such numbers beyond the range of aninteger, SQL Server usesnumericwith the smallest possible precision. For example, the number 2,147,483,648 is typed asnumeric(10,0). Thebiginttype isn’t used for server-side parameterization. This paragraph explains the data types selected in the prior examples.
Read the whole thing to see what’s in and what’s out, as well as what this all means.
Comments closed