DMV를 사용하여 누락된 인덱스 확인

DMV를 사용하여 누락된 인덱스 확인

 

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

 

SQL Server에서 인덱스가 생성되어 있지만 사용되지 않는 인덱스에 대해 식별하는 방법에 대해서 알아 보자.

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) : 인덱스에 대한 누락된 데이터베이스 테이블 컬럼에 대한 정보를 반환.

 

 

실습을 통해서 알아 보자. 예제 데이터베이스는 SQL Server 2008의 AdventureWorks 이다.

인덱스가 없는 컬럼을 검색 조건으로 하여 데이터를 조회 하였다.

select City, StateProvinceID, PostalCode from Person.Address where ModifiedDate = '2001-08-01 00:00:00.000'

 

 

 

Missing index에 대한 상세 정보를 확인 하기 이하여 sys.dm_db_missing_index_details 를 실행 한다.

select * from sys.dm_db_missing_index_details

 

 

• Equality_column = modifiedDate 는 Where절 연산에서 사용되었으며 인덱스 후보자가 될 수 있다.
• Included_column은 인덱스가 생성 될 때 열을 포함하는 두 열을 사용할 수 있다.

 

 

Sys.dm_db_missing_index_group_stats 에서는 컴파일 된 정보, 사용자 스캔 등 정보를 반환 한다. 부가적으로 리는 쿼리의 호출 빈도도 유추 할 수도 있다.

select * from sys.dm_db_missing_index_group_stats

 

 

 

Sys.dm_db_missing_index_groups는 index_group_handle과 index_handle에 대한 정보를 제공한다.

select * from sys.dm_db_missing_index_groups

 

 

 

위의 조회된 핸들 값을 이용하여 테이블의 사용된 열을 조회 할 수 있다.

select * from sys.dm_db_missing_index_columns(1)

 

 

 

위에서 확인한 DMV를 활용하여 인덱스 핸들 정보 및, 테이블명, 사용된 컬컴, 컬럼ID 등의 정보를 한번에 확인 할 수 있다.

SELECT b.*, statement AS table_name, column_id, column_name, column_usage FROM sys.dm_db_missing_index_details AS a CROSS APPLY sys.dm_db_missing_index_columns (a.index_handle) INNER JOIN sys.dm_db_missing_index_groups AS b ON b.index_handle = a.index_handle ORDER BY b.index_group_handle, b.index_handle, column_id;

 

 

완벽하지는 않지만 이러한 정보를 통해서 누락된 인덱스나 인덱스 생성시 참고 할 수 정보로 활용이 가능 할 듯 하다.

 

참고 자료

http://www.mssqltips.com/sqlservertip/1634/using-sql-server-dmvs-to-identify-missing-indexes/

http://msdn.microsoft.com/ko-kr/library/ms345434.aspx

http://msdn.microsoft.com/ko-kr/library/ms345421.aspx

http://msdn.microsoft.com/ko-kr/library/ms345407.aspx

http://msdn.microsoft.com/ko-kr/library/ms345364.aspx

 

 

 

2013-01-24 / 강성욱 / http://sqlmvp.kr