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 StackOverflow.com 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 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.