성능분석 13탄 – 누락된 인덱스(missing index)

성능분석 13탄 – 누락된 인덱스(missing index)

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

 

SQL Server에서 누락된 인덱스는 성능 문제를 일으킬 수 있다. 누락된 인덱스(missing index)는 인덱스가 생성되어 있지만 사용되지 않는 인덱스이다.

 

SQL Server 2005 부터는 누락된 인덱스를 확인하기 위한 DMV가 제공되고 있다. 다음 DMV를 활용하여 누락된 인덱스의 정보를 확인 할 수 있다.

• Sys.dm_db_missing_index_details : 누락된 인덱스에 대한 상세 정보 반환
• Sys.dm_db_missing_index_group_stats : 누락 된 인덱스 그룹에 대한 요약 정보 반환
• Sys.dm_db_missing_index_groups : 누락 된 인덱스의 특정 그룹에 대한 반품 정보 반환
• Sys.dm_missing_index_columns(index_handle) : 인덱스에 대한 누락된 데이터베이스테이블 컬럼에 대한 정보를 반환.

 

 

아래 스크립트는 DMV를 활용하여 쿼리가 실행되고 있는 데이터베이스에 누락된 인덱스를 식별하고 비용이 높은 쿼리를 찾아 인덱스를 생성하는 가이드를 제공한다.

SELECT     a.avg_user_impact * a.avg_total_user_cost * a.user_seeks, db_name(c.database_id), OBJECT_NAME(c.object_id, c.database_id), c.equality_columns, c.inequality_columns, c.included_columns, c.statement, 'USE [' + DB_NAME(c.database_id) + ']; CREATE INDEX mrdenny_' + replace(replace(replace(replace (ISNULL(equality_columns, '') + ISNULL(c.inequality_columns, ''), ', ', '_'), '[', ''), ']', ''), ' ', '') + ' ON [' + schema_name(d.schema_id) + '] .[' + OBJECT_NAME(c.object_id, c.database_id) + '] (' + ISNULL(equality_columns, '') + CASE WHEN c.equality_columns IS NOT NULL AND c.inequality_columns IS NOT NULL THEN ', ' ELSE '' END + ISNULL(c.inequality_columns, '') + ') ' + CASE WHEN included_columns IS NOT NULL THEN 'INCLUDE (' + included_columns + ')' ELSE '' END + ' WITH (FILLFACTOR=70, ONLINE=ON)' FROM sys.dm_db_missing_index_group_stats a JOIN sys.dm_db_missing_index_groups b ON a.group_handle = b.index_group_handle JOIN sys.dm_db_missing_index_details c ON b.index_handle = c.index_handle JOIN sys.objects d ON c.object_id = d.object_id WHERE c.database_id = db_id() ORDER BY DB_NAME(c.database_id), ISNULL(equality_columns, '') + ISNULL(c.inequality_columns, ''), a.avg_user_impact * a.avg_total_user_cost * a.user_seeks DESC

 

 

DMV로 확인한 정보를 이용하여 인덱스를 생성하거나 수정 할 때 중복 인덱스가 있는지 또는 다른 쿼리가 영향을 받는지 충분한 검토가 필요하다.

 

[참고자료]

• http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/
• 누락된 인덱스 확인하기 : http://sqlmvp.kr/140199349943
• DMV를 사용하여 누락된 인덱스 확인 : http://sqlmvp.kr/140178606760

 

 

2014-03-27 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, 강성욱, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, DMV, 누락된인덱스, missing index, Sys.dm_db_missing_index_details, Sys.dm_db_missing_index_group_stats, Sys.dm_db_missing_index_groups, Sys.dm_missing_index_columns(index_handle)