SQL Server
인덱스 상태를 진단하기 위한 새로운 도구
Randy Dyess
한 눈에 보기:
- 인덱스 조각화
- 인덱스 사용률
- 인덱스 실행 작업
이 기사의 코드 다운로드: DyessSQLIndex2007_03.exe (151KB)
SQL Server 성능 조정 검사 목록에 포함되는 상위 10개 항목 중 하나가 바로 데이터베이스의 인덱스를 조정하는 작업입니다. 쿼리를 실행하는 동안 인덱스를 적절하게 사용하는 SQL Server 쿼리 최적화 프로그램의 성능은 효과적인 인덱스의 생성뿐만 아니라 인덱스의 상태에 따라서도 달라집니다.
데이터베이스 관리자는 SQL Server™ 2005에 도입된 일련의 DMV(동적 관리 뷰)와 DMF(동적 관리 함수)를 사용하여 인덱스가 효율적인지 여부를 판단하고 성능 문제를 발견할 수 있습니다.
DMV와 DMF를 사용하면 서버를 관찰하고 서버 상태 정보를 확인하여 서버 인스턴스의 상태와 성능을 모니터링하고 문제를 진단할 수 있습니다. 이전 버전의 SQL Server에 친숙한 데이터베이스 관리자라면 이러한 DMV와 DMF가 DBCC 명령을 사용하고 특정 시스템 저장 프로시저를 실행하고 다양한 시스템 테이블을 쿼리하고 SQL 프로파일러를 사용하여 이벤트를 캡처하는 등의 작업을 대체하게 되리라는 사실을 알 수 있을 것입니다.
세 가지 주요 함수와 뷰 sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats 및 sys.dm_db_index_operational_stats를 사용하면 인덱스가 예상대로 작동하는지를 알 수 있습니다. 이러한 뷰와 함수를 사용하면 인덱스의 I/O와 잠금 패턴을 검토하고, 쿼리 최적화 프로그램이 인덱스를 활용할 때 데이터베이스에서 불필요한 경합이 발생하지 않는지 확인할 수 있습니다.
인덱스 조각화
sys.dm_db_index_physical_stats DMF는 DBCC SHOWCONTIG를 대체할 목적으로 만들어졌으며 인덱스의 조각화 정보를 보여 줍니다. 하지만 해당 인덱스가 포함된 테이블에 공유 잠금(S)을 설정하는 DBCC SHOWCONTIG와는 달리 sys.dm_db_index_physical_stats는 의도 공유 잠금(IS)만 설정함으로써 함수가 실행되는 동안 테이블 차단을 대폭 줄입니다.
sys.dm_db_index_physical_stats를 사용하여 인덱스의 조각화 정보를 확인하려면 함수 출력의 세 열을 함께 검토해야 합니다. 인덱스의 논리적 조각화 정보(힙의 익스텐트 조각화)는 avg_fragmentation_in_percent 열에 반환된 값을 통해 확인할 수 있습니다. 논리적 조각화는 인덱스의 리프 수준에서 순서가 잘못된 페이지 수에 대한 백분율인 반면 익스텐트 조각화는 인덱스의 리프 수준에서 순서가 잘못된 익스텐트에 대한 백분율입니다. 논리적 조각화와 익스텐트 조각화가 발생하면 페이지를 순서대로 읽기 위해 디스크 헤드가 페이지를 건너뛰어야 하기 때문에 추가 I/O 및 디스크 헤드 이동을 유발하여 인덱스 성능에 영향을 줄 수 있습니다. 따라서 논리적 조각화와 익스텐트 조각화는 가능하면 모두 0에 가깝게 유지해야 합니다.
인덱스의 내부 조각화는 페이지 사용률을 나타냅니다. 인덱스 페이지가 가능한 한 꽉 차도록 설정할 수도 있지만 페이지 분할 수를 절대 최소값으로 유지하려면 페이지 사용률과 인덱스 페이지에 대한 삽입 수가 적절한 균형을 이루도록 설정해야 합니다.
인덱스 페이지 사용률을 확인하려면 sys.dm_db_index_physical_stats의 avg_page_space_used_in_percent 인수를 검토합니다. 이 수치가 100%에 얼마나 가깝게 되도록 허용할지를 적절하게 구성하려면 인덱스 채우기 비율을 조정하면서 이때 발생하는 페이지 분할 수를 관찰합니다. 특정 시점에 페이지 분할 수가 급격하게 증가하기 시작하면 인덱스 채우기 비율이 적정 수준보다 높게 설정되었음을 나타냅니다. 인덱스 채우기 비율을 조정하는 데는 적지 않은 시간과 테스트가 필요하므로 계획을 적절히 세운 후에 수행하는 것이 좋습니다. 인덱스에 대한 임의 삽입이 없는 인덱스의 경우 페이지 분할 수가 증가하지 않으므로 채우기 비율을 100으로 설정할 수 있습니다.
AdventureWorks.HumanResources.Employee 테이블에 있는 모든 인덱스의 조각화 수준을 확인하려면 다음과 같은 문을 사용합니다.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL
-- NULL to view all indexes;
-- otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') -- We want all information
이 DMF를 사용하면 다시 만들어야 할 인덱스, 다시 구성해야 할 인덱스 및 유지 관리가 필요하지 않은 인덱스를 자동으로 결정할 수 있습니다. 인덱스 조각화 정보를 보여 주는 이 DMF의 avg_page_space_used_in_percent 및 avg_fragmentation_in_percent 열에서 허용되는 논리 및 밀도 임계값을 벗어나는 값을 검토하면 인덱스에 필요한 작업이 무엇인지 쉽게 확인할 수 있습니다.
인덱스 상태에 따라 그림 1의 예제가 AdventureWorks 샘플 데이터베이스 복사본에 있는 데이터를 반환하지 않는 경우도 있을 수 있지만, 이 예제는 다른 데이터베이스에도 쉽게 적용할 수 있습니다.
Figure 1 페이지 사용률 및 조각화 검토
--Reorganize the following indexes in the AdventureWorks database
USE AdventureWorks
GO
SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 10
AND avg_fragmentation_in_percent < 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 75
AND avg_page_space_used_in_percent > 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes
--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO
SELECT OBJECT_NAME([object_id]) AS 'Table Name',
index_id AS 'Index ID'
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes
쿼리 결과를 테이블 변수에 저장한 다음 테이블 변수에 대해 루프를 실행하여 올바른 ALTER INDEX 문에 대한 동적 문자열을 만드는 것은 쉽습니다(그림 2 참조).
Figure 2 동적 ALTER INDEX 문자열 만들기
--Rebuild the following indexes in the AdventureWorks database
USE AdventureWorks
GO
--Table to hold results
DECLARE @tablevar TABLE(lngid INT IDENTITY(1,1), objectid INT,
index_id INT)
INSERT INTO @tablevar (objectid, index_id)
SELECT [object_id],index_id
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,NULL -- NULL to view all tables
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') --We want all information
WHERE ((avg_fragmentation_in_percent > 15) -- Logical fragmentation
OR (avg_page_space_used_in_percent < 60)) --Page density
AND page_count > 8 -- We do not want indexes less than 1 extent in size
AND index_id NOT IN (0) --Only clustered and nonclustered indexes
SELECT 'ALTER INDEX ' + ind.[name] + ' ON ' + sc.[name] + '.'
+ OBJECT_NAME(objectid) + ' REBUILD'
FROM @tablevar tv
INNER JOIN sys.indexes ind
ON tv.objectid = ind.[object_id]
AND tv.index_id = ind.index_id
INNER JOIN sys.objects ob
ON tv.objectid = ob.[object_id]
INNER JOIN sys.schemas sc
ON sc.schema_id = ob.schema_id
인덱스 사용률
sys.dm_db_index_physical_stats는 인덱스 상태를 쉽게 볼 수 있도록 지원하며 DBCC SHOWCONTIG를 대체할 만큼 기능이 뛰어나기는 하지만, 테이블에 대해 실행할 쿼리에 어떤 인덱스가 유용한지를 결정하는 것과 관련해서는 더 복잡한 문제를 유발합니다. 대개 데이터베이스 개발자나 관리자는 쿼리를 실행하는 동안 쿼리 최적화 프로그램에서 사용할 것으로 예상되는 테이블에 대한 인덱스를 만듭니다. 이전 버전의 SQL Server에서는 이러한 인덱스가 실제로 사용되는지를 확인하기가 지금보다 훨씬 어려웠습니다. 이전에는 인덱스를 삭제한 다음 이것이 쿼리 성능에 영향을 주는지 관찰하거나 쿼리 실행 계획을 캡처한 다음 인덱스 사용 통계를 검토하는 방법을 이용했습니다.
그러나 이제는 새로운 동적 관리 뷰 sys.dm_db_index_usage_stats를 사용하여 쿼리 최적화 프로그램 및 테이블에 대해 실행된 쿼리에서 인덱스를 어느 정도나 사용하고 있는지를 쉽게 확인할 수 있습니다. 이 뷰를 검토하면 인덱스의 유용성을 결정하여 쿼리 최적화 프로그램에서 사용하지 않는 인덱스가 있으면 삭제할 수 있습니다. 인덱스가 저장 공간만 낭비하고 있지는 않은지, 사용되지 않는 인덱스가 유지되어 데이터베이스 성능이 저하되지 않는지에 대해 더 이상 신경 쓸 필요가 없습니다.
이 DMV의 출력에서 탐색(seek) 및 검색(scan) 횟수가 0인 인덱스를 검토하면 SQL Server를 마지막으로 시작한 이후에 인덱스가 사용되었는지 여부를 확인할 수 있습니다. 하지만 대부분의 DMV와 DMF는 지속적이지 않으며 SQL Server를 다시 시작할 때마다 0으로 다시 설정되므로 DMV나 DMF를 사용하여 인덱스 사용률을 확인할 때는 이에 유의해야 합니다. 서비스를 마지막으로 시작한 이후에는 필요하지 않지만 주말이나 월말 또는 분기별 보고서 쿼리에 인덱스가 필요할 수도 있기 때문입니다.
마지막으로 SQL Server 서비스를 시작한 이후 인스턴스의 사용되지 않은 모든 인덱스를 보려면 다음 문을 사용합니다.
SELECT DB_NAME(database_id),OBJECT_NAME([object_id])
FROM sys.dm_db_index_usage_stats
WHERE user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND system_seeks = 0
AND system_scans = 0
AND system_lookups = 0
인덱스 실행 작업
인덱스의 작업을 이해하는 데는 sys.dm_db_index_operational_stats DMF가 매우 유용합니다. 이 DMF를 사용하면 각 인덱스에서 발생하는 I/O, 잠금, 래치 및 액세스 방법 작업을 볼 수 있으며 이를 통해 인덱스가 어떤 방식으로 사용되고 있는지를 이해하고 과도한 I/O 작업 또는 인덱스의 "핫 스폿"으로 인한 인덱스 잠금 문제를 진단할 수 있습니다.
이 DMF의 래치 대기 열을 검토하면 읽기 및 쓰기 작업이 인덱스 리소스에 액세스하는 데 필요한 시간을 설정할 수 있습니다. 또한 인덱스를 저장하는 데 사용되는 디스크 하위 시스템이 인덱스 I/O 작업에 적합한지도 확인할 수 있습니다. 이 열의 정보는 인덱스의 디자인이나 사용 방법으로 인해 하나 이상의 인덱스 페이지에서 과도한 작업이 발생하고, 그 결과 해당 페이지에 포함된 데이터에 대한 경합을 야기하는 핫 스폿이 발생하는지 여부도 나타냅니다. 이러한 경합이 발생하면 대개 해당 영역에 대해 읽기나 쓰기를 시도하는 작업이 지나치게 차단될 수 있습니다.
그림 3은 AdventureWorks.HumanResources.Employee 테이블의 모든 인덱스에 대한 잠금 및 I/O 패턴을 확인하는 방법을 보여 줍니다.
Figure 3 잠금 및 I/O 패턴 확인
SELECT page_latch_wait_count --page latch counts
,page_latch_wait_in_ms --page latch wait times
,row_lock_wait_in_ms --row lock wait times
,page_lock_wait_in_ms --page lock wait times
,row_lock_count --row lock counts
,page_lock_count --page lock counts
,page_io_latch_wait_count --I/O wait counts
,page_io_latch_wait_in_ms --I/O wait times
FROM sys.dm_db_index_operational_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL -- NULL to view all indexes; otherwise, input index number
,NULL -- NULL to view all partitions of an index
)
추가 정보
이 문서에서 소개한 DMV와 DMF는 여기서 설명한 것 외에도 그 용도가 매우 다양합니다. 짬을 내서 이러한 함수와 뷰에 대해 설명하는 SQL Server 온라인 설명서의 기사를 읽어 보면 이들을 사용하여 반환 및 검토할 수 있는 광범위한 정보를 이해하는 데 도움이 될 것입니다. SQL Server 온라인 설명서의 관련 기사로 연결되는 링크는 아래의 "추가 리소스" 추가 기사에 나와 있습니다.
이 문서에서 설명하지 않은 추가 인덱스 DMF와 DMV에 대한 자세한 내용을 보려면 SQL Server 쿼리 최적화 팀에서 게시한 블로그 blogs.msdn.com/queryoptteam/570176.aspx를 참조하십시오.
추가 리소스
Randy Dyess는 Solid Quality Learning의 강사로서 SQL Server OLTP 시스템을 전문적으로 다루고 있습니다. 지금까지 SQL Server와 관련된 수많은 저서와 기사를 집필했으며 www.TransactSQL.Com과 www.Database-Security.Info의 설립자이자 주요 저자입니다.
© 2008 Microsoft Corporation 및 CMP Media, LLC. All rights reserved. 이 문서의 전부 또는 일부를 무단으로 복제하는 행위는 금지됩니다..