How can I find the query causing the missing index DMVs suggesting to create a particular index?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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:
and refer to link as well for more explanation on the same