Extended Events - Excecution Plan

Question

Extended Events - Excecution Plan

Darosh

SSC Rookie

Points: 47
More actions
January 23, 2019 at 2:33 am

#409456

Hi all,
I'm playing bit with execution plans in Extended Events. I found bit strange thing for me. I didn't capture query_post_execution_showplan for one query. See picture:
Please can someone explain me why ?
Regards
Martin

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 1

Without at least the definition of what events you're capturing, filters, actions, it's hard to say. Post the T-SQL.

If you are trying to capture correlated information like this, turn on Causality Tracking. It allows you to group sets of events together and maintain the order in which they are called.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Darosh SSC Rookie Points: 47 More actions · Answer 2

Here is Extended event script. It is only filtered to one database.

CREATE EVENT SESSION [Performance monitor 2] ON SERVER
ADD EVENT sqlserver.query_post_compilation_showplan(
    ACTION(sqlserver.sql_text)
    WHERE ([source_database_id]=(6))),
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.request_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[equal_uint64]([sqlserver].[database_id],(6)))),
ADD EVENT sqlserver.query_pre_execution_showplan(
    ACTION(sqlserver.sql_text)
    WHERE ([source_database_id]=(6))),
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.request_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[equal_uint64]([sqlserver].[database_id],(6)))),
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([sqlserver].[database_id],(6)))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.request_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[equal_uint64]([sqlserver].[database_id],(6)))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.request_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[equal_uint64]([sqlserver].[database_id],(6))))
ADD TARGET package0.event_file(SET filename=N'Performance monitor2')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 3

Nothing in the events suggest why. What's that query doing?
I took your script and tested it on my own server. Behavior is what I would expect. So, we have to look at the exception and it's that query.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning