SQL Server 확장 이벤트를 사용한 Tempdb 병목현상 추적

SQL Server 확장 이벤트를 사용한 Tempdb 병목현상 추적

 

• Version : SQL Server 2008, 2008R2, 2012

 

SQL Server의 성능 관련해서는 Tempdb와 관련된 문제가 많다. Tempdb에서는 크고 작은 임시 테이블과 작업 테이블을 만들어서 사용한다. SQL Server를 운용하는데 있어서 매우 중요한 부분이다. 이때 Tempdb를 제대로 구성하지 않을 경우에는 할당하는 동안 병목이 발생 하여 SQL Server 성능은 심각한 저하가 발생한다. 할당 병목이 발생한 경우 어떻게 찾을 수 있을까? 또한 성능을 향상 시키기 위해 어떻게 해야 할까?

 

할당 병목 이란? : http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/04/what-is-allocation-bottleneck.aspx

 

SQL Server 2005에서는 sys.dm_os_waiting_tasks 라는 DMV를 통하여 할당 병목 현상을 추적 할 수 있다. DMV는 현재 자원을 기다리고 있는 작업에 대한 정보를 반환한다

select session_id, wait_duration_ms, resource_description from sys.dm_os_waiting_tasks where wait_type like 'PAGE%LATCH_%' AND resource_description like '2.%'

 

SQL Server 2005에서는 sys.dm_os_waiting_tasks를 통하여 대기 정보를 확인 하지만 세션 레벨의 세분화된 수준에서 대기를 확인 할 수 없었다.

SQL Server 2008 부터는 확장 이벤트를 사용하여 Tempdb의 할당을 모니터링 할 수 있다. SQL Server2008에 새로 도입된 확장 이벤트는 낮은 오버헤드로 추적이 가능하다. 또한 개별 데이터베이스 수준에서 세션레벨 성능 모니터를 캡처 할 수 있다.

select * from sys.dm_xe_objects as a     inner join sys.dm_xe_packages as b on b.guid = a.package_guid where a.object_type = 'event' and a.name like '%wait%' go   select * from sys.dm_xe_object_columns where object_name = 'wait_info' go   select * from sys.dm_xe_objects as a     inner join sys.dm_xe_packages as b on b.guid = a.package_guid where a.object_type = 'action' go

 

 

 

확장 이벤트를 생성하여 캡처하는 방법을 알아 보자. 아래 코드는 Ringbuffer의 오버헤드를 피하기 위하여 filetarget 명령을 사용하였다. Ringbuffer는 4MB 보다 큰 데이터를 캡쳐하는 경우 다음 ringbuffer 데이터를 덮어 쓰게 된다.

--Drop the event if it already exists DROP EVENT SESSION Monitor_wait_info_tempdb ON SERVER; GO --Create the event CREATE EVENT SESSION Monitor_wait_info_tempdb ON SERVER --We are looking at wait info only ADD EVENT sqlos.wait_info ( --Add additional columns to track ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.session_id, sqlserver.tsql_stack) WHERE sqlserver.database_id = 2 --filter database id = 2 i.e tempdb --This allows us to track wait statistics at database granularity ) --As a best practise use asynchronous file target, reduces overhead. ADD TARGET package0.asynchronous_file_target( SET filename='d:\Monitor_wait_info_tempdb.etl', metadatafile='d:\Monitor_wait_info_tempdb.mta') GO --Now start the session ALTER EVENT SESSION Monitor_wait_info_tempdb ON SERVER STATE = START; GO

 

확장 이벤트가 실행 되면 다음과 같이 해당 폴더에 파일로 정보가 저장 되는 것을 확인 할 수 있다.

 

 

테이블 변수와 임시 테이블을 사용하여 Tempdb에 부하를 발생 한다.

--Now run the test load, using table variables, temp tables, temp tables with named constraints DECLARE @test TABLE (c1 INT NOT NULL, c2 datetime) INSERT @test SELECT 1, GETDATE() --drop table #test GO 1000 CREATE TABLE #test (c1 INT NOT NULL, c2 datetime) INSERT #test SELECT 1, GETDATE() DROP TABLE #test GO 1000 CREATE TABLE #test (c1 INT NOT NULL, c2 datetime, CONSTRAINT pk_test PRIMARY KEY CLUSTERED(c1)) INSERT #test SELECT 1, GETDATE() DROP TABLE #test GO 1000

 

 

위의 스크립트 작업이 완료되면 어디서 대기가 발생하였는지 파일의 정보를 읽어 요약 정보를 확인 할 수 있다.

SELECT wait_typeName , SUM(total_duration) AS total_duration , SUM(signal_duration) AS total_signal_duration FROM ( SELECT FinalData.R.value ('@name', 'nvarchar(50)') AS EventName, FinalData.R.value ('data(data/value)[1]', 'nvarchar(50)') AS wait_typeValue, FinalData.R.value ('data(data/text)[1]', 'nvarchar(50)') AS wait_typeName, FinalData.R.value ('data(data/value)[5]', 'int') AS total_duration, FinalData.R.value ('data(data/value)[6]', 'int') AS signal_duration, FinalData.R.value ('(action/.)[1]', 'nvarchar(50)') AS DatabaseID, FinalData.R.value ('(action/.)[2]', 'nvarchar(50)') AS SQLText, FinalData.R.value ('(action/.)[3]', 'nvarchar(50)') AS SessionID   FROM ( SELECT CONVERT(xml, event_data) AS xmldata FROM sys.fn_xe_file_target_read_file ('d:\Monitor_wait_info_tempdb*.etl', 'd:\Monitor_wait_info_tempdb*.mta', NULL, NULL) ) AsyncFileData CROSS APPLY xmldata.nodes ('//event') AS FinalData (R)) xyz WHERE wait_typeName NOT IN ('SLEEP_TASK') GROUP BY wait_typeName ORDER BY total_duration GO

 

 

 

Tempdb에 대한 할당 병목 현상을 확인 하였다면 할당 병목을 줄이기 위해 다음의 방법을 시도 할 수 있다.

 

1. Tempdb의 파일을 분할 한다. 분할 개수는 권장은 코어 수 만큼 또는 1/4, 1/2 이나 테스트 후 자신의 환경에 맞게 설정 한다.
2. 과도하게 임시 테이블을 사용하는 쿼리를 수정한다.
3. 불필요한 IO가 발생하지 않도록 인덱스를 추가 한다.
4. TF 1118을 사용하여 혼합 익스텐트를 유니폼 하게 할당 한다.

 

Tempdb 동시성 경합: http://sqlmvp.kr/140164023333

 

원문 참고 링크 : http://www.mssqltips.com/sqlservertip/1853/sql-server-tempdb-usage-and-bottlenecks-tracked-with-extended-events/

 

 

2012-12-28 / 강성욱 / http://sqlmvp.kr