Wednesday, May 27, 2009

Fancy Missing Indexes

UPDATED 5/30/2019, the latest version of this script is in the Toolbox here: https://github.com/SparkhoundSQL/sql-server-toolbox/blob/master/missing%20indexes.sql

UPDATED 5/28/2013, see notes below.

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 INFORMATION_SCHEMA.TABLES system tables to generate index create scripts. This is a very fast way to enter an environment, and take a peek at the indexing situation, something I do regularly as a DBA consultant.

SELECT 
 mid.statement
, create_index_statement = 'CREATE NONCLUSTERED INDEX IDX_NC_' + replace(t.name, ' ' ,'')
 + replace(replace(replace(ISNULL(replace(mid.equality_columns, ' ' ,''),'') , '],[' ,'_'),'[','_'),']','') 
 + replace(replace(replace(ISNULL(replace(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() -- current database only
--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 > 75
--and  t.name like '%salesorder%'

--order by avg_user_impact * avg_total_user_cost desc 
order by create_index_statement
Pardon blogspot's inane raw code posting ability.
  • The improv calc up top in 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.TABLES system 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
Other linkage:
https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms179325(v=sql.105)?redirectedfrom=MSDN
https://www.mssqltips.com/sqlservertip/1634/find-sql-server-missing-indexes-with-dmvs/

Updated 5/28/2013 to reflect some changes, including getting rid of the INFORMATION_SCHEMA tables in favor of system tables, update the formatting, 
Labels: , , ,