SQL Server Missing Indexes Script

Facebook
Twitter
LinkedIn

This is a simple Missing Index Script with no Advertisements to bog you down. A few words of guidance:

  1. Don’t blindly add indexes.  For example, DO NOT add all the indexes this script returns.  Be methodical in determining what indexes would have impact and test them one at a time. 
  2. Keep track of what indexes you add so if they cause negative impact down range, you can easily drop them. 
  3. Use Automatic Index Management in newer version of SQL Server (2017+ or Azure SQL):  Automatic Index Management

Missing Indexes Script – run on a per database level.  Click Copy on the code block below and give it a try!

 

				
					DECLARE @runtime datetime
SET     @runtime = GETDATE() 

-- 'Missing Indexes: '
    -- 'The "improvement_measure" column is an indicator of the (estimated) improvement that might '
    -- 'be seen if the index was created.  This is a unitless number, and has meaning only relative '
    -- 'the same number for other indexes.  The measure is a combination of the avg_total_user_cost, '
    -- 'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'

SELECT
    CONVERT (varchar(30), @runtime, 126) AS runtime,
    mig.index_group_handle,
    mid.index_handle,
    CONVERT (
        decimal (28, 1),
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
    ) AS improvement_measure,
    'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' 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
    CONVERT (
        decimal (28, 1),
        migs.avg_total_user_cost * migs.avg_user_impact * (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 OPTION (MAX_GRANT_PERCENT = 3, MAXDOP 1)
				
			

If you want some help, reach out!

Share this post

Picture of by Najaxa Software

by Najaxa Software

Keep Reading

Scroll to Top