A Fine Slice Of SQL Server
Kendra 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 closedErik Darling made me rhyme. First up, data retrieval via seek:
People. People complain. People complain about SQL Server. That’s probably why I get paid to deal with it, but whatever.
One complaint I get to hear week in and week out is that SQL Server isn’t using someone’s index, or that there are too many index scans and they’re slow.
That might actually be a composite of like twelve complaints, but let’s not start counting.
Erik gives us three reasons why we might not see a seek. But wait, there’s more!
I’m not sure why scans got such a bad rap. Perhaps it’s a leftover from the Bad Old Days© when people worried about logical fragmentation and page splits.
What I mean to say is: scans are often treated with a level of revulsion and focus that distracts people from larger issues, and is often due to some malpractice on their part.
This is true–scans aren’t inherently bad and Erik gives us a better mental model to work with.
Comments closedKendra Little has a guide for us:
We’ve recently updated the SQL Server and Azure SQL index architecture and design guide. This article is an in-depth guide to indexing in databases using the SQL Server engine, including SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.
Our recent update adds a table to categorize the types of indexes discussed in the article, clarifies B-trees vs B+ trees, and describes how row locators (aka “secret columns”) are used in nonclustered indexes.
Read on for more information and do check out the guide.
Comments closedEitan Blumin looks at filtered indexes:
In fact, absolutely no functions of any kind can be used within the WHERE clause of a filtered index. Not even schema-bound user-defined scalar functions.
Unfortunately, as stated in the Microsoft Docs page about Filtered Indexes, the WHERE clause of a filtered index can only support simple comparison operators.
Well, it’s not entirely true, as you CAN actually use some functions, but on two conditions:
Read the whole thing. Eitan lays out one limitation of filtered indexes and provides a couple of potential workarounds.
Comments closedGreg Dodd gives it away in the title:
I needed to add a Unique Constraint today to a table. We could go ahead and just add a constraint, but the data I wanted to constrain on was already indexed. Could I just make that a Unique Index and be done with it? Let’s find out with the following table:
Let’s, shall we?
Comments closedDavid Alcock wants to know what choice you make when all choices lead to the same conclusion:
As there is an index that has the LastName and FirstName columns the optimiser has opted for an index seek operator using the IX_Person_LastName_FirstName_MiddleName index, and if I look into the Plan XML I can see that it’s using a trivial plan: StatementOptmLevel=”TRIVIAL”.
This basically means there’s one obvious way to return the query results so the optimiser has avoided the cost of going through full optimisation and has elected to use this plan straightaway.
So what happens if I create an identical copy of that particular index, in fact let’s create five indexes that are exactly the same:
There’s a Mass Effect 3 joke in my intro line. But read on for the answer and also check out Barney Lawrence’s comment for a bit more elucidation.
Comments closedBrent Ozar explains that sometimes you’ll need to slap a DESC on your index columns:
The short answer is that if your query orders columns by a mix of ascending and descending order, back to back, then the index usually needs to match that same alternating order.
Now, for the long answer.
Do read on for the long answer, however.
Comments closedGrant Fritchey gets our hopes up:
Everyone knows that you only get a single clustered index, right? Wouldn’t it be great though if you could have two clustered indexes?
Well, you can. Sort of. Let’s talk about it.
Click through to see what Grant means. This is a thing that I’ve done occasionally, though much more often, I’ve ripped it out because Database Tuning Advisor suggested it and a credulous user took DTA at its word.
Comments closedAaron Bertrand has learned a thing or two about filtered indexes:
Confession time. For filtered indexes, I have long held the following impressions:
1. That the definition of the filter had to exactly match the predicate in the query.
2. That
col IN (1, 2)was not supported in a filter definition, since it’s the same ascol = 1 OR col = 2, which is definitely not supported.
If I were to take a wild guess, I’d think impression 1 was probably influenced by the extreme limitations filtered indexes have with parameterized queries. Anyhow, read the whole thing and learn why both of these are wrong.
Comments closedEitan Blumin explains what hypothetical indexes are and why they might be useful:
Using Hypothetical Indexes, you can generate an estimated execution plan for a given query, that would essentially assume the existence of a “hypothetical” index as if it actually exists as a real index. Compare that estimated execution plan to its counterpart without the hypothetical index, and you’ll be able to determine whether creating this index for real is worth the time and effort.
Hypothetical Indexes are actually nothing new in SQL Server. It existed since SQL Server version 2005. However, its use is still not widespread to this day. Most likely because it’s not very easy to use and the relevant commands are undocumented.
Click through to see how to use them and an important warning if you try it in production.
Comments closed