확장이벤트 사용시 주의사항

 

  • Version : SQL Server 2012, 2014, 2016

 

SQL Serve Extended Event (xevent, 확장이벤트)는 다양한 이슈 및 성능을 해결하기 위한 매우 좋은 도구이다. 하지만 확장이벤트는 사용방법에 따라 오버헤드가 발생하기 때문에 주의하여야 한다.

 

아래 사례는 Microsoft CSS SQL Server Engineers 공식 블로그에 게시된 내용으로 고객 사례를 바탕으로 주의점을 설명하였다. 자세한 내용은 원문을 참고 한다.

 

고객 사례는 간단한 쿼리를 실행하는데 오랜 시간이 걸리데, 덤프 및 호출 스택을 분석한 결과 xevent가 포함된 것을 확인하였고 높은 오버헤드를 일으킬 수 있는 활성화된 이벤트를 발견하였다. 확장이벤트에는 scan_start, scan_stop, wait_info등 다양한 이벤트가 매분마다 수백만 이벤트가 캡처되고 있었다. 특히 모든 이벤트에 대해 SQL_TEXT를 수집하여 높은 오버헤드를 유발 하였다. (아래 그림 참고)

 

 

 

 

위에 언급된 이벤트(scan_started, scan_stopped, wait_info)는 장시간 캡처에는 적합하지 않다. 특히 SQL_TEXT를 포함한 wait_info 캡처는 스케줄러 대기가 발생할때마다 생성되므로 매우 빈번하게 이벤트가 발생하여 이번 오버헤드의 주요 원인 이었다. 이처럼 대규모 작업에서는 SQL_TEXT를 배제하는 것이 좋다.

 

확장이벤트는 다양한 문제를 해결하는데 좋은 도구이지만 무분별한 사용은 많은 오버헤드를 발생시키므로 필터를 잘 적용하여 오버헤드가 발생하지 않도록 설정해서 사용해야 한다.

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2016/02/24/not-every-extended-event-is-suited-for-all-situations/

 

 

2016-05-13 / 강성욱 / http://sqlmvp.kr

 

 

MS SQL, SQL Server, 확장이벤트, Xevent, Extended Event, DB 튜닝, DB 성능, DB 트러블슈팅, DB 모니터링

확장이벤트를 사용한 실행 계획 캡처

 

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

 

확장이벤트를 사용하여 실행 계획을 캡처하는 방법에 대해서 알아본다. 캡처된 쿼리 실행 목록에 다음과 같은 이름이 있다면 플랜을 확인하여 검토할수 있도록 한다.

  • NojoinPredicate (2005 이상)
  • ColumnWithNoStatistics (2005 이상)
  • UnmatchedIndexes (2008 이상)
  • PlanAffectingConvert (2012 이상)

 

아래 스크립트는 확장이벤트를 생성한다. 확장이벤트 생성시 너무 많은 오버헤드가 발생하지 않도록 필요한 내용만 추가하도록 한다. 아래 확장 이벤트는 SQL_Text(쿼리문)과 plan_handle(실행계획)을 포함한다.

-- Remove event session if it exists

IF EXISTS (SELECT 1 FROM [sys].[server_event_sessions]

WHERE [name] = 'InterestingPlanEvents')

BEGIN

DROP EVENT SESSION [InterestingPlanEvents] ON SERVER

END

GO

 

-- Define event session

CREATE EVENT SESSION [InterestingPlanEvents]

ON SERVER

ADD EVENT sqlserver.missing_column_statistics

(

ACTION(sqlserver.database_id,sqlserver.plan_handle,sqlserver.sql_text)

WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))

AND [sqlserver].[database_id]>(4))

),

ADD EVENT sqlserver.missing_join_predicate

(

ACTION(sqlserver.database_id,sqlserver.plan_handle,sqlserver.sql_text)

WHERE ([sqlserver].[is_system]=(0) AND [sqlserver].[database_id]>(4))

),

ADD EVENT sqlserver.plan_affecting_convert

(

ACTION(sqlserver.database_id,sqlserver.plan_handle,sqlserver.sql_text)

WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))

AND [sqlserver].[database_id]>(4))

),

ADD EVENT sqlserver.unmatched_filtered_indexes

(

ACTION(sqlserver.plan_handle,sqlserver.sql_text)

WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))

AND [sqlserver].[database_id]>(4))

)

ADD TARGET package0.event_file

(

SET filename=N'd:\SQL_Data\InterestingPlanEvents' /* change location if appropriate */

)

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,

TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)

GO

 

-- Start the event session

ALTER EVENT SESSION [InterestingPlanEvents] ON SERVER STATE=START;

GO

 

