This is a simple Missing Index Script with no Advertisements to bog you down. A few words of guidance:
- 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.
- Keep track of what indexes you add so if they cause negative impact down range, you can easily drop them.
- 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)
