February 26, 2019 at 7:09 am
I created an extended event to capture the deadlock that occurs in the database . I selected the following --
Events and actions
---------------
lock_deadlock
ock_deadlock_chain
Action
-----------
sql_text
Do not see the sql text captured when I view the event file.
February 26, 2019 at 11:42 am
Guras - Tuesday, February 26, 2019 7:09 AMI created an extended event to capture the deadlock that occurs in the database . I selected the following --
Events and actions
---------------
lock_deadlock
ock_deadlock_chainAction
-----------
sql_textDo not see the sql text captured when I view the event file.
Did you configure the events to include sql_text? It won't be included by default - you would need to add the field from the Global Fields tab when configuring the events.
Sue
February 26, 2019 at 12:35 pm
i hope this helps:
this is the extended events definition i use for a deadlock graph:IF NOT EXISTS(SELECT * FROM [sys].[server_event_sessions] [dxs] WHERE [dxs].[name] = 'Deadlock_Tracking')
BEGIN
CREATE EVENT SESSION [Deadlock_Tracking] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
(ACTION(package0.collect_system_time,
sqlos.task_time,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_id
,sqlserver.database_name,
sqlserver.nt_username,
sqlserver.plan_handle,
sqlserver.session_nt_username,
sqlserver.sql_text,
sqlserver.username)
)
ADD TARGET package0.event_file(SET filename=N'Deadlock_Tracking.xel',max_file_size=(5),max_rollover_files=(4))
--ADD TARGET package0.ring_buffer(SET max_events_limit=(1000),max_memory=(4096))
WITH (MAX_MEMORY=1024 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
END
--ALTER EVENT SESSION [Deadlock_Tracking] ON SERVER STATE = START
--ALTER EVENT SESSION [Deadlock_Tracking] ON SERVER STATE = STOP
Lowell
February 26, 2019 at 2:19 pm
Sue_H - Tuesday, February 26, 2019 11:42 AMGuras - Tuesday, February 26, 2019 7:09 AMI created an extended event to capture the deadlock that occurs in the database . I selected the following --
Events and actions
---------------
lock_deadlock
ock_deadlock_chainAction
-----------
sql_textDo not see the sql text captured when I view the event file.
Did you configure the events to include sql_text? It won't be included by default - you would need to add the field from the Global Fields tab when configuring the events.
Sue
I did . I have the check box checked next to the sql_text. I double check it.
February 27, 2019 at 3:01 am
The system_health session captures deadlocks and you can get the query itself from the deadlock graph. However, if you want to get specific on deadlock monitoring, I'd do it more like this:
CREATE EVENT SESSION DeadlockMonitoring
ON SERVER
ADD EVENT sqlserver.rpc_completed
(ACTION (sqlserver.database_name)
WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.rpc_starting
(ACTION (sqlserver.database_name)
WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.sql_batch_completed
(ACTION (sqlserver.database_name)
WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.sql_batch_starting
(ACTION (sqlserver.database_name)
WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.xml_deadlock_report
(ACTION (sqlserver.database_name)
WHERE (sqlserver.database_name = N'AdventureWorks2017'))
ADD TARGET package0.event_file
(SET filename = N'DeadlockMonitoring')
WITH (MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = ON,
STARTUP_STATE = OFF);
GO
The causality tracking lets you group the data together in a way that you can see the batch or rpc call along with the deadlock graph. Obviously, modify the location of the output file and the database filter (or any other filter) as needed.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply