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

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

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/



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1710 SQL Server 특정 세션에 대한 마지막 실행 문장 확인 jevida(강성욱) 2016.09.13 713
1709 DMV - 데이터베이스 버퍼 메모리 사용량 확인 jevida(강성욱) 2016.09.13 2951
1708 Sys.dm_fts_parser을 이용한 문자열 구문 분석 jevida(강성욱) 2016.09.13 1286
1707 DMV에서 SQL Server 리소스 데이터베이스 값 jevida(강성욱) 2016.09.13 1063
1706 키워드로 PROCEDURE, FUNCTION 찾기 jevida(강성욱) 2016.09.13 1559
1705 BCP 사용 jevida(강성욱) 2016.09.13 3351
1704 DATEADD를 사용한 날짜 추가 및 빼기 jevida(강성욱) 2016.09.13 1153
1703 Change Data Capture(CDC) – 변경 이력 추적 jevida(강성욱) 2016.09.13 2445
1702 SSMS 에서 디버깅 하기 jevida(강성욱) 2016.09.13 6819
1701 CLR 등록 및 활성화 하기 jevida(강성욱) 2016.09.13 1560
» SQL Server 확장 이벤트를 사용한 Tempdb 병목현상 추적 jevida(강성욱) 2016.09.13 1606
1699 MAXDOP 설정 jevida(강성욱) 2016.09.13 1784
1698 SQL Server 이름 변경 하기 jevida(강성욱) 2016.09.13 1575
1697 SQL Server 마지막 시작 시간 확인 하기 jevida(강성욱) 2016.09.13 702
1696 여러 포트를 사용하도록 SQL Server 구성 jevida(강성욱) 2016.09.13 1800
1695 SQL Server가 사용중인 TCP/IP 포트 확인 jevida(강성욱) 2016.09.13 10914
1694 SQL Server SP_Congifure 변경 사항 캡처 jevida(강성욱) 2016.09.13 2061
1693 SQL Server Net Libraries jevida(강성욱) 2016.09.13 1108
1692 SQL Server 별칭 설정 및 사용 jevida(강성욱) 2016.09.13 2614
1691 SQL Server에 연결된 어플리케이션 드라이버 버전 확인 jevida(강성욱) 2016.09.13 880





XE Login