sp_DeadlockAnalyzer is a stored procedure created with one goal: to help DBAs to debug deadlocks in SQL Server. It has one parameter: @xdl which is the XML content from [deadlock graph event] intercepted with SQL Trace (SQL Profiler). It shows information about isolation level, blocked resources, connections and T-SQL statements with their execution plans. The stored procedure should be executed in the context of the original database (ex. SimpleDeadlock in my example).
Future plans:
- Information about execution plan operators (and corresponding T-SQL statements) which cause every lock (see section [blocked resources]).
- To correlate information about statement’s type (INSERT, UPDATE, DELETE, MERGE, SELECT), table hints (ex. XLOCK), isolation levels, foreign keys.
- To show information about how can be deadlock solved.
- To test using deadlock graph events generated by SQL Trace/SQL Profiler != SQL Server 2012 (I’ve used only this version for tests) and by Extended Events sessions (including here system_health; >= SQL Server 2008).
Output:
Source code:
- USE master;
- GO
- SET ANSI_NULLS ON;
- GO
- SET QUOTED_IDENTIFIER ON;
- GO
- IF NOT EXISTS (
- SELECT *
- FROM sys.procedures p
- JOIN sys.schemas s ON p.schema_id = s.schema_id
- WHERE s.name = ‘dbo’
- AND p.name = ‘sp_DeadlockAnalyzer’
- )
- BEGIN
- EXEC (‘CREATE PROCEDURE dbo.sp_DeadlockAnalyzer AS SELECT 1′);
- END
- GO
- ALTER PROCEDURE dbo.sp_DeadlockAnalyzer
- (
- @xdl XML – Deadlock graph event
- )
- AS
- BEGIN
- DECLARE @Resources TABLE (
- Id INT IDENTITY PRIMARY KEY,
- Resource_LockGranularity SYSNAME NOT NULL,
- Resource_DatabaseId INT NOT NULL,
- Resource_HoBtID BIGINT NOT NULL,
- Resource_ObjectName SYSNAME NULL,
- Resource_IndexName SYSNAME NULL,
- ProcessOwner_Id SYSNAME NOT NULL,
- ProcessOwner_LockMode SYSNAME NOT NULL,
- ProcessWaiter_Id SYSNAME NOT NULL,
- ProcessWaiter_LockMode SYSNAME NOT NULL
- );
- INSERT @Resources (
- Resource_LockGranularity ,
- Resource_DatabaseId ,
- Resource_HoBtID ,
- Resource_ObjectName ,
- Resource_IndexName ,
- ProcessOwner_Id ,
- ProcessOwner_LockMode ,
- ProcessWaiter_Id ,
- ProcessWaiter_LockMode
- )
- SELECT Resource_LockGranularity= x.XmlCol.value(‘local-name(.)’,‘SYSNAME’),
- Resource_Database = x.XmlCol.value(‘@dbid’,‘INT’),
- Resource_HoBtID = x.XmlCol.value(‘@hobtid’,‘BIGINT’),
- Resource_ObjectName = QUOTENAME(PARSENAME(x.XmlCol.value(‘@objectname’,‘SYSNAME’), 3)) + ‘.’ + QUOTENAME(PARSENAME(x.XmlCol.value(‘@objectname’,‘SYSNAME’), 2)) + ‘.’ + QUOTENAME(PARSENAME(x.XmlCol.value(‘@objectname’,‘SYSNAME’), 1)),
- Resource_IndexName = QUOTENAME(x.XmlCol.value(‘@indexname’,‘SYSNAME’)),
- ProcessOwner_Id = own.XmlCol.value(‘@id’, ‘SYSNAME’),
- ProcessOwner_LockMode = own.XmlCol.value(‘@mode’, ‘SYSNAME’),
- ProcessWaiter_Id = wtr.XmlCol.value(‘@id’, ‘SYSNAME’),
- ProcessWaiter_Mode = wtr.XmlCol.value(‘@mode’, ‘SYSNAME’)
- FROM @xdl.nodes(‘deadlock-list/deadlock/resource-list/*’) x(XmlCol)
- OUTER APPLY x.XmlCol.nodes(‘owner-list/owner’) own(XmlCol)
- OUTER APPLY x.XmlCol.nodes(‘waiter-list/waiter’) wtr(XmlCol);
- – Information about databases
- SELECT [Object] = db.name + ‘ (‘ + CONVERT(NVARCHAR(11), db.database_id) + ‘)’,
- [Object_Type] = ‘database’,
- IsolationLevel = ‘*snapshot_isolation_state ‘ + db.snapshot_isolation_state_desc
- FROM sys.databases db
- WHERE db.database_id IN (SELECT r.Resource_DatabaseId FROM @Resources r)
- UNION ALL
- SELECT [Object] = x.XmlCol.value(‘(@id)[1]‘, ‘SYSNAME’),
- [Object_Type] = ‘process’,
- IsolationLevel = x.XmlCol.value(‘(@isolationlevel)[1]‘, ‘SYSNAME’)
- FROM @xdl.nodes(‘deadlock-list/deadlock/process-list/process’) x(XmlCol)
- – Information about resources
- SELECT *
- FROM @Resources
- DECLARE @Processes TABLE (Id SYSNAME PRIMARY KEY);
- INSERT @Processes (Id)
- SELECT DISTINCT p.Id
- FROM @Resources r
- CROSS APPLY (
- SELECT r.ProcessOwner_Id
- UNION ALL
- SELECT r.ProcessWaiter_Id
- ) p (Id);
- – Information about serve processes / conections
- SELECT Process_Id = x.XmlCol.value(‘(@id)[1]‘, ‘SYSNAME’),
- SPID = x.XmlCol.value(‘(@spid)[1]‘, ‘INT’),
- IsDeadlockVictim = x.XmlCol.exist(‘(.)[(@id)[1] = (../../@victim)[1]]‘),
- LogUsed = x.XmlCol.value(‘(@logused)[1]‘, ‘INT’),
- TransactionName = x.XmlCol.value(‘(@transactionname)[1]‘, ‘SYSNAME’),
- IsolationLevel = x.XmlCol.value(‘(@isolationlevel)[1]‘, ‘SYSNAME’),
- DeadlockPriority= x.XmlCol.value(‘(@priority)[1]‘, ‘SMALLINT’),
- CurrentDatabase = DB_NAME(x.XmlCol.value(‘(@currentdb)[1]‘, ‘SMALLINT’)) + N’ (‘ + x.XmlCol.value(‘(@currentdb)[1]‘, ‘NVARCHAR(5)’) + N’)’,
- ClientApp = x.XmlCol.value(‘(@clientapp)[1]‘, ‘SYSNAME’),
- HostName = x.XmlCol.value(‘(@hostname)[1]‘, ‘SYSNAME’),
- LoginName = x.XmlCol.value(‘(@loginname)[1]‘, ‘SYSNAME’),
- InputBuffer = x.XmlCol.query(‘inputbuf’)
- FROM @xdl.nodes(‘deadlock-list/deadlock/process-list/process’) x(XmlCol)
- – Statements & execution plan
- DECLARE @Statements TABLE (
- Process_Id SYSNAME NOT NULL,
- SPID INT NOT NULL,
- [Statement] SYSNAME NOT NULL,
- [SqlHandle] VARBINARY(64)
- );
- INSERT @Statements
- SELECT Process_Id = x.XmlCol.value(‘(@id)[1]‘, ‘SYSNAME’),
- SPID = x.XmlCol.value(‘(@spid)[1]‘, ‘INT’),
- [Statement] = y.XmlCol.value(‘(@procname)[1]‘, ‘SYSNAME’),
- [SqlHandle] = CONVERT(VARBINARY(64), y.XmlCol.value(‘(@sqlhandle)[1]‘, ‘VARCHAR(128)’), 1)
- FROM @xdl.nodes(‘deadlock-list/deadlock/process-list/process’) x(XmlCol)
- CROSS APPLY x.XmlCol.nodes(‘executionStack/frame’) y(XmlCol)
- SELECT stm.Process_Id, stm.SPID,
- Batch = stm.[Statement],
- StatementText = SUBSTRING(src.[text], ISNULL(NULLIF(sts.statement_start_offset, -1) / 2, 1), ISNULL((NULLIF(sts.statement_end_offset, -1) - NULLIF(sts.statement_start_offset, -1)) / 2, 200)),
- DatabasebId = pln.dbid,
- DatabaseName = plnattr.query_db_name,
- ObjectId = pln.objectid,
- ObjectName = plnattr.query_object_name,
- QueryPlan = pln.query_plan,
- SqlHandle = stm.SqlHandle,
- QueryPlanHandle = sts.plan_handle
- FROM @Statements stm
- LEFT JOIN sys.dm_exec_query_stats sts ON stm.SqlHandle = sts.sql_handle
- OUTER APPLY sys.dm_exec_query_plan(sts.plan_handle) pln
- OUTER APPLY (
- SELECT DB_NAME(pvt.dbid) AS query_db_name, OBJECT_NAME(pvt.objectid, pvt.dbid) query_object_name
- FROM (
- SELECT fplanatt.attribute, CONVERT(INT, fplanatt.value) AS id
- FROM sys.dm_exec_plan_attributes(sts.plan_handle) fplanatt – ref: http://msdn.microsoft.com/en-us/library/ms189472.aspx
- WHERE fplanatt.attribute IN (‘dbid’, ‘objectid’)
- ) srcpvt
- PIVOT( MAX(srcpvt.id) FOR srcpvt.attribute IN ([dbid], [objectid]) ) pvt
- ) plnattr
- OUTER APPLY sys.dm_exec_sql_text(stm.SqlHandle) src
- END;
- GO
History:
2014-06-10 Small revision of source code (SUBSTRING: ISNULL, NULLIF)