Created
December 2, 2019 11:00
-
-
Save PeterLehmann/c3f720081ecc476675f45b14993de276 to your computer and use it in GitHub Desktop.
mssql missing index
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 [IX_' + 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment