query_post_execution_showplan
Occurs after a SQL statement is executed. This event returns an XML representation of the actual query plan. Using this
event can have a significant performance overhead so it should only be used when troubleshooting or monitoring specific
problems for brief periods of time.
Solution:
Create Extended Events Session:
CREATE EVENT SESSION [ExecPlanDetails] ON DATABASE
ADD EVENT sqlserver.query_post_execution_showplan(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,
sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.rpc_completed (
ACTION (sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,
sqlserver.sql_text,sqlserver.username)
WHERE ([object_name] not like '%sp_reset_connection%')
)
ADD TARGET package0.ring_buffer(SET max_memory=(256000))
WITH (STARTUP_STATE=OFF)
GO
Start Extended Events Session:
ALTER EVENT SESSION [ExecPlanDetails] ON DATABASE STATE = START;
Select Extended Events data
/*SELECT DATA*/
DECLARE @target_data XML;
SELECT @target_data = CAST(st.target_data AS XML)
FROM
sys.dm_xe_database_sessions AS se
INNER JOIN sys.dm_xe_database_session_targets AS st ON se.address like st.event_session_address
where se.name = 'ExecPlanDetails'
SELECT CAST(st.target_data AS XML)
FROM
sys.dm_xe_database_sessions AS se
INNER JOIN sys.dm_xe_database_session_targets AS st ON se.address like st.event_session_address
where se.name = 'ExecPlanDetails'
/*
SELECT se.*
FROM
sys.dm_xe_database_sessions AS se
INNER JOIN sys.dm_xe_database_session_targets AS st ON se.address like st.event_session_address
where se.name = 'ExecPlanDetails'
*/
--SELECT @target_data.value('(RingBufferTarget/@totalEventsProcessed)[1]', 'int') AS TotalEventsProcessed
SELECT top 100
n.value('(@name)[1]', 'varchar(50)') AS event_name,
n.value('(@package)[1]', 'varchar(50)') AS package_name,
DATEADD(hh,-5,
n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
n.value('(data[@name="duration"]/value)[1]', 'int') /1000 as 'duration (ms)',
n.value('(data[@name="logical_reads"]/value)[1]', 'varchar(15)') as logical_reads,
n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
n.value('(data[@name="writes"]/value)[1]', 'varchar(max)') as writes,
n.value('(data[@name="cpu_time"]/value)[1]', 'int') /1000 as 'cputime (ms)',
n.value('(data[@name="row_count"]/value)[1]', 'varchar(max)') as row_count,
--n.value('(data[@name="batch_text"]/value)[1]', 'varchar(max)') as batch_text,
--n.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') as SQL_Text,
n.value('(data[@name="statement"]/value)[1]', 'varchar(max)') as statement1,
n.value('(data[@name="object_name"]/value)[1]', 'varchar(max)') as object_name1,
n.value('(action[@name="client_app_name"]/value)[1]', 'varchar(max)') as client_app_name
FROM @target_data.nodes('RingBufferTarget/event') AS q(n)
--WHERE
--not n.value('(data[@name="object_name"]/value)[1]', 'varchar(max)') like '%sp_reset_connection%'
ORDER BY n.value('(@timestamp)[1]', 'datetime2') desc
Clean up Extended Events Session:
ALTER EVENT SESSION ExecPlanDetails ON DATABASE STATE = STOP
ALTER EVENT SESSION ExecPlanDetails ON DATABASE DROP TARGET package0.ring_buffer
DROP EVENT SESSION ExecPlanDetails ON DATABASE
|
|
|
|
|
|