asked
Determine query causing missing index
Hello, On our production server, a missing index query (from
http://sqlserverperformance.wordpress.com/2011/01/06/sql-server-index-tuning-for-mere-mortals/) shows one particular missing index which would be a good candidate from which to create an index. The user seeks in the order of tens of million, average user impact in the high 90s. Before I do this, I would like to know precisely what queries are running that would benefit from this index. What are some ways to find these queries? Do the dmvs record which queries are associated with the missing indexes? Is there a way in Profiler to capture queries using the field in the index (other than using TextData filter)?
I talk about this in a blog post. There is no way to connect directly from the missing index DMOs to queries. Instead, you need to query against the missing index information in the execution plans. You can see an example query here:
http://www.scarydba.com/2009/03/02/more-refinements-on-the-missing-indexes-query/
8 People are following this question.