확장 이벤트 생성이 완료되면 SSMS의 [관리]-[확장이벤트] 메뉴에서 실행중인 확장이벤트를 확인할 수 있다.

 

이벤트를 발생시키기 위해 아래 스크립트를 실행한다. 예제는 AdventureWorks2012 (2014)에서 진행하였다.

-- These queries assume a FRESH restore of AdventureWorks2014

ALTER DATABASE [AdventureWorks2012] SET AUTO_CREATE_STATISTICS OFF;

GO

 

USE [AdventureWorks2012];

GO

 

CREATE INDEX [NCI_SalesOrderHeader] ON [Sales].[SalesOrderHeader] (

[PurchaseOrderNumber], [CustomerID], [TotalDue], [DueDate]

)

WHERE [SubTotal] = 10000.00;

GO

 

/*

No join predicate

NOTE: We clear procedure here because the event ONLY fires for the *initial* compilation

*/

DBCC FREEPROCCACHE; /* Not for production use */

 

SELECT [h].[SalesOrderID], [d].[SalesOrderDetailID], [h].[CustomerID]

FROM [Sales].[SalesOrderDetail] [d],

[Sales].[SalesOrderHeader] [h]

WHERE [d].[ProductID] = 897;

GO

 

-- Columns with no statistics

SELECT [BusinessEntityID], [NationalIDNumber], [JobTitle], [HireDate], [ModifiedDate]

FROM [HumanResources].[Employee]

WHERE [HireDate] = '2013-01-01';

GO

 

-- Unmatched Index

DECLARE @Total MONEY = 10000.00;

 

SELECT [PurchaseOrderNumber], [CustomerID], [TotalDue], [DueDate]

FROM [Sales].[SalesOrderHeader]

WHERE [SubTotal] = @Total;

GO

 

-- Plan Affecting Convert

SELECT [BusinessEntityID], [NationalIDNumber], [JobTitle], [HireDate], [ModifiedDate]

FROM [HumanResources].[Employee]

WHERE [NationalIDNumber] = 345106466;

GO

 

ALTER EVENT SESSION [InterestingPlanEvents]

ON SERVER

STATE=STOP;

GO

 

이벤트 세션을 중지한 후 캡처된 내용을 SSMS에서 확인할 수 있다.

 

 

조인 조건이 없는 쿼리문이 캡처되었으며 sql_text 필드에는 쿼리에 대한 텍스트를 확인할 수 있으며 plan_handle 항목에서는 실행된 쿼리의 계획을 보여준다. Plan_hadle 값을 sys.dm_exec_query_plan을 사용하여 xml 형식의 실행 계획을 확인할 수 있다.

select * from sys.dm_exec_query_plan(0x06000600B9B79001F036B96B0400000001000000000000000000000000000000000000000000000000000000)

 

 

SSMS에서 조회된 XML을 클릭하면 실행계획을 그림으로 변환하여 보여준다.

 

아래 스크립트를 실행하여 실습에 진행한 확장이벤트를 삭제하고 AdventureWorks2012의 옵션을변경하고 인덱스를 삭제한다.

DROP EVENT SESSION [InterestingPlanEvents]

ON SERVER;

GO

 

ALTER DATABASE [AdventureWorks2012] SET AUTO_CREATE_STATISTICS ON;

GO

 

DROP INDEX [NCI_SalesOrderHeader] ON [Sales].[SalesOrderHeader];

GO

 

 

[참고자료]

http://sqlperformance.com/2015/10/extended-events/capture-plan-warnings

 

2015-10-29 / 강성욱 / http://sqlmvp.kr

 

MSSQL, SQL Server, 확장이벤트, Xtended event, 실행계획 캡처, 쿼리 실행 계획, 쿼리 분석

XEvent(확장이벤트)를 활용한 활성 로그 모니터링 하기

 

  • Version : SQL Server 2012, 2014

 

XEvent의 file_wirte_complete, transaction_log 이벤트를 사용하여 트랜잭션이 커밋될 때 트랜잭션 로그 파일에 기록되는 것을 모니터링할 수 있다.

 

실습을 위해 테스트 테이블을 생성한다.

CREATE TABLE TestTable (

c1 INT IDENTITY,

c2 CHAR (1000) DEFAULT 'a');

GO

 

INSERT INTO [TestTable] DEFAULT VALUES;

GO

 

XEvent를 설정한다.

-- Drop the session if it exists.

IF EXISTS (

SELECT * FROM sys.server_event_sessions

WHERE [name] = N'MonitorLog')

DROP EVENT SESSION [MonitorLog] ON SERVER

GO

 

-- Create the event session

CREATE EVENT SESSION [MonitorLog] ON SERVER

