Last year, I made my tool for automating execution plan analysis available on
The original version could parse execution plans from sys.dm_exec_query_stats or dm_exec_cached_plans and generate a cross-reference of which execution plans employed each index. The DMV sys.dm_db_index_usage_stats shows how often each index is used, but not where, that is, which particular stored procedure.
My latest version can now also 1) use the DMV sys.dm_exec_procedure_stats, 2) it can also get the full list of stored procedures in a database and generate the plans with SET SHOWPLAN_XML, and it can parse a set of previously generated sqlplans.
(see Seth's comment)
(build time 2010-03-22 10:02 EST)
Better yet, go to www.qdpma.com to check for the most recent version.
A quick review: SQL Server 2005 made it easy to get execution statistics with DMVs and DMFs. Among the collection of Execution Related Dynamic Management Views and Functions, sys.dm_exec_query_stats maintains aggregate performance statistics for cached query plans. By cross applying to the DMFs sys.dm_exec_sql_text and sys.dm_exec_query_plan, we can get the SQL and the XML execution plan.
Technically, the SQL Server engine can parse the contents of the XML plan, but I have never thought that this was a good idea, especially when working with very large execution plans or a very large number of execution plans.
Parsing XML execution plans from a C# program, on the other hand is not problem at all. It takes only a few minutes to grind through 1GB of XML plans.
If anyone thinks this is an abnormally large, check what your Plan Cache: Cache Pages and Cache Object Counts are. I have seen over 1M pages, and 50,000 objects (of course only a few hundred of the 50K were actually reused).
Anyways, please give my tool a try, and send feedback,
I am trying to make this a generally useful tool
For reference, the previous blogs: