Some time ago I wrote a post how SQL Server optimizer keep track of what indexes maybe beneficial and logs this info into sys.dm_db_missing_index_* DMVs. Brent Ozar kindly pointed out that one can script 'CREATE INDEX' statement to build missing indexes to achieve performance gains. However, what was missing in my past post is what SQL statement may benefit from such an index. Fortunately, SQL Server puts missing index information in query execution plan, which is just a typed XML, whose XSD schema file can be found in <disk>:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan folder of your SQL Server 2008 installation. One place to lookup for query execution plans in XML format for SQL statement is a plan cache of SQL Server using

select qp.query_plan,

SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 ,

(( CASE qs.statement_end_offset

WHEN -1 THEN datalength(st.TEXT)

ELSE qs.statement_end_offset

END

- qs.statement_start_offset)/2) + 1) AS txt

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(sql_handle) st

cross apply sys.dm_exec_query_plan(plan_handle) qp

Then one can modify above statment with XQuery to query for Missing Index information using above mentioned XSD schema for XML showplan in SQL Server 2008 to get what potential index is missing for the statement. Please note that not all statement have index missing, only some.   

Luckily, Brent Ozar and great site www.sqlserverpedia.com already have full query ready for this which I copied down below for your convenience.

SELECT qp.query_plan
, total_worker_time/execution_count AS AvgCPU
, total_elapsed_time/execution_count AS AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count AS AvgReads
, execution_count
, SUBSTRING(st.TEXT, (qs.statement_start_offset/2)+1 , ((CASE qs.statement_end_offset WHEN -1 THEN datalength(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS txt
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]' , 'decimal(18,4)') * execution_count AS TotalImpact
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]' , 'varchar(100)') AS [DATABASE]
, qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]' , 'varchar(100)') AS [TABLE]
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
WHERE qp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex[@Database!="m"]') = 1
ORDER BY TotalImpact DESC

So, now you know what indexes are missing and also have an idea what SQL statements benefit from that index.

Happy querying!

 -Yuriy