Single-Column-Key Missing Index Recommendations are Usually Wrong.


When you’re looking at an index recommendation – whether it’s in an execution plan or the missing index DMVs – it helps to understand Clippy’s blind spots.

Let’s start with the small StackOverflow2010 database so you can follow along. (It’s just a 1GB direct download, and it expands to a 10GB database that reflects circa 2010.) With no nonclustered indexes present yet, run this query:

The execution plan pipes up with an index recommendation, but it’s terrible, because it would double the size of our table, including every single column:

And here’s the kicker: note that the only key in the index is Location. Clippy says, “Oh, you don’t need to sort them by DisplayName – I’ll take care of that for you.”

After we create Clippy’s notoriously bad index, sure, he uses the index – but then he turns right around and sorts all of the results by DisplayName, every single time the query runs:

Clippy’s all out of ideas

Clippy says, “Man, I just have no idea how I might possibly make this query go faster. If you need me, I’ll be over here manually sorting rows. I wanna make sure you get the most of your $7,000 per core licensing for Enterprise Edition, so a-sorting I go!”

When you see an expensive sort operator in a plan, sure, you could just hover your mouse over that Sort, look at the “Order By” at the bottom of the tooltip, and use that knowledge to craft a better index than Clippy came up with.

This stuff is easy to spot in plans,
but less easy to see in the DMVs.

When you’re looking at a query plan with a ridiculous index suggestion like that, it’s easy to say, “Yeah, Clippy’s been drinking on the job again.”

However, when you’re looking at the output of the missing index DMVs – especially with a tool like sp_BlitzIndex – I’ve seen a lot of folks say, “Well, that must be the right index.”

Here’s an easy way to improve Clippy’s index recommendations: when you see a single-key recommended index with just 1-2 included columns, think about moving those includes to the key. In most cases, it doesn’t cost that much more in terms of index space or performance, and it can eliminate those extra pesky sorts even before you spot the query plans involved.

And if you see a single-column key with a ton of includes, it likely means someone’s doing a SELECT *. Clippy’s just throwing everything in the include – but you might actually need some of those columns to be sorted, too.

We’ll be talking about how to do that in this week’s Mastering Index Tuning class.

Previous Post
Building SQL ConstantCare®: You Should Probably Learn Power BI. I Did.
Next Post
Locks Taken During Indexed View Modifications

2 Comments. Leave new

  • Chris Harshman
    September 4, 2018 1:14 pm

    When I first read about the missing index features of SQL Server years and years ago, I always took its recommendations with a grain of salt… I think now I’m up to about a pound of salt.

  • […] Single-Column-Key Missing Index Recommendations are Usually Wrong Ah, it’s very common. But if you still learn about indexes – check why not worth (in many cases) taking SSMS’ advises seriously and asking sp_BlitzIndex instead. […]


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.