Deadlocks : Count , Analysis and Event Notification

SQL Server : Number of Deadlocks since Last SQL Server Restart

Extended Events Query:

DEADLOCK Query from Extended Events:

Decipher waitresource= “KEY: 27:72057596131278848 (1413e0809b53)”

DBCC Usuage:

SQL Handle to SQL Text :

Object Name from Associated ObjectID:

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.

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