I often find myself reviewing missing index requests during consulting engagements. Not because they’re so awesome, but because they’re often just good enough to provide some quick relief before more fine-tuned efforts are explored.
More to the point: if someone has no idea which queries they need to tune, and everything is pretty slow, this is a good starting place.
Given sufficient server uptime, of course.
Sometimes you’ll see that slam-dunk missing index request with lots of uses, and you can tie it to a query that you know is bad. Of course, I’m quite partial to using sp_BlitzIndex to analyze indexes. There are a few different places that missing indexes will be detailed in.
- Mode 0: the most important stuff
- Mode 4: anything and everything
- Mode 3: just missing index requests
- Table Mode: analyzing just one table
The easiest way to find examples like I’ll be talking about is to look at just one table. In this case, the Posts table.
EXEC sp_BlitzIndex @TableName = 'Posts';
If you have missing index requests for a table, they’ll look something like this:
That estimated benefit number is pretty big here, so it jumps out a bit. Normally I don’t start really paying attention until that number is >5 million. That’s not terribly scientific, but you have to draw the line somewhere.
Of course, one very sneaky thing to consider is when you have a set of duplicative requests with low-ish estimated benefit individually, but combined they just might add up to something quite useful.
Just A Kid
The estimated benefit number is just a function of the three feedback metrics that get logged with missing index requests: uses * impact * average query cost.
Uses is a fairly reliable metric, but impact and average query cost are a little more hand-wavy. Even high-cost queries can be very fast. It doesn’t mean that they can’t be tuned or don’t need indexes, but they might not be your worst-performers.
In tomorrow’s post, we’ll look at that, and how you can come up with a good-enough index for a bunch of similar queries.
Thanks for reading!