Deadlocks : Count , Analysis and Event Notification

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 :

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 Name from Associated ObjectID:

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 = 1611152785;

DBCC Page Results to Object Name:
Paul Randall Blog Link :
http://www.sqlskills.com/blogs/paul/finding-table-name-page-id/
Decipher Waitresource:

Microsoft Link : http://support.microsoft.com/en-us/kb/224453

http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx

This field indicates the resource that a SPID is waiting on. The following table lists common waitresource formats and their meaning:

Resource	Format	Example
Table	DatabaseID:ObjectID:IndexID	TAB: 5:261575970:1 In this case, database ID 5 is the pubs sample database and object ID 261575970 is the titles table and 1 is the clustered index.
Page	DatabaseID:FileID:PageID	PAGE: 5:1:104 In this case, database ID 5 is pubs, file ID 1 is the primary data file, and page 104 is a page belonging to the titles table.To identify the object id that the page belongs to, use the DBCC PAGE (dbid, fileid, pageid, output_option) command, and look at the m_objId. For example:DBCC TRACEON ( 3604 ) DBCC PAGE ( 5 , 1 , 104 , 3 )
Key	DatabaseID:Hobt_id (Hash value for index key)	KEY: 5:72057594044284928 (3300a4f361aa)In this case, database ID 5 is Pubs, Hobt_ID 72057594044284928 corresponds to non clustered index_id 2 for object id 261575970 (titles table). Use the sys.partitions catalog view to associate the hobt_id to a particular index id and object id. There is no way to unhash the index key hash to a specific index key value.
Row	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3In this case, database ID 5 is pubs , file ID 1 is the primary data file, page 104 is a page belonging to the titles table, and slot 3 indicates the row’s position on the page.
Compile	DatabaseID:ObjectID [[COMPILE]]	TAB: 5:834102012 [[COMPILE]] This is not a table lock, but rather a compile lock on a stored procedure. Database ID 5 is pubs, object ID 834102012 is stored procedure usp_myprocedure. See Knowledge Base Article 263889 for more information on blocking caused by compile locks.

DeadLock Event Notification

Extended events provide light weight alternative for using profiler. The following example shows how to create queue , service and get notified whenever deadlock occurs.

You get the email notification with the deadlock details attached. Its nice way of knowing the deadlocks in production without pouring over ton of trace logs.

USE msdb

GO
-- Create a queue to receive messages.
CREATE QUEUE queDeadLock_Graph;
GO

-- Create a service on the queue that references
-- the event notifications contract.
CREATE SERVICE svcDeadLock_Graph
ON QUEUE queDeadLock_Graph
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

-- Now query the sys.databases for the service_broker_guid of the msdb database.
SELECT service_broker_guid FROM sys.databases WHERE name = 'msdb';

CREATE EVENT NOTIFICATION evnDeadLock_Graph
ON SERVER
WITH FAN_IN
FOR DEADLOCK_GRAPH
TO SERVICE 'svcDeadLock_Graph'
, '36642DC7-A701-4691-8D6A-0E82FE313867'; -- the GUID for msdb goes here

USE msdb
GO
CREATE TABLE dbo.tblDeadLock_Log (
DeadLock_ID int IDENTITY(1,1) CONSTRAINT pk_tblDeadLock_Log PRIMARY KEY
, DeadLock_Detected datetime
, DeadLock_Graph nvarchar(max)
, NoMailReason nvarchar(2048))
GO

USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procReceiveDeadLock_Graph]
AS
DECLARE @conversation_handle uniqueidentifier
DECLARE @message_body xml
DECLARE @message_type_name nvarchar(128)
DECLARE @deadlock_graph xml
DECLARE @event_datetime datetime
DECLARE @deadlock_id int
DECLARE @DBname sysname
BEGIN TRY
BEGIN TRAN
WAITFOR(
RECEIVE TOP(1) @conversation_handle = conversation_handle
, @message_body = CONVERT(xml ,message_body)
, @message_type_name = message_type_name
FROM msdb.dbo.queDeadLock_Graph)
, TIMEOUT 10000
-- http://resquel.com/ssb/2010/07/24/ServiceBrokerCanMakeYourTransactionLogBig.aspx
-- Validate message
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification' AND
@message_body.exist('(/EVENT_INSTANCE/TextData/deadlock-list)') = 1)
BEGIN
-- Extract the info from the message
SELECT @deadlock_graph = @message_body.query('(/EVENT_INSTANCE/TextData/deadlock-list)')
, @event_datetime = @message_body.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
, @DBname = DB_NAME(@message_body.value('(//*/process/@currentdb)[1]', 'varchar(10)'))
-- Put the info in the table
INSERT msdb.dbo.tblDeadLock_Log (DeadLock_Detected, DeadLock_Graph)
VALUES (@event_datetime, convert(nvarchar(max),@deadlock_graph) )
SELECT @deadlock_id = SCOPE_IDENTITY()
-- Send deadlock alert mail.
-- Requires configured database mail, will log an error if not (or anything else goes wrong).
BEGIN TRY
DECLARE @subj nvarchar(255), @bdy nvarchar(max), @qry nvarchar(max), @attfn nvarchar(255)
SELECT @subj = 'A deadlock occurred on ' + @@SERVERNAME + ', on the ' + QUOTENAME(COALESCE(@DBname, 'unknown')) + ' database'
, @bdy = 'A deadlock occurred at ' + CONVERT(varchar(50),@event_datetime, 120) + ' on SQL Server: ' + @@SERVERNAME + '. See attached xdl-file for deadlock details.'
, @qry = 'SET NOCOUNT ON; SELECT deadlock_graph FROM msdb.dbo.tblDeadLock_Log WITH (READUNCOMMITTED) WHERE DeadLock_ID = ' + CAST(@deadlock_id AS varchar(10)) -- Locking hint is to prevent this dynamic query to be blocked by the lock held by the insert. The dynamic SQL will not come from inside this transaction.
, @attfn = @@SERVERNAME + '_' + CAST(@deadlock_id AS varchar(10)) + '.xdl'
EXEC sp_send_dbmail @profile_name = 'Raju'
, @recipients = '[email protected]'
, @subject = @subj
, @body = @bdy
, @query = @qry
, @attach_query_result_as_file = 1
, @query_attachment_filename = @attfn -- http://support.microsoft.com/kb/924345
, @query_result_header = 0
, @query_result_width = 32767
, @query_no_truncate = 1
END TRY
BEGIN CATCH
UPDATE msdb.dbo.tblDeadLock_Log
SET NoMailReason = ERROR_MESSAGE()
WHERE DeadLock_ID = @deadlock_id
END CATCH
END
ELSE -- Not an event notification with deadlock-list
END CONVERSATION @conversation_handle
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH

ALTER QUEUE dbo.queDeadLock_Graph
WITH
STATUS = ON,
ACTIVATION (
PROCEDURE_NAME = msdb.dbo.procReceiveDeadLock_Graph,
STATUS = ON,
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER)
GO

select * from [dbo].[tblDeadLock_Log

create database DeadLock

use DeadLock
-- Sample Table, which will track individual Orders entered by

-- Various Sales Persons

create table Orders
( OrderID int,
OrderDate datetime,
Amount money,
SalesPersonID int )

go

-- Sample Table, which will track all individual Sales Persons details

create table SalesPerson
(SalesPersonID int,
Name varchar(100),
Region varchar(100) );

-- Insert Date in SalesPerson

insert SalesPerson Values (1,'Dave Lawlor','California');
insert SalesPerson Values (2,'Mark F','Atlanta');
insert SalesPerson Values (3,'Vivek Pundit','India');

-- Insert Date in Orders Table

insert Orders values (101,'2011-01-01', 343,1)
insert Orders values (102,'2011-02-01', 35,2)

-- TRANSACTION 1

BEGIN TRANSACTION
UPDATE Orders
SET Amount='100'
WAITFOR DELAY'00:00:10'
UPDATE SalesPerson
SET Region='Bellevue'
ROLLBACK TRANSACTION

-- TRANSACTION 2

BEGIN TRANSACTION
UPDATE SalesPerson
SET Region='Bellevue'
WAITFOR DELAY '00:00:10'
UPDATE Orders
SET Amount='100'
ROLLBACK TRANSACTION

/*

Error Message
Msg 1205, Level 13, State 45, Line 8
Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
*/

Deadlock Process Node

In a wait-for graph, the process node contains information about the process. The following table explains the components of a process.

Component DefinitionServer process IdServer process identifier (SPID), a server assigned identifier for the process owning the lock.Server batch IdServer batch identifier (SBID).
Execution context IdExecution context identifier (ECID). The execution context ID of a given thread associated with a specific SPID.ECID = {0,1,2,3, …n}, where 0 always represents the main or parent thread, and {1,2,3, …n} represent the subthreads.

Deadlock priorityDeadlock priority for the process. For more information about possible values, see SET DEADLOCK_PRIORITY (Transact-SQL).
Log UsedAmount of log space used by the process.
Owner Id Transaction ID for the processes which are using transactions and currently waiting on a lock.

Transaction descriptorPointer to the transaction descriptor that describes the state of the transaction.Input bufferInput buffer of the current process, defines the type of event and the statement being executed.

Possible values include:
Language
RPC
None

StatementType of statement. Possible values are:

NOP
SELECT
UPDATE
INSERT
DELETE
Unknown
SQL Handle to SQL Text