patternsqlModerate
Finding the query causing the missing index DMVs
Viewed 0 times
thequeryfindingdmvscausingmissingindex
Problem
How can I find the query causing the missing index DMVs suggesting to create a particular index?
Thank you.
Thank you.
Solution
Short answer: you can't with 100% accuracy.
Long answer: you can query the plan cache to identify plans with missing index warnings and compare the results with what you find in sys.dm_db_missing_index_* DMVs. Here's a script that you could use to query the plan cache. If the plan doesn't get cached or gets pushed out the cache for any reason, you won't find any matches, so we're back to the short answer.
To be 100% sure, you would have to trace the workload and capture the plans with missing indexes. It's not lightweight and it's not trivial. Here's a script to do that. With a small effort it could be ported to Extended Events.
Long answer: you can query the plan cache to identify plans with missing index warnings and compare the results with what you find in sys.dm_db_missing_index_* DMVs. Here's a script that you could use to query the plan cache. If the plan doesn't get cached or gets pushed out the cache for any reason, you won't find any matches, so we're back to the short answer.
To be 100% sure, you would have to trace the workload and capture the plans with missing indexes. It's not lightweight and it's not trivial. Here's a script to do that. With a small effort it could be ported to Extended Events.
Context
StackExchange Database Administrators Q#88713, answer score: 12
Revisions (0)
No revisions yet.