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

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

 

• 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, 실행계획 캡처, 쿼리 실행 계획, 쿼리 분석