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

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

확장 이벤트를 사용한 CPU 고부하 쿼리 추적

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

 

SQL Server를 운영 할 때 일부 쿼리에서 과도한 자원을 점유하여 성능에 문제를 발생 시킨다. 다음은 CPU를 많이 사용하는 쿼리를 추적 하는 기술에 대해서 알아본다.

 

다음 스크립트를 사용하여 실습용 데이터베이스 및 테이블을 생성한다. 데이터베이스 ID는 사용자마다 다를 수 있다.

CREATE DATABASE production;

GO

 

USE production;

GO

 

CREATE TABLE t1 (c1 INT IDENTITY, c2 UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID(), c3 CHAR (5000) DEFAULT 'a');

CREATE CLUSTERED INDEX t1_CL ON t1 (c1);

CREATE NONCLUSTERED INDEX t1_NCL ON t1 (c2);

GO

 

SET NOCOUNT ON;

 

INSERT INTO t1 DEFAULT VALUES;

GO 1000

 

--Get the database ID to plug into the event session

SELECT DB_ID ('production');

GO

 

 

확장 이벤트 조건을 정의 할 때 데이터베이스 ID를 지정한다.

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'EE_ExpensiveQueries')

    DROP EVENT SESSION EE_ExpensiveQueries ON SERVER;

GO

 

CREATE EVENT SESSION EE_ExpensiveQueries ON SERVER

ADD EVENT sqlserver.sql_statement_completed

(ACTION (sqlserver.sql_text, sqlserver.plan_handle)

WHERE sqlserver.database_id = 14 /*DBID*/ AND CPU_time > 10 /*total ms of CPU time*/)

ADD TARGET package0.asynchronous_file_target

(SET FILENAME = N'C:\SQL_Data\EE_ExpensiveQueries.xel', METADATAFILE = N'C:\SQL_Data\EE_ExpensiveQueries.xem')

WITH (max_dispatch_latency = 1 seconds);

GO

 

 

이벤트 세션을 시작하고 이벤트 조건에 만족하도록 쿼리를 실행 한다.

ALTER EVENT SESSION EE_ExpensiveQueries ON SERVER STATE = START;

GO

 

USE production;

GO

 

SELECT COUNT (*) FROM t1 WHERE c1 > 500;

GO

 

SELECT SUM (c1) FROM t1 WHERE c3 LIKE 'a';

GO

 

ALTER INDEX t1_CL ON t1 REORGANIZE;

GO

 

 

쿼리 실행이 완료 되었으면 캡처 된 쿼리가 있는지 확이 한다. 실습에서는 2개의 쿼리가 캡처 되었다.

USE master;

GO

 

SELECT COUNT (*) FROM sys.fn_xe_file_target_read_file

('C:\SQL_Data\EE_ExpensiveQueries*.xel', 'C:\SQL_Data\EE_ExpensiveQueries*.xem', NULL, NULL);

GO

 

 

 

캡처된 쿼리를 다음의 스크립트를 사용하여 확인 할 수 있다. 결과는 XML로 반환 된다.

SELECT data FROM

(SELECT CONVERT (XML, event_data) AS data FROM sys.fn_xe_file_target_read_file

('C:\SQL_Data\EE_ExpensiveQueries*.xel', 'C:\SQL_Data\EE_ExpensiveQueries*.xem', NULL, NULL)

) entries;

GO

 

 

 

다음 스크립트를 사용하여 XML BLOB 정보를 확인 할 수 있다.

SELECT

data.value (

'(/event[@name=''sql_statement_completed'']/@timestamp)[1]', 'DATETIME') AS [Time],

data.value (

'(/event/data[@name=''cpu'']/value)[1]', 'INT') AS [CPU (ms)],

CONVERT (FLOAT, data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT')) / 1000000

AS [Duration (s)],

data.value (

'(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [SQL Statement],

SUBSTRING (data.value ('(/event/action[@name=''plan_handle'']/value)[1]', 'VARCHAR(100)'), 0, 100)

AS [Plan Handle]

FROM

(SELECT CONVERT (XML, event_data) AS data FROM sys.fn_xe_file_target_read_file

('C:\SQL_Data\EE_ExpensiveQueries*.xel', 'C:\SQL_Data\EE_ExpensiveQueries*.xem', null, null)

) entries

ORDER BY [Time] DESC;

GO

 

 

 

위의 PlanHandle 값을 사용하여 sys.dm_exec_query_plan DMV에서 쿼리 플랜을 확인 할 수 있다. (Plan handle 값에서 0x를 붙여 사용한다.)

SELECT [query_plan] FROM sys.dm_exec_query_plan (0x06000e00faf5b11e80166a4e0200000001000000000000000000000000000000000000000000000000000000);

GO

 

 

CPU를 많이 사용한 쿼리의 실행 계획을 확인 할 수 있다.

 

 

확장 이벤트를 사용하면 서버에서 발생하는 여러 가지 상황들을 캡처하여 분석 할 수 있다.

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/tracking-expensive-queries-with-extended-events-in-sql-2008/

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
» 확장 이벤트를 사용한 CPU 고부하 쿼리 추적 [1] jevida(강성욱) 2016.10.15 2140
1932 데이터에 대한 이해와 spill in tempdb jevida(강성욱) 2016.10.13 1772
1931 로그 파일이 많으면 왜 안 좋은가 jevida(강성욱) 2016.10.13 2071
1930 트랜잭션 백업 실패와 전체 백업 성공 그리고 대처 방안 jevida(강성욱) 2016.10.13 1554
1929 Fast recovery 와 로그 잠금 jevida(강성욱) 2016.10.13 2032
1928 고스트 클린업 jevida(강성욱) 2016.10.13 2269
1927 페이지 분할이 발생 하였을 때 롤백을 하면 어떻게 될까? jevida(강성욱) 2016.10.13 1662
1926 DBCC WRITEPAGE - DBCC 명령을 사용한 데이터 파괴하기 jevida(강성욱) 2016.10.13 1651
1925 SQL Server Backup Error 3023 jevida(강성욱) 2016.10.13 2317
1924 Delete 작업과 페이지 offset 변화 jevida(강성욱) 2016.10.13 1488
1923 트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구 jevida(강성욱) 2016.10.13 5301
1922 PFX 형식의 인증서를 SQL Server에서 사용하기 jevida(강성욱) 2016.10.13 1163
1921 SQL Server NUMA 메모리 노드와 Operating System 접근 jevida(강성욱) 2016.10.13 1310
1920 SQL Server 에러 핸들링 비용 비교 jevida(강성욱) 2016.10.13 1320
1919 압축 백업 시 Checksum 옵션으로 손상 확인하기 jevida(강성욱) 2016.10.13 1657
1918 SQL Server 2012 Memory Manager 구성 jevida(강성욱) 2016.10.13 1274
1917 SQL Server Memory Manager 변화 jevida(강성욱) 2016.10.13 1505
1916 Ring_Buffer_Resource_Monitor jevida(강성욱) 2016.10.13 1005
1915 SQL Server 프로파일러 템플릿 만들기 jevida(강성욱) 2016.10.13 1356
1914 SQL Server 메모리 병목 현상 식별 jevida(강성욱) 2016.10.13 1833





XE Login