--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
'연구개발 > DBA' 카테고리의 다른 글
성능 관련 기초 이론 (0) | 2015.11.04 |
---|---|
리소스 잠금 및 리소스 확인 (0) | 2015.09.07 |
tempdb 대량 I/O 발생시키는 쿼리 확인 (0) | 2015.09.01 |
tempdb file read/write i/o 기록 확인 (0) | 2015.08.31 |
tempdb 많이 사용하는 실행계획과 텍스트 반환 (0) | 2015.08.31 |