A Fine Slice Of SQL Server
Erik 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 closedRemove CONVERT/CAST from your WHERE clauses and JOINS when comparing to variables of different data types. Set their data types to match your table definitions before using them as a filter. Optimizing your queries this way will greatly reduce the amount of CPU time, reads, and I/O generated in your queries and allow your code to take better advantage of indexes.
This can quietly be a major performance issue.
Comments closedTibor Karaszi thinks about plan ages:
So what if your oldest plan is 3 only days old? Or 3 hours? Or 3 minutes?
Finding your most expensive queries based on such short “tail” is pretty meaningless and you end up wasting time tuning a set of queries that in the end wasn’t the most important ones, after all.
This isn’t necessarily something we always think about but Tibor makes a great point here.
Comments closedErik Darling plugs in the variables:
I’m a really big fan of using operator properties for a lot of things, at least visually. Where things sort of fall down for that is copying and pasting things out.
For some stuff, you still need to head down to the XML.
Click through to see how. Also read the comments for more information.
Comments closedPaul White begins a new series:
This is the first part of a series about simple parameterization and trivial plans. These two compilation features are closely connected and have similar goals. Both target performance and efficiency for workloads frequently submitting simple statements.
Despite the “simple” and “trivial” names, both have subtle behaviours and implementation details that can make how they work difficult to understand. This series doesn’t dwell too long on the basics but concentrates on less well-known aspects likely to trip up even the most experienced database professionals.
In this first part, after a quick introduction, I look at the effects of simple parameterization on the plan cache.
Read on for more detail.
Comments closedKendra Little announces a new article:
We’ve just published a new article in the SQL docs, Tune nonclustered indexes with missing index suggestions . The article explains what the missing index feature is, limitations of the feature, and how to use missing index DMVs and missing index suggestions in Query Store to tune indexes.
Read on to understand the impetus behind this article and then go check it out yourself.
Comments closed