SQL Server : Number of Deadlocks since Last SQL Server Restart
|
SELECT sqlserver_start_time AS ServerRestartTime FROM sys.dm_os_sys_info SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total' -- Trace Flags to Capture Deadlock Events DBCC TRACESTATUS(1222) DBCC TRACEON(1222,-1) DBCC TRACEOFF(1222,-1) |
Extended Events Query:
|
WITH SystemHealth AS ( SELECT CAST(target_data as xml) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE name = 'system_health' AND st.target_name = 'ring_buffer') SELECT XEventData.XEvent.query('(data/value/deadlock)[1]') AS DeadLockGraph FROM SystemHealth CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent) WHERE XEventData.XEvent.value('@name','varchar(4000)') LIKE '%deadlock%' |
|
select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph, XEventData.XEvent.value('(@timestamp)[1]', 'datetime2') AS TS1 FROM (select CAST(target_data as xml) as TargetData from sys.dm_xe_session_targets st join sys.dm_xe_sessions s on s.address = st.event_session_address where name = 'system_health') AS Data CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent) where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report' |
DEADLOCK Query from Extended Events:
|
SELECT TOP 10 XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS DeadlockGraph , XEventData.XEvent.value('(@timestamp)[1]', 'datetime2') AS TS1 FROM ( SELECT CAST(target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st WITH ( NOLOCK ) JOIN sys.dm_xe_sessions s WITH ( NOLOCK ) ON s.address = st.event_session_address WHERE name = 'system_health' ) AS Data CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData ( XEvent ) WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report' AND XEventData.XEvent.value('(@timestamp)[1]', 'datetime2') > GETUTCDATE()-2 ORDER BY XEventData.XEvent.value('(@timestamp)[1]', 'datetime2') DESC |
Decipher waitresource= “KEY: 27:72057596131278848 (1413e0809b53)”
|
SELECT DB_NAME(27) AS DatabaseName SELECT OBJECT_NAME(p.object_id) AS TableName , i.name AS IndexName FROM sys.partitions AS p INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id WHERE partition_id = 72057596131278848; -- IF nothing is Changed the key actually exists SELECT * FROM dbo.table1 WHERE %%LOCKRES%% = '{1413e0809b53}' |
DBCC Usuage:
|
DBCC PAGE ( 30 , 1 , 310497 , 2 ) WITH TABLERESULTS DBCC TRACEON ( 3604 ) DBCC PAGE ( 8 , 1 , 591132 , 1 ) DBCC TRACEOFF ( 3604 ) -- PAGE: 8:1:591132 SELECT OBJECT_NAME(1371151930) SELECT OBJECT_NAME(1371151930) -- Proc [Database Id = 8 Object Id = 50099219] -- Proc [Database Id = 8 Object Id = 2021582240] SELECT OBJECT_NAME(50099219) SELECT OBJECT_NAME(2021582240) |
SQL Handle to SQL Text :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
|
DECLARE @pStartOffSet INT DECLARE @pEndOffSet INT DECLARE @pSQLHandle VARBINARY(64) SELECT @pSQLHandle = 0x03003800150027147ddafc00dea500000100000000000000 ,@pStartOffSet = 36772,@pEndOffSet = 37296 SELECT SUBSTRING(st.text, (@pStartOffSet/2)+1, ((CASE @pEndOffSet WHEN -1 THEN DATALENGTH(st.text) ELSE @pEndOffSet END - @pStartOffSet)/2) + 1) AS statement_text FROM sys.dm_exec_sql_text(@pSQLHandle) AS st SELECT @pSQLHandle = 0x03003800dcdb3213e1d3fc00dea500000100000000000000 ,@pStartOffSet = 4646,@pEndOffSet = 4896 |
Object…