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

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

SQL Server 인덱스 튜닝 접근

 

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

 

잘 만들어진 인덱스는 SQL Server의 읽기 성능을 향상 시킬 수 있다. 하지만 인덱스를 유지하기 위해 정기적인 인덱스 유지보수(rebuilds, reorganization and updating statistics) 및 추가 스토리지 공간 등 비용이 발생 한다. 그렇다면 인덱스를 사용, 관리, 튜닝 하는데 있어서 어떤 방식으로 접근해야 할까?

 

이번 포스트는 SQL Server Premier Field Engineer Blog에 게시된 내용으로 필자가 이해한 내용을 바탕으로 정리 하였으며 번역의 오류나 기술적 오류가 있을 수 있으므로 원문을 참고하길 바란다.

 

인덱스에 대한 비용 부분은 데이터를 업데이트 할 때도 발생 한다. 이해를 돕기 위해 예를 들어 설명한다. 다음 스크립트를 실행하여 예제 테이블을 생성한다.

CREATE TABLE dbo.Person(

CompanyID INT IDENTITY,

NetworkId VARCHAR(20),

FirstName VARCHAR(20),

MiddleName VARCHAR(20),

LastName VARCHAR(50),

DateOfBirth DATE,

SSN CHAR(9),

EmailAddress VARCHAR(100),

BusinessPhone VARCHAR(10),

ModifiedDate DATETIME,

CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (CompanyID)

);

GO

 

CREATE INDEX ix_LastName ON dbo.person (LastName);

CREATE INDEX ix_LastFirstMiddle ON dbo.Person (LastName, FirstName, MiddleName);

CREATE INDEX ix_LastNameFirstName ON dbo.Person (LastName, FirstName) INCLUDE (DateOfBirth);

GO

 

테이블에 행을 삽입(데이터 입력)하거나 삭제 할 때마다 또는 비클러스터형 테이블에 데이터를 입력, 삭제 할 때 인덱스 컬럼은 포함된 인덱스를 업데이트 해야 한다. 이때 I/O 비용이 발생한다. – 대부분의 사용자는 이 과정이 트랜잭션 로그에 기록되지 않는다고 생각한다.

 

SQL Server에서 제공하는 튜닝 도구 마법사에서 제안된 인덱스를 추가하기에는 사용자들에게 많은 혼란을 가져온다. 인덱스에 대해 좀 더 명확하고 총체적인 접근이 필요하다.

 

 

인덱스 유지 관리를 위해 시각화 하여보자. [실제 실행 계획 포함] 옵션을 선택 한 후 다음 스크립트를 이용하여 데이터를 입력 한다.

INSERT INTO dbo.Person (NetworkId, FirstName, MiddleName, LastName, DateOfBirth, SSN, EmailAddress, BusinessPhone, ModifiedDate )

VALUES ('jroberts', 'Jonathan', 'Q', 'Roberts', '19700206', '123456789', 'jroberts@somecompany.com', '9195559632', GETDATE());

 

 

F4키를 눌러 속성에서 개체 노드 아래로 스크롤을 확장하여 보자. 여기에서 비클러스터형 인덱스는 삽입에 의해 수정 되는 것을 확인 할 수 있다.

 

 

다음 스크립트를 이용하여 MiddleName를 업데이트 하여 보자.

UPDATE dbo.Person SET MiddleName = 'Quincy'

WHERE NetworkId = 'jroberts';

 

 

 

 

이처럼 인덱스의 수와 디자인에 따라 서버의 성능에 영향을 주는 것을 확인 할 수 있다. 우리의 목표는 더 작은 수의 인덱스를 사용하는 것이다. 이와 같은 작업을 하기 위해서는 인덱스의 영향력을 모니터링하고 통합 할 수 있는 기회를 찾고 기존의 인덱스 수정에 대한 검토를 해야 한다. 이 때 중요한 것은 예비 병목 현상을 분석하여 인덱스 수정으로 인한 장애가 발생하지 않도록 해야 한다.

 

인덱스 생성에서 [누락된 인덱스] 또는 DTA를 사용하는 경우 목표 테이블에 대한 다양한 인덱스 제안 중에 중복되는 인덱스 생성에 대해 주의하자. 다음 스크립트는 누락된 인덱스를 수집하는 스크립트 이다.

-- Gather missing index data for the current database

SELECT t.name AS 'table',

( avg_total_user_cost * avg_user_impact ) * ( user_seeks + user_scans )

AS 'potential_impact',

'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + SCHEMA_NAME(t.schema_id)

+ '.' + t.name COLLATE DATABASE_DEFAULT + ' ('

+ ISNULL(d.equality_columns, '')

+ CASE WHEN d.inequality_columns IS NULL THEN ''

ELSE CASE WHEN d.equality_columns IS NULL THEN ''

ELSE ','

END + d.inequality_columns

END + ') ' + CASE WHEN d.included_columns IS NULL THEN ''

ELSE 'INCLUDE (' + d.included_columns + ')'

END + ';' AS 'create_index_statement'

FROM sys.dm_db_missing_index_group_stats AS s

INNER JOIN sys.dm_db_missing_index_groups AS g

ON s.group_handle = g.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS d

ON g.index_handle = d.index_handle

INNER JOIN sys.tables t WITH ( NOLOCK ) ON d.OBJECT_ID = t.OBJECT_ID

