Missing Index – Improvement Measure Calculation Logic

Missing Index – Improvement Measure Calculation Logic

To help answer some of these questions, I calculate an arbitrary number to determine the “usefulness” of a new index, called improvement measure. The higher this number, the more useful it is to create it. The improvement measure is calculated based on 4 factors

1. Average cost of the user queries that could be reduced by the new index. This is the current cost of the query.
2. Average impact on the query if this index were created. i.e. The query cost would on average drop by this percentage value.
3. Number of Seek operations that this index could have been used by user queries.
4. Number of Scan operations that this index could have been used by user queries.

Now that you understand what missing indexes are, factors to consider and the logic behind the recommendations, go ahead and run the below query on your databases. The output provides you the “CREATE INDEX” T-SQL statement that you can use to create the index, if you see fit after considering all factors mentioned so far. This saves you time having to write the T-SQL yourself. The CREATE INDEX statement will include the index key columns in order, suggest included columns where applicable.

USE [msdb]  /*Replace with your Database Name */
GO

SELECT
    GETDATE() AS [RunTime],
    DB_NAME(mid.database_id) AS [DBNAME],
    OBJECT_NAME(mid.[object_id]) AS [ObjectName],
mid.[object_id] AS [ObjectID],
    CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS [Improvement_Measure],
    'CREATE NONCLUSTERED INDEX IX_SON_' + 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],
'DROP INDEX ' + OBJECT_NAME(mid.[object_id])+'.'+'IX_SON_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) AS 'DELETE_INDEX_STATEMENT',
    migs.user_seeks, migs.user_scans, migs.last_user_seek, migs.last_user_scan, migs.avg_total_user_cost, migs.avg_user_impact, migs.avg_system_impact,
    mig.index_group_handle, mid.index_handle
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
    AND mid.database_id = DB_ID()
ORDER BY [Improvement_Measure] DESC
GO