Friday, August 14, 2015

Use Extended Events to capture Deadlocked Queries


 -- Create a new event session (it is better to create a new session and not modify the system’s built-in session “system_health”):
USE MASTER
GO
CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.asynchronous_file_target
(SET filename= N'C:\temp\deadlock.xel' )
WITH
(MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=10 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)


-- Enable the session to start capturing events:
USE MASTER
GO
ALTER EVENT SESSION [Deadlock_Monitor] ON SERVER STATE = start;


-- To see how many deadlocks have been captured by the session since it started running, you can run this query:
select COUNT(*) from sys.fn_xe_file_target_read_file ('c:\temp\deadlock*.xel', 'c:\temp\deadlock*.xem', null, null)


-- To get a list of the captured deadlocks and their graphs you can execute this query:
select xml_data.value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') Execution_Time,
xml_data.value('(event/data/value)[1]','varchar(max)') Query
from (select object_name as event, CONVERT(xml, event_data) as xml_data
from sys.fn_xe_file_target_read_file
('c:\temp\deadlock*.xel', 'c:\temp\deadlock*.xem', null, null)) v order by Execution_Time


-- If you want the session to stop capturing events (until you enable the session again), you can use this query:
ALTER EVENT SESSION [Deadlock_Monitor] ON SERVER STATE = stop;


-- If you want to completely remove (delete) the session from the server, you can use this query:
DROP EVENT SESSION [Deadlock_Monitor] ON SERVER


Source:
http://blogs.msdn.com/b/john_daskalakis/archive/2013/12/02/how-to-use-extended-events-to-proactively-monitor-your-sql-server-for-deadlock-issues.aspx

No comments:

Post a Comment

https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context...