MSSQL : 누락된 인덱스 분석

누락된 인덱스란 쿼리의 성능을 향상시킬 수 있는 인덱스가 존재하지 않는 경우를 말합니다. 누락된 인덱스를 찾고 생성하는 방법은 다음과 같습니다.

누락된 인덱스 찾기

`sys.dm_db_missing_index_details` 뷰

이 뷰는 누락된 인덱스의 테이블 이름, 열 이름, 인덱스 유형 등의 정보를 제공합니다. 예를 들어, 다음 쿼리는 누락된 인덱스의 테이블 이름과 열 이름을 출력합니다.

SELECT
    d.[object_id],
    OBJECT_NAME(d.[object_id]) AS [Table Name],
    d.index_handle,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns
FROM sys.dm_db_missing_index_details d

 

인덱스의 효용성 평가

`sys.dm_db_missing_index_groups` , `sys.dm_db_missing_index_group_stats` 뷰

이 뷰들은 누락된 인덱스의 그룹 번호, 평균 쿼리 비용 감소, 사용 횟수, 시스템 영향 등의 정보를 제공합니다. 예를 들어, 다음 쿼리는 누락된 인덱스의 그룹 번호와 평균 쿼리 비용 감소를 출력합니다.

SELECT
    g.index_group_handle,
    g.index_handle,
    ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) AS [Improvement Measure]
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
ORDER BY [Improvement Measure] DESC

인덱스 생성

인덱스의 이름, 테이블 이름, 열 이름, 인덱스 유형 등을 지정해야 합니다. 예를 들어, 다음 쿼리는 `Customer` 테이블의 `LastName` 열에 대해 클러스터형 인덱스를 생성합니다.

CREATE CLUSTERED INDEX IX_Customer_LastName
ON Customer (LastName)

이상이 MS SQL에서 누락된 인덱스를 분석하는 방법에 대해 알아 보았습니다.

활용 예시

//누락된 인덱스에 대한 인덱스 생성 쿼리
SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1), 
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) 
        ) AS estimated_improvement,
    '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
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON 
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON 
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;
GO

//위에서 생성한 인덱스의 활용 빈도를 확인하는 쿼리
select object_name(ii.object_id) as table_name,ii.name as index_name, 
user_seeks,user_scans,user_lookups,user_updates,last_user_seek,
last_user_scan,last_user_lookup,last_user_update
from sys.indexes as ii
left join sys.dm_db_index_usage_stats as us on ii.object_id=us.object_id and ii.index_id=us.index_id
where ii.name like '%missing%'