데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3126
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1469
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1230
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1088
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 938
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 846
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 912
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 945
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1883
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1255
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1223
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2689
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1687
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1750
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5673
» DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1343
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3807
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1787
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1244
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1261





XE Login