Last active
May 23, 2017 12:52
-
-
Save a-sitnikov/910f2a34e3a2c62fefb00bce5f0f5f56 to your computer and use it in GitHub Desktop.
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
--http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx | |
--http://infostart.ru/public/128175/ | |
SELECT | |
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, | |
'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) | |
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']' | |
+ ' ON ' + mid.statement | |
+ ' (' + ISNULL (mid.equality_columns,'') | |
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END | |
+ ISNULL (mid.inequality_columns, '') | |
+ ')' | |
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, | |
migs.*, mid.database_id, mid.[object_id] | |
FROM | |
sys.dm_db_missing_index_groups mig | |
INNER JOIN sys.dm_db_missing_index_group_stats migs | |
ON migs.group_handle = mig.index_group_handle | |
INNER JOIN sys.dm_db_missing_index_details mid | |
ON mig.index_handle = mid.index_handle | |
WHERE | |
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10 | |
ORDER BY | |
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC |
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 [РекомендуемыйИндекс]= | |
'-- CREATE INDEX [DTA_' + OBJECT_NAME(mid.object_id) + '_' + | |
CAST(mid.index_handle AS nvarchar) + '] ON ' + | |
mid.statement + ' (' + ISNULL(mid.equality_columns,'') + | |
', ' + ISNULL(mid.inequality_columns,'') + | |
') INCLUDE (' + ISNULL(mid.included_columns,'') + ');', | |
[ЧислоКомпиляций] = migs.unique_compiles, | |
[КоличествоОперацийПоиска] = migs.user_seeks, | |
[КоличествоОперацийПросмотра] = migs.user_scans, | |
[СредняяСтоимость] = CAST(migs.avg_total_user_cost AS int), | |
[СреднийПроцентВыигрыша] = CAST(migs.avg_user_impact AS int), | |
[ТаблицаИндекса] = OBJECT_NAME(mid.object_id) | |
FROM sys.dm_db_missing_index_groups mig | |
JOIN sys.dm_db_missing_index_group_stats migs | |
ON migs.group_handle = mig.index_group_handle | |
JOIN sys.dm_db_missing_index_details mid | |
ON mig.index_handle = mid.index_handle | |
AND mid.database_id = DB_ID() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment