You've seen fancy ketchup. Here's fancy missing indexes.
This script is database specific. It does not use the server-wide DMV's alone, it joins to
SELECT mid.statement , create_index_statement = 'CREATE NONCLUSTERED INDEX IDX_NC_' + t.name + replace(replace(replace(ISNULL(mid.equality_columns,'') , '], [' ,'_'),'[','_'),']','') + replace(replace(replace(ISNULL(mid.inequality_columns,''), '], [' ,'_'),'[','_'),']','') + ' ON ' + 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 + ')', '') COLLATE SQL_Latin1_General_CP1_CI_AS , unique_compiles, migs.user_seeks, migs.user_scans, last_user_seek, migs.avg_total_user_cost , avg_user_impact, mid.equality_columns, mid.inequality_columns, mid.included_columns , quartile --select * FROM sys.dm_db_missing_index_groups mig INNER JOIN --get only the top 20% of suggestions based on cost. (select *, quartile = NTILE(5) OVER (ORDER BY avg_total_user_cost asc) from sys.dm_db_missing_index_group_stats) migs ON migs.group_handle = mig.index_group_handle --and migs.quartile = 1 INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle inner join sys.tables t on t.object_id = mid.object_id inner join sys.schemas s on s.schema_id = t.schema_id WHERE 1=1 and mid.database_id = DB_ID() --and (datediff(week, last_user_seek, getdate())) < 6 --AND migs.unique_compiles > 10 --and migs.quartile >= 3 --and migs.user_seeks > 10 and migs.avg_user_impact > 50 --order by avg_user_impact * avg_total_user_cost desc order by create_index_statement
- The improv calc
up topin the alternate ORDER BY is simply to evaluate the relative use of a potential index against others that show up in the report. Its not terribly scientific but weighs usage and value.
- The WHERE clause items can be used depending on the situation. If your server has been operating for a while and you'd like to see what indexing needs recent changes have been generating, use the date filter. Have a lot of ad hocs? Filter on unique_compiles > 1. The third WHERE clause is the same as the improv value above, helps me filter out things that are too small to care about. Lastly, I filter out anything that won't have at least a 50% impact. Oftentimes, I'll set this last value much higher.
- Joins to
INFORMATION_SCHEMA.TABLESsystem tables to generate a proper index create statement. It follows a naming convention I prefer, IDX_NC_ for nonclustered indexes, followed by the table name and the key fields. For covering indexes, I'll remove the key fields from the name and just put "covering". Your naming convention for indexes is probably different.
- Before creating any indexes from the missing index DMV's, do a little research on them and keep in mind that they reset with the server. It maybe useful to retain these over time, spanning multiple resets.
- Oftentimes, you'll see many of the rows in this result overlap. The INCLUDES might be in a different order, or you'll see one suggested index that has all but one other field that another suggestion has. Intelligently combine these together, keeping in mind that order DOES matter in the key fields.
- Lastly, if you've examined existing indexing on the table, use the provided creation script in the column create_index_statement .
- This script, aside from a few tweaks here and there, is hardly an original creation of this blog. As part of your research on missing index DMV's, don't miss: http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
Updated 5/28/2013 to reflect some changes, including getting rid of the INFORMATION_SCHEMA tables in favor of system tables, update the formatting,
"As I leave you I want you to know... just think how much you'll be missing." -Richard Nixon