on March 18, 2022
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.
Why now?
The missing indexes feature has been around in SQL Server since (looks at watch) SQL Server 2005. Seventeen years later, we’ve added a new article on using the feature. Why?
- Our partners in Microsoft Customer Service and Support let us know that we needed more guidance for customers in the documentation on using missing indexes. (Thanks to Joseph Pilov.)
- I found that older guidance on using the feature had been archived. (How did I find the archived content? A link from one of my old blog posts. Blogging is forever my long-term memory.)
- The older guidance was good, but didn’t mention some of the gotchas that can come with working with missing index requests – things like only one missing index request scripting from a query plan in SSMS, even if multiple requests were present in the query plan XML.
- New features like Query Store have been released since the archived guidance was written. Query Store helps persist missing index requests across index maintenance, restarts/failovers, recompiles, and memory pressure.
All this considered, it made sense to use the archived guidance as a reference to put together a new article on using missing index requests to tune queries.
What else may come?
I’d like to write a complimentary article on tuning indexes with the Query Store. This will be an example of looking at a workload in Query Store, and using information both from missing indexes and from the execution plans to tune queries.
Learn more
Coincidentally, Erik Darling also published an article on missing indexes this week. Check out Understand Your Plan: Missing Index Requests.
Got suggestions for the missing indexes article?
If you see something you’d like to change on the missing indexes article, there are two options to share it:
- Scroll down to the bottom of the article to the “Submit and view feedback” box. Select “This page”, and fill out an issue with your suggestion.
- Make a suggestion in a pull request. To learn how, see How to contribute to SQL Server documentation.
REGISTER