RECOMPILE

--1단계 : 가장 많은 리컴파일 데이터베이스 찾기

CREATE EVENT SESSION [XE_Database_Recompile_Histogram] ON SERVER

ADD EVENT sqlserver.sql_statement_recompile(

ACTION(sqlserver.database_id)

WHERE ([package0].[counter]<(2000)))

ADD TARGET package0.histogram(

SET filtering_event_name = N'sqlserver.sql_statement_recompile'

, slots = (100)

, source = N'sqlserver.database_id')

WITH (

MAX_MEMORY = 4096 KB

, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS

, MAX_DISPATCH_LATENCY = 5 SECONDS

)

GO

-- 2단계 : 어떤 이유로 리컴파일이 가장 많이 발생하는지 찾기

CREATE EVENT SESSION [XE_Database_Recompile_Histogram_Cause] ON SERVER 

ADD EVENT sqlserver.sql_statement_recompile(

    WHERE (

[package0].[less_than_uint64]([package0].[counter],(2000)) 

AND [package0].[equal_uint64]([sqlserver].[database_id],(54)))) 

ADD TARGET package0.histogram(

SET filtering_event_name=N'sqlserver.sql_statement_recompile'

, source=N'recompile_cause'

, source_type=(0))

WITH (

MAX_MEMORY=4096 KB

, EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS

, MAX_DISPATCH_LATENCY=5 SECONDS

, MAX_EVENT_SIZE=0 KB

, MEMORY_PARTITION_MODE=NONE

, TRACK_CAUSALITY=OFF

, STARTUP_STATE=OFF

)

GO

-- 3단계 : 2단계에서 찾은 원인에 해당하는 쿼리 찾기

CREATE EVENT SESSION [XE_Database_Recompile_Histogram_Cause_Statement] ON SERVER 

ADD EVENT sqlserver.sql_statement_recompile(

    ACTION(sqlserver.sql_text,sqlserver.tsql_stack)

    WHERE (

[package0].[counter]<(2000) AND [sqlserver].[database_id]=(10)

AND [recompile_cause]=(11))) 

ADD TARGET package0.event_file(

SET filename=N'XE_Database_Recompile_Histogram_Cause_Statement'

, max_file_size=(100))

WITH (

MAX_MEMORY=4096 KB

, EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS

, MAX_DISPATCH_LATENCY=5 SECONDS

, MAX_EVENT_SIZE=0 KB

, MEMORY_PARTITION_MODE=NONE

, TRACK_CAUSALITY=OFF

, STARTUP_STATE=OFF

)

GO

-- 시작

ALTER EVENT SESSION [XE_Database_Recompile_Histogram] ON SERVER

STATE = START

GO

ALTER EVENT SESSION [XE_Database_Recompile_Histogram_Cause] ON SERVER

STATE = START

GO

ALTER EVENT SESSION [XE_Database_Recompile_Histogram_Cause_Statement] ON SERVER

STATE = START

GO

-- 종료

ALTER EVENT SESSION [XE_Database_Recompile_Histogram] ON SERVER

STATE = STOP

GO

ALTER EVENT SESSION [XE_Database_Recompile_Histogram_Cause] ON SERVER

STATE = STOP

GO

ALTER EVENT SESSION [XE_Database_Recompile_Histogram_Cause_Statement] ON SERVER

STATE = STOP

GO

-- 삭제

DROP EVENT SESSION [XE_Database_Recompile_Histogram] ON SERVER

GO

DROP EVENT SESSION [XE_Database_Recompile_Histogram_Cause] ON SERVER

GO

DROP EVENT SESSION [XE_Database_Recompile_Histogram_Cause_Statement] ON SERVER

GO

-- 1단계 XQuery를 이용한 분석

SELECT db_name(xnode.xdata.query('.').value('(Slot/value)[1]','varchar(max)')) AS database_name

, xnode.xdata.query('.').value('(Slot/@count)[1]','varchar(max)') as recompile_count

