on September 20, 2016
I recently mapped out my thought process for how I approach a new instance of SQL Server when it comes to index tuning.
It now looks like this:
Highlight: Can I use Query Store?
One of the first things I think about is whether the new 2016 Query Store option is available to collect query runtime statistics and execution plans. Information on query duration, reads, cpu use, and execution plans are so critical to index tuning that I care a ton about this new feature.
And it is a new feature. It’s even had its first big bugfix – if you’re running it on something other than Enterprise or Developer Edition, make sure you’ve tested and installed CU1, which contains this fix for query store cleanup.
I’m a big fan of SQL Server’s plan cache and index management dynamic management views – but I love that Query Store takes away the mystery of wondering what might be missing from the cache, or which missing index requests might have been cleared by an index rebuild.
Observation: Tuning indexes is most effective when you analyze the top execution plans to design your indexes – not the missing index DMVs
When I first began tuning indexes in SQL Server, I largely reviewed and followed missing index suggestions in the missing index DMVs. I learned to combine those suggestions together and with indexes on the tables.
My tuning style has evolved from this, though. SQL Server’s index recommendations are useful, but they’re very rough - sometimes they suggest columns for the includes which you don’t absolutely need. Sometimes they suggest a column as an include that would be better in the key. Sometimes they overestimate the benefit the index would provide. And sometimes you just don’t get a suggestion at all.
It’s not that the Missing Index feature doesn’t work, it’s simply that the missing index feature is not designed to fine-tune an index workload. And that’s totally fair - those index requests are generated during query optimization, and that’s definitely something that we want to be fast!
What I very much prefer these days is to look at the top running queries during the periods I want to tune. I like to examine the execution plans and CPU, reads, and duration for the statements along with the plans.
I do still like to look at missing index suggestions, I just prefer to do it in the context of the plan asking for it.
The reason that I love the whole concept of Query Store is that it’s a built in way to make this a whole lot easier!