SQL Tuning: Finding statements for missing indexes

Missing indexes are an important part of the indexing strategy. I usually start with sys.dm_db_index_usage_stats to find both inefficient and unused indexes and then supplement with missing indexes.

The missing index DMVs are great but they’ve always been missing something.

What are they missing you ask? They currently tell you what table they are for but not what query. How do I know if the queries that sponsored this missing index are business critical or not? Wouldn’t it be nice to know what statements caused this “missing index” to appear?

You may or may not know that you can use xquery to query your xml query plans from cache. If you’re already familiar with this concept then this should be really quick to understand. If you’re not, don’t worry. Xquery is really simple.

We can query our cached plans by using the sys.dm_exec_cached_plans DMV. Joining sys.dm_exec_query_plan gives us the XML query plan and we can then query within that XML with the following statement. This example looks for plans that have a missing index for the address table.


Want the full query? Grab it from my TechNet Gallery here:


It’s worth a mention that I rewrote a query from a blog post Jonathan Kehayias did on implicit conversions to get the missing indexes. I wonder what fun you’ll come up with by searching the XML plans?

One thought on “SQL Tuning: Finding statements for missing indexes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s