FROM (

SELECT CAST(target_data as xml) as target_data

FROM sys.dm_xe_sessions as s

INNER JOIN sys.dm_xe_session_targets as t

ON s.address = t.event_session_address

WHERE s.name = 'XE_Database_Recompile_Histogram') as a

CROSS APPLY a.target_data.nodes('HistogramTarget/Slot') as xnode (xdata)

GO

-- 2단계 XQuery를 이용한 분석

;WITH cte_xdata_results as (

SELECT xnode.xdata.query('.').value('(Slot/value)[1]','varchar(max)') as recompile_cause

, xnode.xdata.query('.').value('(Slot/@count)[1]','varchar(max)') as recompile_count

FROM (

SELECT CAST(target_data as xml) as target_data

FROM sys.dm_xe_sessions as s

INNER JOIN sys.dm_xe_session_targets as t

ON s.address = t.event_session_address

WHERE s.name = 'XE_Database_Recompile_Histogram_Cause'

) as a

CROSS APPLY a.target_data.nodes('HistogramTarget/Slot') as xnode (xdata)

)

SELECT map_value as statement_recompile_cause

, recompile_count

FROM cte_xdata_results as x

INNER JOIN sys.dm_xe_map_values as mv

ON x.recompile_cause = mv.map_key

WHERE mv.name = 'statement_recompile_cause';

GO

-- 3단계 분석

/* 

tsql_stack 의 handle를 이용해 프로덕션 데이터베이스에서 조회 할 수 있음.(결과가 모두 출력되지 않으면

도구 > 옵션 > 쿼리 결과 > SQL Server > 텍스트로 결과 표시 > 각 열에 표시할 최대 문자 수를 적당하게 변경 후 SSMS 재시작)

*/

SELECT *

FROM sys.dm_exec_sql_text(0x03002E005D05B6215B3BFB00A2A2000001000000000000000000000000000000000000000000000000000000);

;WITH cte_dm_xe_sessions AS (

SELECT xnode.xdata.query('./data').value('(data[@name="recompile_cause"]/text)[1]','varchar(max)') AS recompile_cause

, db_name(xnode.xdata.query('./data').value('(data[@name="source_database_id"]/value)[1]','varchar(max)')) AS [db_name]

, xnode.xdata.query('./data').value('(data[@name="object_type"]/text)[1]','varchar(max)') AS object_type

, xnode.xdata.query('./action').value('(action[@name="sql_text"]/value)[1]','varchar(max)') AS sql_text

, xnode.xdata.query('./action/value/frames') AS tsql_stack_frames

FROM ( SELECT CONVERT(xml,target_data) AS target_data

FROM sys.dm_xe_sessions AS s

INNER JOIN

sys.dm_xe_session_targets AS t

ON s.[address] = t.event_session_address

WHERE s.name = 'XE_Database_Recompile_Histogram_Cause_Statement'

AND target_name = 'ring_buffer'

) AS a

CROSS APPLY

a.target_data.nodes('RingBufferTarget/event') xnode (xdata)

)

SELECT a.db_name

, a.recompile_cause

, a.object_type

, a.sql_text

, [level] AS frame_level

, b.[text]

, SUBSTRING

( b.[text]

, (offsetStart/2)+1

, (( CASE offsetEnd

WHEN -1 THEN DATALENGTH(b.[text])

ELSE offsetEnd

END - offsetStart

)/2)+1

) AS stmt

FROM ( SELECT a.*

, ynode.ydata.query('.').value('(frame/@level)[1]','varchar(max)') AS [level]

, ynode.ydata.query('.').value('(frame/@handle)[1]','varchar(max)') AS handle

, ynode.ydata.query('.').value('(frame/@offsetStart)[1]','varchar(max)') AS offsetStart

, ynode.ydata.query('.').value('(frame/@offsetEnd)[1]','varchar(max)') AS offsetEnd

FROM cte_dm_xe_sessions AS a

CROSS APPLY

a.tsql_stack_frames.nodes('frames/frame') ynode (ydata)

) AS a

OUTER APPLY

sys.dm_exec_sql_text (CONVERT(varbinary(max),handle,1)) b

GO