configure extended event for deadlock

Question

configure extended event for deadlock

vsamantha35

SSChampion

Points: 11551
More actions
August 27, 2019 at 8:56 am

#3674632

Hi All,
I want to capture deadlock information and associated batch and tsql stmts .
I was able to reproduce the deadlock error but not sure why xml deadlock graph was not getting captured in the .xel file.
Am I doing anything wrong here? Is track_causality option = ON is doing something wrong? Please suggest.
CREATE EVENT SESSION [xe_deadlocks] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([sqlserver].[database_name]=N'testdb' AND [sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([sqlserver].[database_name]=N'testdb' AND [sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.sp_statement_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([sqlserver].[database_name]=N'testdb' AND [sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
WHERE ([sqlserver].[database_name]=N'testdb' AND [sqlserver].[is_system]=(0))),
ADD EVENT sqlserver.xml_deadlock_report(
WHERE ([sqlserver].[database_name]=N'testdb' AND [sqlserver].[is_system]=(0)))
ADD TARGET package0.event_file(SET filename=N'C:\xevents\xe_deadlocks.xel',max_file_size=(1024),max_rollover_files=(4))
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=ON
,STARTUP_STATE=OFF
)
GO
Thanks,
Sam

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

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

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

Causality tracking won't prevent it from capturing the deadlock graph. I think the issue likely lies in the is_system filter. You're not looking for system issues, you're looking for user issues. Get rid of that filter.

"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

vsamantha35 SSChampion Points: 11551 More actions · Answer 2

Hi Sir,

Removed the system filter. However, still it is not capturing deadlock graph. Don't know why.

CREATE EVENT SESSION [xe_deadlocks] ON SERVER

ADD EVENT sqlserver.error_reported(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb')),

ADD EVENT sqlserver.rpc_completed(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb')),

ADD EVENT sqlserver.sp_statement_completed(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb')),

ADD EVENT sqlserver.sql_batch_completed(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb')),

ADD EVENT sqlserver.xml_deadlock_report(

WHERE ([sqlserver].[database_name]=N'testdb'))

ADD TARGET package0.event_file

(

SET filename=N'C:\xevents\xe_deadlocks.xel',

max_file_size=(1024),

max_rollover_files=(4)

)

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=ON,

STARTUP_STATE=OFF)

GO

deadlock err

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

Look down below in the details. Is there a second tab? I'll bet there is.

"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

vsamantha35 SSChampion Points: 11551 More actions · Answer 4

vsamantha35

SSChampion

Points: 11551

August 27, 2019 at 7:03 pm

#3674828

No Sir, I don't see any tab.

dead

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

I hae had this same issue a few years ago and solved it. Only problem I have is remembering the issue I had and how I solved it. I will look back through my stuff as I have time, but I would be interested if you find a resolution if you find one if I can't find anything myself.

vsamantha35 SSChampion Points: 11551 More actions · Answer 6

I am testing this XE on Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 16299: )

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 7

It shouldn't matter, but go and get the latest cumulative update. You shouldn't be running RTM at this point.

I'm unsure what the issue is. I do this kind of monitoring regularly in 2017. It works.

"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

vsamantha35 SSChampion Points: 11551 More actions · Answer 8

Is there any issue with the ACTION selection w.r.t. xml_deadlock_report ? I removed and tested but didn't make much difference.

Applied the latest CU for sql server 2017. still the issue. One observation, I tried to choose columns , I dont know why xml_deadlock_report is not listed. Any thoughts ?

dead3

Grant, could you please share sql 2017 DDL script of the extended event. so that I can try from my end.

This reply was modified 6 years ago by vsamantha35.

vsamantha35 SSChampion Points: 11551 More actions · Answer 9

Hi All,

Removed the filter clause in the extended event and was able to see the deadlock graph xevent trace. Looks like deadlocks happening in master. It worked for me after removing the filters. May be I will try configure 2 separate xtended events 1 for deadlock graph capture and other for collecting tsql stmts with filter of database name.

Thanks Grant for continuous support.

vsamantha35 SSChampion Points: 11551 More actions · Answer 10

This is code which is working for me.

CREATE EVENT SESSION [XE_testdb_DEADLOCKS] ON SERVER

ADD EVENT sqlserver.error_reported(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb' OR [sqlserver].[database_name]=N'MASTER')),

ADD EVENT sqlserver.rpc_completed(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb' OR [sqlserver].[database_name]=N'MASTER')),

ADD EVENT sqlserver.sp_statement_completed(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb' OR [sqlserver].[database_name]=N'MASTER')),

ADD EVENT sqlserver.sql_batch_completed(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb' OR [sqlserver].[database_name]=N'MASTER')),

ADD EVENT sqlserver.sql_batch_starting(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb' OR [sqlserver].[database_name]=N'MASTER')),

ADD EVENT sqlserver.xml_deadlock_report(

ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)

WHERE ([sqlserver].[database_name]=N'testdb' OR [sqlserver].[database_name]=N'MASTER'))

ADD TARGET package0.event_file(SET filename=N'C:\xevents\XE_testdb_DEADLOCKS.xel',max_file_size=(1024))

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=ON,-------------------on

STARTUP_STATE=OFF)

GO