After getting into extended events this week I somehow ended up on an XQuery kick (chalk this up to late night research and reading material). I took some concepts I found from Jason Strate’s blog and re-worked them some to fit what I needed. So basically the concept I took was this, from research I understood that the DMV sys.dm_exec_cached plans has the SHOWPLAN XML for an execution plan. Now I already knew that there was an element of missing indexes but I never dove into the cache itself. I also understood that if this element is present in the plan that it would contain details that would improve the performance of the query being executed. So in saying that what I’ve researched I leveraged XQuery to search the entire plan cache through the DMV
sys.dm_exec_cached_plans to find all the plans with the MissingIndexes element. Once all the plans are in place they can be shredded to determine the SQL text, the index that is missing and the count of the number of times the plan was used among other things. This is a pretty powerful tool to have in the arsenal as I’m sure so many already do…….

To view similiar content you can view Jason Strate’s example located here