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

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

누락된 인덱스 확인하기

jevida(강성욱) 2016.10.08 07:03 Views : 2871

누락된 인덱스 확인하기

 

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

 

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

 

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

 

 

누락된 인덱스를 확인하는 방법 중 하나는 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로 확인한 정보를 이용하여 인덱스를 생성하거나 수정 할 때에는 중복 인덱스가 있는지 등을 충분히 검토 후 적용해야 한다.

 

 

[참고자료]

 

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1871 DMV를 활용한 CPU 트러블슈팅 - Sys.dm_exec_query_stats, sys.dm_os_ring_buffers 활용 [1] jevida(강성욱) 2016.10.08 1907
1870 백업 미디어 세트에 압축 백업 추가하기 jevida(강성욱) 2016.10.08 1451
1869 Collation에 따른 실행계획 변경과 성능 문제 jevida(강성욱) 2016.10.08 1667
1868 SQL Connection Timeout 디버깅 with BizTalk Server jevida(강성욱) 2016.10.08 2279
1867 인스턴스 파일 초기화 활성 jevida(강성욱) 2016.10.08 1710
» 누락된 인덱스 확인하기 jevida(강성욱) 2016.10.08 2871
1865 비클러스터 인덱스 페이지 내용 jevida(강성욱) 2016.10.08 2011
1864 ATTACH DATABASE 오류 1314 jevida(강성욱) 2016.10.08 1161
1863 SQL Server 커넥션 풀링 jevida(강성욱) 2016.10.08 4051
1862 가상 SQL Server에 Hot Add vCPU 사용하기 jevida(강성욱) 2016.10.08 1112
1861 DDL 트리거를 활용한 ERRORLOG에 XEVENT 상태 기록하기 jevida(강성욱) 2016.10.08 1348
1860 쉐어포인트의 SQL Server 접속 문제 jevida(강성욱) 2016.10.08 1446
1859 Lazy Log Truncation jevida(강성욱) 2016.10.08 1290
1858 인덱스 구성과 상황에 따른 인덱스 성능 jevida(강성욱) 2016.10.08 1462
1857 Max worker thread 초과 이슈 jevida(강성욱) 2016.10.08 2663
1856 SQL Server Failover 클러스터 설치 트러블슈팅 jevida(강성욱) 2016.10.08 2342
1855 MAXDOP 극대화 하기 jevida(강성욱) 2016.10.08 2018
1854 SQL Server 가상화 팁 jevida(강성욱) 2016.10.08 2019
1853 Net Framework 4.0과 SQL Server 2008 설치 오류 jevida(강성욱) 2016.10.08 1842
1852 SQL Server 인덱스 튜닝 접근 jevida(강성욱) 2016.10.07 3441





XE Login