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

 



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice 2023년 1월 - SQLER의 업데이트 강좌 리스트 코난(김대우) 2023.01.02 526
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 875
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 934
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 1023
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1947
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1284
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1297
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2780
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1723
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1906
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5801
» DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1386
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 4117
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1839
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1266
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1297
1711 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 891
1710 SQL Server 특정 세션에 대한 마지막 실행 문장 확인 jevida(강성욱) 2016.09.13 748
1709 DMV - 데이터베이스 버퍼 메모리 사용량 확인 jevida(강성욱) 2016.09.13 3059
1708 Sys.dm_fts_parser을 이용한 문자열 구문 분석 jevida(강성욱) 2016.09.13 1350
1707 DMV에서 SQL Server 리소스 데이터베이스 값 jevida(강성욱) 2016.09.13 1091





XE Login