ADD EVENT [sqlserver].[file_write_completed],

ADD EVENT [sqlserver].[transaction_log]

ADD TARGET [package0].[ring_buffer]

WITH (MAX_MEMORY = 50MB, max_dispatch_latency = 1 seconds)

GO

 

-- Start the session

ALTER EVENT SESSION [MonitorLog] ON SERVER

STATE = START;

GO

 

 

SSMS에서 XEvent의 라이브데이터 감시를 UI를 통해서 확인할 수 있다.

 

 

라이브데이터 감시가 시작되면 상단의 컬럼탭에서 마우스 오른쪽을 클릭하여 [열 선택]을 클릭한다.

 

 

[열 선택] 창이 나타나면 필요한 열을 추가하여 모니터링에 표시할 수 있도록 한다.

 

데이터를 입력하면 트랜잭션 로그가 커밋 될 때 트랜잭션로그파일에 기록되는 것을 확인할 수 있다.

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/t-sql-tuesday-67-monitoring-log-activity-with-extended-events/

 

 

2015-06-10 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MSSQL, DB, 확장이벤트, XEvent, Extended Event, 트랜잭션로그, 로그 모니터링, DB모니터링, 트랜잭션 커밋, 활성 로그

백업 확장이벤트로 백업 진행 과정과 소요되는 시간 확인

 

  • Version : SQL Server 2016

 

SQL Server 2016에서 추가된 새로운 확장 이벤트 sqlserver.backup_restore_progress_trace

를 사용하여 백업과정과 각 과정에서 소요되는 시간을 확인할 수 있다.

 

실습을 위해 확장이벤트를 생성한다.

CREATE EVENT SESSION [Backup progress] ON SERVER

ADD EVENT sqlserver.backup_restore_progress_trace

(

ACTION(package0.event_sequence)

 

-- to only capture backup operations:

--WHERE [operation_type] = 0

 

-- to only capture restore operations:

--WHERE [operation_type] = 1

)

ADD TARGET package0.event_file

(

SET filename = N'C:\SQL_Backup\BackupProgress.xel'

); -- default options are probably ok

GO

 

ALTER EVENT SESSION [Backup progress] ON SERVER STATE = START;

GO

 

데이터베이스를 생성하고 데이터를 입력한다.

create database SW_Test

go

 

SELECT s1.* INTO SW_Test.dbo.what

FROM sys.all_objects AS s1

CROSS JOIN sys.all_objects;

GO

 

백업과 복원을 실행한다.

BACKUP DATABASE SW_Test TO DISK = 'c:\SQL_Backup\SW_Test.bak'

WITH INIT, COMPRESSION, STATS = 30;

GO

 

DROP DATABASE SW_Test;

GO

 

RESTORE DATABASE SW_Test FROM DISK = 'c:\SQL_Backup\SW_Test.bak'

WITH REPLACE, RECOVERY;

 

 

수집된 데이터를 조회한다. Duration 단위는 밀리세컨드이다.

;WITH x AS

(

SELECT ts,op,db,msg,es

FROM

(

SELECT

ts = x.value(N'(event/@timestamp)[1]', N'datetime2'),

op = x.value(N'(event/data[@name="operation_type"]/text)[1]', N'nvarchar(32)'),

db = x.value(N'(event/data[@name="database_name"])[1]', N'nvarchar(128)'),

msg = x.value(N'(event/data[@name="trace_message"])[1]', N'nvarchar(max)'),

es = x.value(N'(event/action[@name="event_sequence"])[1]', N'int')

FROM

(

SELECT x = CONVERT(XML, event_data)

FROM sys.fn_xe_file_target_read_file

(N'c:\SQL_Backup\BackupProgress*.xel', NULL, NULL, NULL)

) AS y

) AS x

WHERE op = N'Backup' -- N'Restore'

AND db = N'SW_Test'

AND ts > CONVERT(DATE, SYSDATETIME())

)

SELECT x.db, x.op, x.ts,

[Message] = x.msg,

Duration = COALESCE(DATEDIFF(MILLISECOND, x.ts,

LEAD(x.ts, 1) OVER(ORDER BY es)),0)

FROM x

ORDER BY es;

 

 

 

[참고자료]

http://sqlperformance.com/2015/06/extended-events/t-sql-tuesday-67-backup-restore

 

 

2015-06-16 / 강성욱 / http://sqlmvp.kr

 

Sqlserver 2016, mssql, sqlserver, DBA, Backup, 백업, 데이터베이스 백업, 확장이벤트, XEvent, sqlserver.backup_restore_progress_trace, 백업소요시간, SQL Backup

+ Recent posts