Finding the query causing the missing index DMVs

Question

How can I find the query causing the missing index DMVs suggesting to create a particular index?

Thank you.

Answer 1

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.

Answer 2

Querying the plan cache to look for the indexes involved in the calculation of finding missing indexes DMV's can be looked from below pasted link:

https://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/

and refer to link as well for more explanation on the same

http://blogs.lessthandot.com/index.php/datamgmt/dbadmin/sql-server-dba-tip-missing-index-dmv/