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
IF OBJECT_ID(N'tempdb.dbo.#Resources') IS NOT NULL
DROP TABLE #Resources;
IF OBJECT_ID(N'tempdb.dbo.#Processes') IS NOT NULL
DROP TABLE #Processes;
– Information about resources
CREATE TABLE #Resources (
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);
CREATE TABLE #Processes (
SPID INT NOT NULL,
IsVictim BIT NOT NULL,
Database_Name SYSNAME NOT NULL,
InputBuffer XML NULL,
TransactionName SYSNAME NULL,
IsolationLevel SYSNAME NULL,
Snapshot_Isolation_State SYSNAME NULL,
DeadlockPriority SMALLINT NULL,
LogUsed INT NULL,
ClientApp SYSNAME NULL,
HostName SYSNAME NULL,
LoginName SYSNAME NULL,
Database_Id INT NOT NULL,
CallStack XML NULL,
Process_Id SYSNAME PRIMARY KEY
);
INSERT #Processes
SELECT y.SPID, y.IsVictim,
QUOTENAME(DB_NAME(y.Database_Id)) AS Database_Name, y.InputBuffer,
y.TransactionName, y.IsolationLevel, db.snapshot_isolation_state_desc, y.DeadlockPriority, y.LogUsed,
y.ClientApp, y.HostName, y.LoginName, y.Database_Id,
y.CallStack,
y.Process_Id
FROM (
SELECT Process_Id = x.XmlCol.value('(@id)[1]', 'SYSNAME'),
SPID = x.XmlCol.value('(@spid)[1]', 'INT'),
IsVictim = x.XmlCol.exist('(.)[(@id)[1] = (../../@victim)[1]]'),
– CurrentDatabase= DB_NAME(x.XmlCol.value('(@currentdb)[1]', 'SMALLINT')) + N' (' + x.XmlCol.value('(@currentdb)[1]', 'NVARCHAR(5)') + N')',
Database_Id = x.XmlCol.value('(@currentdb)[1]', 'SMALLINT'),
InputBuffer = x.XmlCol.query('inputbuf'),
TransactionName = x.XmlCol.value('(@transactionname)[1]', 'SYSNAME'),
IsolationLevel = x.XmlCol.value('(@isolationlevel)[1]', 'SYSNAME'),
DeadlockPriority = x.XmlCol.value('(@priority)[1]', 'SMALLINT'),
LogUsed = x.XmlCol.value('(@logused)[1]', 'INT'),
ClientApp = x.XmlCol.value('(@clientapp)[1]', 'SYSNAME'),
HostName = x.XmlCol.value('(@hostname)[1]', 'SYSNAME'),
LoginName = x.XmlCol.value('(@loginname)[1]', 'SYSNAME'),
CallStack = x.XmlCol.query('./executionStack')
FROM @xdl.nodes('deadlock-list/deadlock/process-list/process') x(XmlCol)
) y INNER JOIN sys.databases db ON y.Database_Id = db.database_id;
DECLARE @DistinctProcesses TABLE (SPID_Description VARCHAR(16) PRIMARY KEY);
INSERT @DistinctProcesses (SPID_Description)
SELECT DISTINCT QUOTENAME('SPID ' + CONVERT(VARCHAR(11), p.SPID))
FROM #Processes p;
DECLARE @DistinctProcessesList NVARCHAR(4000);
SET @DistinctProcessesList = '';
SELECT @DistinctProcessesList = @DistinctProcessesList + ', ' + dp.SPID_Description
FROM @DistinctProcesses dp;
SET @DistinctProcessesList = STUFF(@DistinctProcessesList, 1, 2, '');
DECLARE @SqlStatement NVARCHAR(MAX);
SET @SqlStatement = N'
SELECT t.Resource_ObjectName, t.Resource_IndexName, t.Resource_LockGranularity,
' + @DistinctProcessesList + ',
t.Resource_DatabaseId, t.Resource_HoBtID
FROM (
SELECT x.Resource_LockGranularity, x.Resource_ObjectName, x.Resource_IndexName,
''SPID '' + CONVERT(VARCHAR(11), y.SPID) AS SPID_Description, y.LockInfo,
x.Resource_DatabaseId, x.Resource_HoBtID
FROM (
SELECT r.Resource_LockGranularity, r.Resource_ObjectName, r.Resource_IndexName,
r.ProcessOwner_Id, ''acqr '' + r.ProcessOwner_LockMode AS OwnerLockInfo,
r.ProcessWaiter_Id, ''wait '' + r.ProcessWaiter_LockMode AS WaitwerLockInfo,
r.Resource_DatabaseId, r.Resource_HoBtID
FROM #Resources r
) x
CROSS APPLY (
SELECT p.SPID, x.OwnerLockInfo
FROM #Processes p WHERE p.Process_Id = x.ProcessOwner_Id
UNION ALL
SELECT p.SPID, x.WaitwerLockInfo
FROM #Processes p WHERE p.Process_Id = x.ProcessWaiter_Id
) y(SPID, LockInfo)
) z
PIVOT( MAX(z.LockInfo) FOR z.SPID_Description IN (' + @DistinctProcessesList + ') ) t';
EXEC (@SqlStatement);
– Information about server processes / conections including SQL batches
SELECT * FROM #Processes ORDER BY IsVictim DESC;
– Statements and execution plans
DECLARE @Statements TABLE (
Process_Id SYSNAME NOT NULL,
SPID INT NOT NULL,
IsVictim BIT NOT NULL,
[Statement] SYSNAME NOT NULL,
[SqlHandle] VARBINARY(64) NULL,
[Text] NVARCHAR(4000) NULL,
Line INT NULL,
StmtStartOffset INT NULL,
StmtEndOffset INT NULL,
StatementNum INT NOT NULL,
PlanHandle VARBINARY(64) NULL,
QueryPlan XML NULL
);
INSERT @Statements
SELECT y.*, qs.plan_handle AS PlanHandle, pln.query_plan AS QueryPlan
FROM (
SELECT Process_Id = x.Process_Id,
SPID = x.SPID,
IsVictim= x.IsVictim,
[Statement] = y.XmlCol.value('(@procname)[1]', 'SYSNAME'),
[SqlHandle] = CONVERT(VARBINARY(64), y.XmlCol.value('(@sqlhandle)[1]', 'VARCHAR(128)'), 1),
[Text] = y.XmlCol.value('(text())[1]', 'NVARCHAR(4000)'),
Line = y.XmlCol.value('(@line)[1]', 'INT'),
StmtStartOffset = ISNULL(y.XmlCol.value('(@stmtstart)[1]', 'INT'), 0),
StmtEndOffset = ISNULL(y.XmlCol.value('(@stmtend)[1]', 'INT'), -1),
StatementNum = ROW_NUMBER() OVER(ORDER BY y.XmlCol DESC)
FROM #Processes x
OUTER APPLY x.CallStack.nodes('executionStack/frame') y(XmlCol)
) y
LEFT JOIN sys.dm_exec_query_stats qs ON y.SqlHandle = qs.sql_handle AND y.StmtStartOffset = qs.statement_start_offset AND y.StmtEndOffset = qs.statement_end_offset
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) pln
SELECT s.SPID, s.IsVictim,
s.[Statement], s.[Text], s.Line,
s.QueryPlan, s.PlanHandle, s.SqlHandle,
s.Process_Id
FROM @Statements s
ORDER BY s.IsVictim DESC, s.StatementNum;
– Data access operators
WITH XMLNAMESPACES (
'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
'http://www.w3.org/2001/XMLSchema' AS xsd,
DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
),
QueryPlans AS (
SELECT x.Process_Id, x.SPID, x.IsVictim, x.QueryPlan
FROM (
SELECT s.Process_Id, s.SPID, s.IsVictim, s.QueryPlan, ROW_NUMBER() OVER(PARTITION BY BINARY_CHECKSUM(CONVERT(NVARCHAR(MAX),s.QueryPlan)) ORDER BY @@SPID) AS RowNum
FROM @Statements s
WHERE s.QueryPlan IS NOT NULL
) x
WHERE x.RowNum = 1
)
SELECT b.SPID, b.IsVictim,
b.StatementType, b.[Statement],
b.LogicalOp, b.PhysicalOp,
b.ObjectName, b.IndexName, b.IndexKind,
b.Warnings, b.MissingIndexes,
b.Process_Id,
b.BatchId, b.StatementId, b.NodeId
FROM (
SELECT a.Process_Id, a.SPID, a.IsVictim,
a.BatchId, a.StatementId, a.NodeId,
a.StatementType, a.[Statement], /*a.ParamStatement,*/
LogicalOp = CASE
WHEN a.TableReferenceId = -1 AND a.IndexKind = N'Clustered' AND a.LogicalOp = 'Clustered Index Seek' THEN 'Key Lookup'
ELSE a.LogicalOp
END,
PhysicalOp = CASE
WHEN a.IndexKind = N'NonClustered' AND a.PhysicalOp IN('Clustered Index Insert', 'Table Insert') THEN 'Index Insert'
WHEN a.IndexKind = N'NonClustered' AND a.PhysicalOp IN('Clustered Index Update', 'Table Update') THEN 'Index Update'
WHEN a.IndexKind = N'NonClustered' AND a.PhysicalOp IN('Clustered Index Delete', 'Table Delete') THEN 'Index Delete'
WHEN a.IndexKind = N'NonClustered' AND a.PhysicalOp IN('Clustered Index Merge', 'Table Merge') THEN 'Index Merge'
ELSE a.PhysicalOp
END,
a.ObjectName, a.IndexName, a.IndexKind,
a.Warnings, a.MissingIndexes
FROM (
SELECT – batch_XmlCol = batch.XmlCol.query('.'),
BatchId = DENSE_RANK() OVER(ORDER BY batch.XmlCol),
StatementId = stmt.XmlCol.value('(@StatementId)[1]', 'INT'),
NodeId = oper.XmlCol.value('(@NodeId)[1]', 'INT'),
StatementType = stmt.XmlCol.value('(@StatementType)[1]', 'SYSNAME'),
[Statement] = stmt.XmlCol.value('(@StatementText)[1]', 'NVARCHAR(4000)'),
ParamStatement = stmt.XmlCol.value('(@ParameterizedText)[1]', 'NVARCHAR(4000)'),
LogicalOp = oper.XmlCol.value('(@LogicalOp)[1]', 'SYSNAME'),
PhysicalOp = oper.XmlCol.value('(@PhysicalOp)[1]', 'SYSNAME'),
[TableReferenceId]= objt.XmlCol.value('(@TableReferenceId)[1]', 'INT'),
[IndexKind] = objt.XmlCol.value('(@IndexKind)[1]', 'SYSNAME'),
[ObjectName] = ISNULL(objt.XmlCol.value('(@Database)', 'SYSNAME') + '.' + objt.XmlCol.value('(@Schema)', 'SYSNAME') + '.', '') + objt.XmlCol.value('(@Table)[1]', 'SYSNAME'),
[IndexName] = ISNULL(objt.XmlCol.value('(@Index)', 'SYSNAME'), ''),
Warnings = wrng.XmlCol.query('.'),
MissingIndexes = misx.XmlCol.query('.'),
Process_Id = xp.Process_Id,
SPID = xp.SPID,
IsVictim= xp.IsVictim
FROM QueryPlans xp
CROSS APPLY xp.QueryPlan.nodes('//Batch') batch(XmlCol)
CROSS APPLY batch.XmlCol.nodes('Statements/StmtSimple') stmt(XmlCol)
OUTER APPLY stmt.XmlCol.nodes('QueryPlan/Warnings') wrng(XmlCol)
OUTER APPLY stmt.XmlCol.nodes('QueryPlan/MissingIndexes') misx(XmlCol)
OUTER APPLY stmt.XmlCol.nodes('QueryPlan//RelOp') oper(XmlCol) – Operators
–OUTER APPLY oper.XmlCol.nodes('(.//Object)[1]') objt(XmlCol)
OUTER APPLY oper.XmlCol.nodes('./*/Object') objt(XmlCol)
) a
) b
WHERE b.PhysicalOp IN (
'Table Insert', 'Table Update', 'Table Delete', 'Table Merge', 'Table Scan',
'Clustered Index Insert', 'Clustered Index Update', 'Clustered Index Delete', 'Clustered Index Merge', 'Clustered Index Scan', 'Clustered Index Seek',
'Index Insert', 'Index Update', 'Index Delete', 'Index Merge', 'Index Scan', 'Index Seek',
'RID Lookup' – Key Lookup = Clustered Index Seek
) AND EXISTS (
SELECT *
FROM #Resources r
WHERE (r.ProcessOwner_Id = b.Process_Id OR r.ProcessWaiter_Id = b.Process_Id)
AND r.Resource_ObjectName = b.ObjectName
AND r.Resource_IndexName = b.IndexName
)
ORDER BY IsVictim DESC, SPID, BatchId, StatementId, NodeId;
END;
GO