A query to find MS-SQL missing indexes

 

SELECT
    dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans)
Avg_Estimated_Impact,
dm_migs.last_user_seek AS
Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
‘CREATE INDEX
[IDX_’
+ OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + ‘_’
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,),‘, ‘,‘_’),‘[‘,),‘]’,) +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ‘_’
ELSE
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,),‘, ‘,‘_’),‘[‘,),‘]’,)
+ ‘]’
+ ‘ ON ‘ + dm_mid.statement
+ ‘ (‘ + ISNULL (dm_mid.equality_columns,)
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ‘,’ ELSE
END
+ ISNULL (dm_mid.inequality_columns, )
+ ‘)’
+ ISNULL (‘ INCLUDE (‘ + dm_mid.included_columns
+ ‘)’, ) AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats
dm_migs
ON dm_migs.group_handle =
dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details
dm_mid
ON dm_mig.index_handle =
dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s