/* With the DMV sys.dm_db_missing_xxx you can get statistic informations about missing indexes, like count of seeks and cost reduction if it would exists and so on. But which are all the queries where no fitting index exists for? Maybe they can be optimize so they could use an already exisiting index. If you run such a query in SSMS 2008 and have a look at the execution plan, in the header a hint about missing is shown and this information is also saved in the plan cache. With this Transact-SQL script you can run a query against the cached plans to get all SQL statements with missing indexes hints incl execution plan. Note: The xml data of the cached query plans are not indexed in the DMV, therefore the query can run up to several minutes. Works with SQL Server 2005 and higher versions in all editions. Requires VIEW SERVER STATE permissions. */ -- Get all SQL Statements with missing indexes and their cached query plans ; WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan) SELECT ECP.[usecounts] AS [UsageCounts] ,ECP.[refcounts] AS [RefencedCounts] ,ECP.[objtype] AS [ObjectType] ,ECP.[cacheobjtype] AS [CacheObjectType] ,EST.[dbid] AS [DatabaseID] ,EST.[objectid] AS [ObjectID] ,EST.[text] AS [Statement] ,EQP.[query_plan] AS [QueryPlan] FROM sys.dm_exec_cached_plans AS ECP CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP WHERE ECP.[usecounts] > 1 -- Plan should be used more then one time (= no AdHoc queries) AND EQP.[query_plan].exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0 ORDER BY ECP.[usecounts] DESC
