Find missing indexes for SQL Server 2012

The following example generates a listing of missing indexes that could be beneficial for T-SQL batches that have been submitted to the query optimizer since the last time the SQL Server service was restarted:

T-SQL

SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [Index_Useful] ,
igs.last_user_seek ,
id.statement AS [Statement] ,
id.equality_columns ,
id.inequality_columns ,
id.included_columns ,
igs.unique_compiles ,
igs.user_seeks ,
igs.avg_total_user_cost ,
igs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS igs
INNER JOIN sys.dm_db_missing_index_groups AS ig ON igs.group_handle = ig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle
ORDER BY [Index_Useful] DESC;

Query Execution Results
Query Execution Results
Missing Index Details - Full listing showing the desirability status, columns to be used and user seeks
Missing Index Details – Full listing showing the desirability status, columns to be used and user seeks

As you can see, the most required index missing in the database above is one on the status table.
We can create the missing index using the following command:

CREATE INDEX IX_ERPStatus ON tblERPStatus (SystemID, ERPDocumentNumber)

Advertisements