확장이벤트를 사용한 실행 계획 캡처
- 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