WHERE d.database_id = DB_ID()

AND s.group_handle IN

        (

            SELECT TOP 500 group_handle

            FROM sys.dm_db_missing_index_group_stats WITH ( NOLOCK )

            ORDER BY ( avg_total_user_cost * avg_user_impact ) *

                    ( user_seeks + user_scans ) DESC

        )

AND t.name LIKE 'Person'

ORDER BY ( avg_total_user_cost * avg_user_impact ) * ( user_seeks + user_scans ) DESC;

 

 

다음 스크립트는 1000개 이상의 행을 가진 테이블에 대한 인덱스 사용 결과를 조회한다.(스크립트를 수정하여 사용하면 다양한 검색 조건을 만들 수 있다.) 해당 DMV 정보는 SQL Server가 시작 될 때마다 재설정 됨을 유의하자.

-- Index usage for tables having more than 10000 rows

SELECT t.name 'table', i.name 'index_name',

( u.user_seeks + u.user_scans + u.user_lookups ) 'reads',

u.user_updates 'writes',

        ( SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = u.index_id

AND u.object_id = p.object_id

) 'rows',

        i.type_desc, i.is_primary_key,

        i.is_unique

FROM sys.dm_db_index_usage_stats u

INNER JOIN sys.indexes i ON i.index_id = u.index_id AND u.object_id = i.object_id

INNER JOIN sys.tables t ON u.object_id = t.object_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE OBJECTPROPERTY(u.object_id, 'IsUserTable') = 1

AND ( SELECT SUM(p.rows)

FROM sys.partitions p

WHERE p.index_id = u.index_id

AND u.object_id = p.object_id

) > 10000

AND u.database_id = DB_ID()

AND t.name LIKE 'Person'

ORDER BY reads;

 

 

중복 인덱스 이슈에서 때로는 중복 인덱스를 추가하는 것이 더 작은 I/O를 사용하기도 한다. 또한 잘 사용하지 않는 Where 절에 대해서는 INCLUDE 와 같은 전략을 고려할 수도 있다. 인덱스에 대한 수정 후에는 사용 통계를 확인하여 추가 조정을 고려해야 한다.

 

[인덱스 디자인 시 유의 사항]

  • 신중한 검토 없이 인덱스를 생성 수정하지 않도록 한다.
  • 긴 INCLUDE를 사용하여 중복사용을 하지 않도록 한다.
  • 인덱스 키를 너무 크게 사용하지 않도록 한다.
  • OLTP, OLAP 특성을 고려하여 디자인 하도록 한다.
  • 인덱스의 열 순서가 최적화 되어 올바른지 확인 한다.
  • 조인되는 테이블에 인덱스를 생성하여 사용할 수 있도록 유도 한다.
  • 인덱스의 사용량을 주기적으로 수집하여 계획을 세울 수 있도록 한다.

 

 

 

[참고자료]

 

  • Clustered index design guidelines :

http://technet.microsoft.com/en-us/library/ms190639(v=SQL.105).aspx

 

  • General Index design Guidelines :

http://technet.microsoft.com/en-us/library/ms191195(v=SQL.105).aspx

 

 



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

No. Subject Author Date Views
1870 백업 미디어 세트에 압축 백업 추가하기 jevida(강성욱) 2016.10.08 1432
1869 Collation에 따른 실행계획 변경과 성능 문제 jevida(강성욱) 2016.10.08 1666
1868 SQL Connection Timeout 디버깅 with BizTalk Server jevida(강성욱) 2016.10.08 2220
1867 인스턴스 파일 초기화 활성 jevida(강성욱) 2016.10.08 1682
1866 누락된 인덱스 확인하기 jevida(강성욱) 2016.10.08 2787
1865 비클러스터 인덱스 페이지 내용 jevida(강성욱) 2016.10.08 2009
1864 ATTACH DATABASE 오류 1314 jevida(강성욱) 2016.10.08 1160
1863 SQL Server 커넥션 풀링 jevida(강성욱) 2016.10.08 3909
1862 가상 SQL Server에 Hot Add vCPU 사용하기 jevida(강성욱) 2016.10.08 1112
1861 DDL 트리거를 활용한 ERRORLOG에 XEVENT 상태 기록하기 jevida(강성욱) 2016.10.08 1344
1860 쉐어포인트의 SQL Server 접속 문제 jevida(강성욱) 2016.10.08 1445
1859 Lazy Log Truncation jevida(강성욱) 2016.10.08 1268
1858 인덱스 구성과 상황에 따른 인덱스 성능 jevida(강성욱) 2016.10.08 1457
1857 Max worker thread 초과 이슈 jevida(강성욱) 2016.10.08 2568
1856 SQL Server Failover 클러스터 설치 트러블슈팅 jevida(강성욱) 2016.10.08 2333
1855 MAXDOP 극대화 하기 jevida(강성욱) 2016.10.08 1964
1854 SQL Server 가상화 팁 jevida(강성욱) 2016.10.08 2015
1853 Net Framework 4.0과 SQL Server 2008 설치 오류 jevida(강성욱) 2016.10.08 1834
» SQL Server 인덱스 튜닝 접근 jevida(강성욱) 2016.10.07 3367
1851 Sys.dm_os_performance_counter 해석하기 jevida(강성욱) 2016.10.07 2233





XE Login