[MSSQL] 20. 미싱 인덱스 Missing Index 확인 쿼리

미싱 인덱스(Missing Index)를 조회할 때 사용하는 쿼리입니다.

 

EqualCols는 = 조건으로 검색이 들어오는 컬럼에 대한 내용이 조회되며,

InEqualCols는 범위 조건(<,>,Between) 등으로 검색이 들어오는 컬럼에 대한 내용이 조회됩니다. 

 

AvgTotalUserCost는 해당 미싱 인덱스를 생성했을 때 SQL Server가 개선이 될 것으로 예상하는 비용인데, 

높을수록(100에 가까울수록) 그 효용이 높습니다. 

사이트의 상황에 따라 다르지만, AvgTotalUserCost > 10 이상인 인덱스에 대해 우선적으로 확인하고, 그 이후 10 미만의 인덱스에 대해 검토하는 것도 시간 효율성 측면에서 좋은 방법이라고 생각합니다. 

SELECT db.[name] AS [dbName]
    ,id.[object_id] AS [objID]
    ,id.[statement] AS [tabName]
    ,id.[equality_columns] AS [EqualCols]
    ,id.[inequality_columns] AS [InEqualCols]
    ,id.[included_columns] AS [IncludeCols]
    ,gs.[unique_compiles] AS [UniqCompiles]
    ,gs.[user_seeks] AS [UserSeeks]
    ,gs.[user_scans] AS [UserScans]
    ,gs.[last_user_seek] AS [LastUserSeekTime]
    ,gs.[last_user_scan] AS [LastUserScanTime]
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]
    ,gs.[avg_user_impact] AS [AvgUserImpact]
    ,gs.[system_seeks] AS [SystemSeeks]
    ,gs.[system_scans] AS [SystemScans]
    ,gs.[last_system_seek] AS [LastSystemSeekTime]
    ,gs.[last_system_scan] AS [LastSystemScanTime]
    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
    ,gs.[avg_system_impact] AS [AvgSystemImpact]
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN '_'
        ELSE ''
        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK)
    ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK)
    ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK)
    ON db.[database_id] = id.[database_id]
WHERE id.[database_id] > 4 -- Remove this to see for entire instance
ORDER BY [IndexAdvantage] DESC
OPTION (RECOMPILE);
GO