누락된 인덱스 확인하기

누락된 인덱스 확인하기

 

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

 

SQL Server에는 인덱스를 사용하여 데이터베이스의 성능을 높일 수 있다. 하지만 인덱스가 생성되어 있어도 사용하지 못하면 좋은 성능을 낼 수 없다.

 

이번 시간에는 인덱스가 생성되어 있지만 사용하지 않는 인덱스(Missing Index)를 확인 하는 방법을 알아보자. 이 내용은 지난 포스트에서 다루었던 내용을 학습하고 보면 더욱 좋을 듯 하다.

 

• DMV를 활용한 누락된 인덱스 확인 : http://sqlmvp.kr/140178606760

 

누락된 인덱스를 확인하는 방법 중 하나는 DMV를 이용하는 것이다. 위의 아티클에 잘 설명되어 있듯이 sys.dm_db_missing_index_details, sys.dm_db_missing_index_columns를 이용하여 확인 할 수 있다.

 

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

USE Database_Name GO   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://sqlmag.com/database-performance-tuning/slow-sql-server-caused-missing-indexes
• DMV를 활용한 누락된 인덱스 확인 : http://sqlmvp.kr/140178606760

 

 

 

2013-10-21 / 강성욱 / http://sqlmvp.kr