SQL Server 인덱스 튜닝 접근

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 특성을 고려하여 디자인 하도록 한다.
• 인덱스의 열 순서가 최적화 되어 올바른지 확인 한다.
• 조인되는 테이블에 인덱스를 생성하여 사용할 수 있도록 유도 한다.
• 인덱스의 사용량을 주기적으로 수집하여 계획을 세울 수 있도록 한다.

 

 

 

[참고자료]

• http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/09/13/an-approach-to-sql-server-index-tuning.aspx

 

• 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

 

 

2013-09-25 / 강성욱 / http://sqlmvp.kr