|
느리거나 오래 실행되는 쿼리는 리소스를 과도하게 소비할 수 있으며 쿼리 차단으로 이어집니다.
과도한 리소스 소비는 CPU 리소스에만 국한되지 않고, I/O 저장소 대역폭과 메모리 대역폭 또한 포함될 수 있습니다. SQL Server 쿼리가 WHERE절을 통해서 결과 집합을 제한함으로써 테이블 전체 스캔을 피하도록 설계되었더라도, 해당 쿼리를 지원하는 적절한 인덱스가 없는 경우에 예상한대로 수행되지 않게됩니다. 또한, 사용자 입력에 따라 응용 프로그램에서 동적으로 WHERE절이 구성되는 경우, 기존 인덱스가 모든 경우의 조건을 다룰 수가 없게 됩니다. Transact-SQL 문장에 의한 과도한 CPU, I/O, 그리고 메모리 소비는 본 문서 전반부에서 다루어졌습니다.
인덱스 부재와 함께 사용되지 않는 인덱스도 존재할 수 있습니다. 모든 인덱스는 유지 관리 대상이므로, 쿼리 성능에는 영향을 미치지 않더라도, DML 쿼리에는 영향을 줍니다.
논리적 읽기를 위한 대기 상태 혹은 쿼리를 차단하고 있는 시스템 리소스에 대한 대기 상태로 인해 쿼리가 느리게 실행될 수 있습니다. 차단의 원인은 서투른 응용 프로그램 설계, 잘못된 쿼리 계획, 유용한 인덱스 부재, 그리고 작업 부하를 고려해서 적절히 구성되지 못한 SQL Server 인스턴스와 같은 것들이 될 수 있습니다.
이번 절은 느린 쿼리의 두 가지 원인, 차단과 인덱스 문제에 촛점을 맞춥니다.
차단
차단은 주로 논리적 읽기에 대한 차단입니다. 이는 특정 리소스에 X 잠금 획득을 위한 대기나 래치 같은 저 수준 동기화 개체로 인한 대기와 같습니다.
논리적 읽기 대기는 이미 잠긴 리소스에 호환되지 않은 다른 잠금을 요청할 때 발생합니다. 이는 Transact-SQL 문장을 실행할 때 트랜잭션 격리 수준에 따라 데이터 일관성을 제공하기 위해서 필요하지만, 최종 사용자에게는 SQL Server가 느리게 실행된다는 인식을 줍니다. 쿼리가 차단된다는 것이 시스템에 어떤 리소스를 소비하는 것은 아니므로, 시간은 오래 걸리지만 리소스 소비는 작은 쿼리를 찾을 수 있습니다. 동시성 제어 및 차단에 대한 상세 정보는 SQL Server 온라인 설명서를 참조하십시오.
저 수준의 동기화 개체에 대한 대기는 시스템이 작업 부하를 처리할 수 있도록 구성되지 못한 결과입니다.
차단/대기에 대한 공통적인 시나리오는:
ㆍ 차단 주범 식별
ㆍ 오래 걸리는 차단 식별
ㆍ 개체 별 차단
ㆍ 페이지 래치 문제
ㆍ 차단으로 인한 전반적인 성능 평가를 위해 SQL Server 대기 사용
요청을 처리하는데 필요한 시스템 리소스(혹은 잠금)가 현재 이용할 수 없는 상태이면 SQL Server 세션은 대기 상태에 놓입니다. 다른 말로 하자면, 리소스가 미해결 요청에 대한 큐를 가지는 것입니다. DMV는 리소스 대기 중인 세션에 대한 정보를 제공합니다.
SQL Server 2005는 보다 상세하고 일관된 대기 정보를 제공합니다, SQL Server 2000은 76개의 대기 유형을 지원한 반면 SQL Server 2005는 대략 125 개 이상의 대기 유형을 보고해 줍니다. DMV는 이러한 정보들을 두 가지 범위로 나누어 제공합니다. SQL Server 전체에 누적된 대기를 위한 sys.dm_os_wait_statistics[역주17]와, 세션 단위로 분류된 대기를 위한 세션 범위의 sys.dm_os_waiting_tasks입니다. 다음의 DMV는 특정 리소스에 대기 중인 작업의 대기 큐에 대한 상세 정보를 제공합니다. 예를 들어, 다음 쿼리를 실행해서 차단된 세션 56에 대한 상세 정보를 찾을 수 있습니다.
|
select * from sys.dm_os_waiting_tasks where session_id=56
waiting_task_address session_id exec_context_id wait_duration_ms
wait_type
resource_address blocking_task_address blocking_session_id
blocking_exec_context_id resource_description
-------------------- ---------- --------------- -------------------- -----
------------------------------------------------------- ------------------
--------------------- ------------------- ------------------------ -------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
---------------------------
0x022A8898 56 0 1103500
LCK_M_S 0x03696820
0x022A8D48 53 NULL
ridlock fileid=1 pageid=143 dbid=9 id=lock3667d00 mode=X
associatedObjectId=72057594038321152 |
이 결과에서 세션 53이 세션 56에 의해서 특정 잠금 대기로 1103500 밀리초 동안 차단되고 있음을 보여줍니다.
잠금이 허가된 혹은 잠금을 대기 중인 세션을 찾기 위해서, sys.dm_tran_locks DMV를 사용할 수 있습니다. 각 행은 현재 활성 요청을 나타내는 것으로, 잠금 관리자를 통해 잠금을 허가 받았거나 혹은 그 요청에 의해 차단된 상태로 허가를 기다리고 있는 요청입니다. 일반적인 잠금에 대해서는, 허가된 요청은 해당 리소스에 잠금이 요청자에게 허가되었음을 나타냅니다. 대기 중인 요청은 아직 허가받지 못했음을 나타냅니다. 예를 들어, 다음 쿼리는 세션 56이 리소스 1:143:3에 대해서 세션 53에 의한 X 모드가 있으므로 차단되었음을 보여줍니다.
|
select
request_session_id as spid,
resource_type as rt,
resource_database_id as rdb,
(case resource_type
WHEN 'OBJECT' then object_name(resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
resource_description as rd,
request_mode as rm,
request_status as rs
from sys.dm_tran_locks |
다음은 출력 결과입니다.
|
spid rt rdb objname rd rm
rs
--------------------------------------------------------------------------
---
56 DATABASE 9
S GRANT
53 DATABASE 9 S GRANT
56 PAGE 9 t_lock 1:143 IS GRANT
53 PAGE 9 t_lock 1:143 IX GRANT
53 PAGE 9 t_lock 1:153 IX GRANT
56 OBJECT 9 t_lock IS GRANT
53 OBJECT 9 t_lock IX GRANT
53 KEY 9 t_lock (a400c34cb X GRANT
53 RID 9 t_lock 1:143:3 X GRANT
56 RID 9 t_lock 1:143:3 S WAIT |
사실 저장 프로시저 sp_block에서 보여지는대로 위 두 DMV를 조인할 수 있습니다. 그림 1은 차단된 세션과 그 세션을 차단하는 세션 목록을 보여줍니다. 소스 코드는 부록 B에서 찾을 수 있습니다. 필요하면 저장 프로시저를 수행해서 원하는 속성을 결과 집합에 추가하거나 제거할 수 있습니다. @spid 옵션 매개변수는 특정 spid를 차단하는 세션과 잠금 요청에 대한 상세 정보를 제공합니다.
그림 1: sp_block 결과
SQL Server 2000에서는, 다음 문장을 사용해서 차단된 spid에 대한 정보를 알 수 있습니다.
|
select * from master..sysprocesses where blocked <> 0 |
저장 프로시저 sp_lock으로 관련된 잠금 정보를 볼 수 있습니다.
오래 걸리는 차단 식별
이전에 언급한대로, SQL Server에서 차단은 일반적인 것이며 트랜잭션의 일관성을 유지하기위해 필요한 논리적 잠금의 결과입니다. 그러나, 잠금에 대한 대기가 한계를 초과하면 응답 시간에 영향을 미칩니다. 오래 걸리는 차단을 식별하기 위해 BlockedProcessThreshold 구성 매개 변수를 사용하면 서버 범위의 차단 임계값을 구성할 수 있습니다. 임계값은 초 단위 시간을 정의합니다. 차단이 임계값을 초과하면 SQL Trace에서 추적할 수 있도록 이벤트가 발생합니다.
예를 들어, 다음과 같이 SQL Server Management Studio에서 차단 프로세스 임계값을 200초로 구성할 수 있습니다:
1.
Execute Sp_configure ‘blocked process threshold’, 200
2.
Reconfigure with override
차단 프로세스 임계값을 구성하고 나서, SQL Trace나 프로파일러를 가지고 추적 이벤트를 캡처합니다.
3.
SQL Trace를 사용한다면, event_id = 137로 sp_trace_setevent를 설정합니다.
4.
SQL Server 프로파일러를 사용한다면, (Errors and Warnings 개체 밑에) Blocked Process Report 이벤트 클래스를 선택합니다. 그림 2를 참조하십시오.
그림 2: 오래 걸리는 차단과 교착 상태 추적
참고 이것은 가벼운 추적 작업에 해당합니다. (1) 차단이 임계값을 초과하거나 (2) 교착상태가 발생할 때만 이벤트가 캡처됩니다. 차단 잠금에 대해 매 200초 간격으로 추적 이벤트가 발생합니다. 이는 600초 동안 차단된 단일 잠금의 경우 3번의 추적 이벤트가 발생하는 것입니다. 그림 3을 참조하십시오.
그림 3: 차단 보고 > 차단 임계값
추적 이벤트에는 차단하고 있는 프로세스와 차단 당하는 프로세스 양쪽의 전체 SQL 문장을 포함합니다. 위 경우 “Update Customers” 문장이 “Select from Customers” 문장을 차단하고 있습니다.
상대적으로, SQL Server 2000에서는 차단 시나리오를 검사하기 위해 Sysprocesses 시스템 테이블을 조회하고 결과를 처리하는 별도의 코드를 필요로 합니다.
sys.dm_db_index_operational_stats를 사용한 개체 별 차단
SQL Server 2005의 새로운 DMV인 sys.dm_db_index_operational_stats는 차단을 포함해서 총체적인 인덱스 사용 통계 정보를 제공합니다. 차단으로 표현하자면, 테이블, 인덱스, 분할 별 상세한 잠금 통계 정보를 제공합니다. 예를 들어 주어진 인덱스나 테이블에 대한 액세스, 잠금(row_lock_count), 차단(row_lock_wait_count), 그리고 대기(row_lock_wait_in_ms) 이력 정보를 포함합니다.
이 DMV를 통해서 얻을 수 있는 정보들은 다음과 같습니다:
ㆍ 잠금 보유 누적 개수, 예) 행 혹은 페이지.
ㆍ 차단 혹은 대기 누적 개수, 예) 행, 페이지.
ㆍ 차단 혹은 대기 누적 시간, 예) 행, 페이지.
ㆍ 페이지 래치 대기 누적 개수.
ㆍ page_latch_wait 대기 누적 시간: 순차 키 입력과 같은 경우의 특정 페이지에 대한 경합과 관련됩니다. 그런 경우, 마지막 페이지에 동시에 여러 프로세스가 쓰기 작업을 수행하기 위해 배타적 페이지 래치를 요구하게 되므로 마지막 페이지에 핫 스팟(hot spot)이 일어나게 됩니다. 이것이 Pagelatch 대기로 나타나게 됩니다.
ㆍ page_io_latch_wait 대기 누적 시간: I/O 래치는 사용자가 요청한 페이지가 버퍼 풀에 존재하지 않을 때 발생합니다. 느린 I/O 하위 시스템, 혹은 용량을 초과한 I/O 하위 시스템은 높은 PageIOlatch를 경험할 수 있으며 이것이 실제 I/O 문제를 나타냅니다. 이러한 문제는 캐시 플러시나 인덱스 부재 등에 의해서도 일어날 수 있습니다.
ㆍ 페이지 래치 대기 시간.[역주18]
차단 관련 정보 이외에도, 인덱스 액세스에 대한 추가 정보들이 제공됩니다.
ㆍ 액세스 유형, 예) 범위, 단일 조회.
ㆍ 리프 수준에서의 insert, update, delete.
ㆍ 리프 수준 상위 수준에서의 insert, update, delete. 리프 상위의 동작은 인덱스 유지 관리 작업니다. 상위 수준에는 각 리프 페이지의 첫 행이 입력됩니다. 리프에 새로운 페이지가 할당되면, 해당 페이지의 첫 행이 상위 페이지에 입력됩니다.
ㆍ 리프 수준에서의 페이지 병합은 행 삭제로 인해 할당이 해제된 빈 페이지를 나타냅니다.
ㆍ 인덱스 유지 관리. 리프 수준 상위에서의 페이지 병합은 리프에서 행 삭제로 인해 중간 수준 페이지가 빈 상태로 남겨지는, 할당 해제된 빈 페이지입니다. 리프 페이지의 첫 행은 상위 수준에 입력되어 있습니다. 리프 수준에서 충분한 수의 행이 삭제되면, 리프 페이지에 대한 첫 행을 입력으로 포함하고 있던 중간 수준의 인덱스 페이지들은 빈 페이지가 됩니다. 이것은 리프 상위 수준에서의 병합을 유발합니다.
이 정보는 SQL Server 인스턴스가 시작되면서부터 누적됩니다. 이 정보는 인스턴스가 재시작하면 사라지며, 또한 재설정하는 방법도 없습니다. 이 DMV에 의해서 반환되는 데이터는 해당 힙이나 인덱스를 나타내는 메타 데이터 캐시가 사용 가능한 경우에만 존재합니다. 힙이나 인덱스를 메타 데이터 캐시로 가져갈 때마다 각 열 값은 0으로 설정됩니다. 통계 정보는 메타 데이터 캐시에서 캐시 개체가 제거될 때까지 누적됩니다. 따라서, 이후 분석을 위해서는 주기적으로 데이터를 수집해서 저장해 두어야 합니다.
부록 B 인덱스 작업 데이터를 수집하는데 사용할 수 있는 저장 프로시저들을 제공합니다. 원하는 주기로 해당 데이터를 분석할 수 있습니다. 다음은 부록 B에 소개한 저장 프로시저를 사용하는 단계입니다.
1.
init_index_operational_stats 를 사용해서 indexstats를 초기화합니다.
2.
insert_indexstats를 사용해서 기준 상태를 캡처합니다.
3.
작업 부하를 발생시킵니다.
4.
insert_indexstats를 사용해서 인덱스 통계 정보에 대한 최종 정보를 캡처합니다.
5.
수집된 인덱스 통계 정보를 분석하기 위해서, 저장 프로시저 get_indexstats를 실행합니다. 평균 잠금 개수(인덱스와 분할에 대한 Row_lock_count), 차단, 그리고 인덱스 별 대기 정보를 생성합니다. 높은 값의 blocking % 혹은 높은 평균 대기는 인덱스 전략 문제나 쿼리 문제를 나타냅니다.
다음은 저장 프로시저를 사용해서 얻을 수 있는 정보들의 몇 가지 예제[역주19] 를 보여줍니다.
ㆍ 인덱스 사용량을 정렬 기준으로 모든 데이터베이스의 상위 5개 인덱스 출력.
exec get_indexstats
@dbid=-1,
@top='top 5',
@columns='index, usage',
@order='index usage'
ㆍ 잠금 승격을 시도한 적이 있는 상위 5개 인덱스 잠금 승격 출력.
exec get_indexstats
@dbid=-1,
@top='top 5',
@order='index lock promotions',
@threshold='[index lock promotion attempts] > 0'
ㆍ 평균 잠금 대기 시간이 2ms를 초과하는 상위 5개 단일 조회 작업에 대해 wait, scan, singleton열을 포함해서 출력.
exec get_indexstats
@dbid=5,
@top='top 5',
@columns='wait,scan,singleton',
@order='singleton lookups',
@threshold='[avg row lock wait ms] > 2'
ㆍ 모든 데이터베이스에서 행 잠금 대기가 1을 초과하는 상위 10개에 대해 avg, wait 열을 포함해서 대기 시간을 기준으로 출력.
exec get_indexstats
@dbid=-1,
@top='top 10 ',
@columns='wait,row',
@order='row lock wait ms',
@threshold='[row lock waits] > 1'
ㆍ 평균 행 잠금 대기 시간을 정렬 기준으로 상위 5개 행 출력.
exec get_indexstats
@dbid=-1,
@top='top 5',
@order='avg row lock wait ms'
ㆍ 평균 페이지 래치 대기 시간을 정렬 기준으로 상위 5개 행 출력.
exec get_indexstats
@dbid=-1,
@top='top 5',
@order='avg page latch wait ms'
ㆍ 평균 페이지 I/O 래치 시간을 정렬 기준으로 상위 3%의 인덱스 통계 출력.
exec get_indexstats
@dbid=-1,
@top='top 3 percent',
@order='avg pageio latch wait ms',
@threshold='[pageio latch waits] > 0'
ㆍ db=5에서 block%를 기준으로 0.1을 초과하는 상위 10위 정보 출력.
exec get_indexstats
@dbid=-1,
@top='top 10',
@order='block %',
@threshold='[block %] > 0.1'
그림 4는 차단 분석 보고 예제를 보여줍니다.
그림 4: 차단 분석 보고
SQL Server 2000에서는 개체나 인덱스 사용량에 대한 통계 정보를 제공하지 않습니다.
차단으로 인한 전반적인 성능 평가를 위해 SQL 대기 사용
SQL Server 2000은 76개의 대기 유형을 보고합니다. SQL Server 2005는 응용 프로그램 성능 추적을 위해서100개 이상의 추가 대기 유형을 제공합니다. 사용자 연결이 대기 중인 동안, SQL Server는 대기 시간을 누적합니다. 예를 들어, 응용 프로그램이 I/O, 잠금, 혹은 메모리 같은 리소스를 요청하면 해당 리소스를 사용 가능할 때까지 기다릴 수 있습니다. 작업 부하가 발생했을 때 해당하는 성능 프로필을 얻을 수 있도록 이러한 대기 정보들이 모든 연결에 걸쳐 요약되고 분류됩니다. 따라서, SQL 대기 유형은 응용 프로그램 작업 부하로부터 발생하는 사용자(혹은 쓰레드) 대기 정보를 식별하고 분류합니다.
다음 쿼리는 SQL Server에 상위 10위 대기 정보을 보여줍니다. 이 대기 정보는 누적되는 정보이지만DBCC SQLPERF ([sys.dm_os_wait_stats], clear)를 사용해서 재설정할 수가 있습니다.
|
select top 10 *
from sys.dm_os_wait_stats
order by wait_time_ms desc |
다음은 출력 결과입니다. 주목할 만한 몇 가지 핵심 요점은:
ㆍ 지연 기록기(lazy writer)와 같이 백그라운드 쓰레드로 인한 몇 가지 대기는 정상적입니다.
ㆍ 몇 가지 세션은 SH 잠금을 얻기 위해 장 시간 대기했습니다.
ㆍ signal wait은 작업자(worker)가 리소스에 액세스를 허가 받았을 때부터 CPU 예약을 획득한 시점까지의 대기 시간입니다. 오랜 signal wait은 높은 CPU 경합을 암시합니다.
|
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms
signal_wait_time_ms
------------------ -------------------- -------------------- -------------
------- -------
LAZYWRITER_SLEEP 415088 415048437 1812
156
SQLTRACE_BUFFER_FLUSH 103762 415044000 4000
0
LCK_M_S 6 25016812 23240921
0
WRITELOG 7413 86843 187
406
LOGMGR_RESERVE_APPEND 82 82000 1000
0
SLEEP_BPOOL_FLUSH 4948 28687 31
15
LCK_M_X 1 20000 20000
0
PAGEIOLATCH_SH 871 11718 140
15
PAGEIOLATCH_UP 755 9484 187
0
IO_COMPLETION 636 7031 203
0 |
대기 정보를 분석하기 위해서, 정기적으로 데이터를 수집해야 합니다. 부록 B에서 두 가지 저장 프로시저를 제공합니다.
ㆍ Track_waitstats. 원하는 샘플 숫자와 샘플 간격으로 데이터를 수집할 수 있습니다. 다음은 호출 예제입니다.
exec dbo.track_waitstats @num_samples=6
,@delay_interval=30
,@delay_type='s'
,@truncate_history='y'
,@clear_waitstats='y'
ㆍ Get_waitstats. 이전 단계에서 수집된 데이터를 분석합니다. 다음은 호출 예제입니다.
|
exec [dbo].[get_waitstats_2005] |
ㆍ spid가 실행 중입니다. 그 다음 현재 사용할 수 없는 리소스를 필요로 합니다. 리소스를 사용할 수 없으므로, 시간 T0에 리소스 대기 목록으로 이동합니다.
ㆍ Signal은 리소스가 사용 가능함을 나타냅니다, 따라서 spid는 시간 T1 에 실행 가능 큐로 이동합니다.
ㆍ spid는 cpu 가 실행 가능 큐에 도착한 순서대로 작업을 하고 T2에 실행 상태가 될 때까지 기다립니다.
이 저장 프로시저를 사용해서 리소스 대기와 신호(signal) 대기를 분석하거나 자원 경합을 격리시킬 수 있습니다.
그림5 보고서 예제
그림 5: 대기에 대한 통계 정보 분석 리포트
그림 5의 대기 분석 리포트 예제는 성능 문제가 차단(LCK_M_S)과 메모리 할당(RESOURCE_SEMAPHORE)에 있음을 알려줍니다. 특히 전체 대기 중 메모리 요구로 인한 문제가 43%인 반면, 55%가 공유 잠금입니다. 개체 별 차단 분석은 경합의 핵심 요점을 식별합니다.
인덱스 사용량 모니터링
쿼리 성능의 또 다른 관점은 DML 쿼리, 데이터를 삭제, 추가, 변경하는 쿼리와 관련됩니다. 테이블에 더 많은 인덱스를 정의하면 데이터를 수정하는데 더 많은 리소스를 필요로 합니다. 트랜잭션에서 점유하는 잠금과 결합해서, 오랜 시간 동안의 변경 작업은 동시성을 방해하게 됩니다. 따라서, 시간이 지난 뒤에 응용 프로그램에 의해서 사용되는 인덱스가 무엇인지 알아내는 것이 매우 중요합니다. 그리고 나서, 사용되지 않는 인덱스로 인해 데이터베이스 스키마가 불필요하게 크지 않은지를 알아내야 합니다.
SQL Server 2005는 sys.dm_db_index_usage_stats 동적 관리 뷰를 새로 제공합니다. 이 뷰는 어떤 인덱스가 사용되고 있는지, 해당 인덱스가 사용자 쿼리에 의해서 사용되는지 시스템 작업에 의해서만 사용되는지를 알려줍니다. 쿼리를 실행할 때마다, 쿼리에서 사용한 쿼리 계획에 따라서 이 뷰내의 열 값이 증가됩니다. 데이터는 SQL Server 실행 동안 수집됩니다. 이 DMV의 데이터는 메모리에서만 유지되며 영구히 보존되지는 않습니다. 따라서 SQL Server 인스턴스가 중지되면, 데이터는 손실됩니다. 정기적으로 데이터를 수집하고 저장해서 이후 분석을 위해 사용합니다.
인덱스에 대한 작업은 사용자 유형과 시스템 유형으로 분류됩니다. 사용자 유형은 SELECT와 INSERT/UPDATE/DELETE 작업입니다. 시스템 유형은 DBCC나 DDL 명령 혹은 update statistics와 같은 명령입니다. 각 문장의 범주별로 열이 구별됩니다:
ㆍ 인덱스에 대한 검색 (user_seeks나 system_seeks)
ㆍ 인덱스에 대한 조회 작업 (user_lookups 나 system_lookups)
ㆍ 인덱스에 대한 스캔 작업 (user_scans 나 system_scans)
ㆍ 인덱스에 대한 변경 작업 (user_updates 나 system_updates)
인덱스 액세스 유형별로, 마지막 액세스 시간 또한 알 수 있습니다.
인덱스 자체는 세 가지 칼럼, database_id, object_id, index_id를 통해서 알 수 있습니다. index_id = 0은 힙 테이블을 나타내며, index_id = 1은 클러스터형 인덱스를, index_id > 1인 경우는 비클러스터형 인덱스를 나타냅니다.
SQL Server 2005에서 검색, 스캔, 그리고 조회 동작에 대한 규칙과 정의는 다음과 같습니다.
ㆍ 검색: 데이터 액세스를 위해서 B-트리 구조가 사용된 카운트를 나타냅니다. 한 행을 찾기 위해서 인덱스 각 수준에서 적은 페이지를 읽거나 혹은 기가바이트 데이터나 수 백만 행을 읽기위해 인덱스 페이지 반을 읽었더라도 문제가 되지 않습니다. 따라서 인덱스를 사용한 것으로 해당 범주에 누적됩니다.
ㆍ 스캔: B-트리 인덱스 중 하나를 사용하지 않고 테이블의 데이터 계층을 사용해서 스캔을 한 카운트를 나타냅니다. 테이블에 인덱스가 없는 경우가 이에 해당합니다. 인덱스가 존재하지만 쿼리에 의해서 사용되지 않은 경우에도 해당합니다.
ㆍ 조회: 비클러스터형 인덱스가 사용된 경우를 ‘검색”으로 정의한다면, 추가로 클러스터형 인덱스(혹은 힙)를 사용해서 데이터를 조회한 경우를 나타냅니다. 이는 SQL Server 2000에서 책갈피 조회(bookmark lookup) 시나리오를 나타냅니다. 이는 테이블 액세스에 사용된 비클러스터형 인덱스가 쿼리 select 목록에 열과 where 절에 정의된 열을 모두 포함하지 못하는 시나리오를 나타냅니다. SQL Server는 비클러스터형 인덱스에 대해 user_seeks 열 값을 증가시키고 클러스터형 인덱스 항목의 user_lookups 열에 더합니다. 이 카운트는 테이블에 비클러스형 인덱스가 많은 경우 매우 높은 값을 가질 수 있습니다. 클러스터형 인덱스에 user_seeks 값이 매우 높고, 더불어 user_lookups 값도 높으며 특정 비클러스터형 인덱스의 user_seeks 값 또한 매우 높다면, 높은 값을 가진 비클러스터형 인덱스를 클러스터형 인덱스로 바꾸어 만들면 쿼리 성능을 더 향상시킬 수도 있습니다.
다음 DMV 쿼리로 모든 데이터베이스의 모든 개체에 대해 인덱스 사용량에 대한 유용한 정보를 알 수 있습니다.
|
select object_id, index_id, user_seeks, user_scans, user_lookups
from sys.dm_db_index_usage_stats
order by object_id, index_id
다음 결과는 특정 테이블 정보를 보여줍니다.
object_id index_id user_seeks user_scans user_lookups
------------ ------------- -------------- -------------- -----------------
521690298 1 0 251 123
521690298 2 123 0 0 |
이 경우, 인덱스를 사용하지 않고 테이블의 데이터 계층을 직접 액세스한 쿼리가 251번 실행되었습니다. 첫 번째 비클러스터형 인덱스를 사용해서 테이블을 액세스한 쿼리가 123번 실행되었습니다. 이는 쿼리의 select 열이나 WHERE 절에 지정된 열이 비클러스터형 인덱스에 모두 존재하지 않으므로 클러스터형 인덱스를 123번 조회한 것입니다.
가장 관심있게 살펴볼 범주는 ‘사용자 유형 문장’ 입니다. ‘시스템 범주’내에 나타나는 사용량은 기존 인덱스에 결과로 보여질 수 있습니다. 인덱스가 존재하지 않으면, 통계 정보를 변경하거나 일관성을 검사할 필요가 없기 때문입니다. 따라서, 임시(ad hoc) 문장이나 사용자 응용 프로그램에 의한 사용량을 나타내는 4개의 칼럼에 집중해서 분석합니다.
SQL Server가 시작된 이후에 특정 테이블에서 사용되지 않은 인덱스에 대한 정보를 얻기 위해서, 해당 테이블이 존재하는 데이터베이스에서 다음 쿼리를 실행합니다.
|
select i.name
from sys.indexes i
where i.object_id=object_id('<table_name>') and
i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid>[역주20] ) |
사용되지 않은 모든 인덱스 정보는 다음 쿼리로 알 수 있습니다:
|
select object_name(object_id), i.name
from sys.indexes i
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
order by object_name(object_id) asc |
이 경우, 테이블 이름과 인덱스 이름은 테이블 이름에 따라서 정렬됩니다.
실제로 이 동적 관리 뷰의 목적은 오랫 동안 인덱스의 사용량을 관찰하는 것입니다. 뷰나 쿼리를 수행한 결과를 매일 혹은 특정 주기로 저장하고 시간이 지나면서 각각 비교를 하는 것입니다. 분기별 보고나 회계연도 보고와 같이 한 달 혹은 특정 기간 동안 사용되지 않는 인덱스를 알아낸다면, 데이터베이스에서 해당 인덱스를 삭제할 수 있습니다. |