August 12, 2025 at 4:59 pm
Hi, i cannot recall the object or even if it comes off the shelf but i once saw a tool in sql server where the competing tables etc involved in a deadlock are recorded. I think you had to know a bit about the complicated looking test it provided but it was sweet. my recollection is that it was on one of those system databases.
i dont think it was one of these...but am not sure...it was specific to deadlocks...
SQL Server Error Log:
Trace Flags 1204 and 1222:
Extended Events:
SQL Server Profiler:
Third-party Monitoring Tools:
August 12, 2025 at 5:11 pm
so deadlock information is capture int eh system health extended event, in the stack of files it saves.
here's a really good stack of queries i put together and use on a daily basis. the first query gives you an overview. how many deadlocks and the deadlocks per day.
the query that pulls from the extended event takes a long time...five minutes or more, depending on how many deadlocks there are. but to save us effort, it puts that information into a temp table, so you don't need to redo it over and over.
the last two queries give us a rollup of the most frequent deadlocks, so something that happened 500 times is more important to fix that something that happened 5 times., and the last query is all deadlocks, ordered by the most recent. you can save the xml of the deadlock as *.xdl to disk, and then reopen it to view the graphical version of the deadlock.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
--desc: deadlock occurrences report: # deadlocks since server restart
SELECT
'Deadlocks Occurrences Report',
CONVERT(BIGINT,((1.0 * p.cntr_value /
NULLIF(datediff(DD,d.create_date,CURRENT_TIMESTAMP),0)))) as
AveragePerDay,
CAST(p.cntr_value AS NVARCHAR(100)) + ' deadlocks have been recorded
since startup.' AS Details,
d.create_date as StartupDateTime
FROM sys.dm_os_performance_counters p
INNER JOIN sys.databases d ON d.name = 'tempdb'
WHERE RTRIM(p.counter_name) = 'Number of Deadlocks/sec'
AND RTRIM(p.instance_name) = '_Total'
;
--desc: greatly improved version for getting deadlocks
IF OBJECT_ID('[tempdb]..[#RawDeadlocks]') IS NOT NULL
DROP TABLE [#RawDeadlocks]
GO
IF OBJECT_ID('[tempdb]..[#DeadlockDetails]') IS NOT NULL
DROP TABLE [#DeadlockDetails]
GO
CREATE TABLE [#RawDeadlocks] (
[DeadlockReportID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[DeadlockReport] XML NULL,
[Execution_Time] DATETIME NULL)
IF OBJECT_ID('tempdb..[#errorlog]') IS NOT NULL
DROP TABLE [#errorlog]
CREATE TABLE [#errorlog] (
[LogDate] DATETIME NULL,
[ProcessInfo] VARCHAR(100) NULL,
[Text] VARCHAR(max) NULL)
--#################################################################################################
-- get the tag
--#################################################################################################
DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX);
Declare @LatestExecutionTime datetime
INSERT INTO #errorlog EXEC sp_readerrorlog;
SELECT @tag = text
FROM #errorlog
WHERE [Text] LIKE 'Logging%MSSQL\Log%';
--#################################################################################################
-- get the deadlock graphs in to a table
--#################################################################################################
SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29);
INSERT INTO [#RawDeadlocks]([DeadlockReport],[Execution_Time])
SELECT
CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
CONVERT(xml, event_data) .value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime')
AS Execution_Time
FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME like 'xml_deadlock_report'
-- SELECT * FROM msdb.dbo.RawDeadlocks;
SELECT
DeadlockReport,
DeadlockReport.value('deadlock[1]/process-list[1]/process[1]/@lastbatchcompleted', 'datetime') AS [lastbatchcompleted],
DeadlockReport.value('deadlock[1]/process-list[1]/process[1]/executionStack[1]/frame[1]/@procname', 'varchar(128)') AS VictimInfo,
DeadlockReport.value('deadlock[1]/process-list[1]/process[1]/executionStack[1]/frame[2]/@procname', 'varchar(128)') AS VictimInfo2,
DeadlockReport.value('deadlock[1]/process-list[1]/process[1]/inputbuf[1]/@text', 'varchar(128)') AS InputBuffer,
DeadlockReport.value('deadlock[1]/process-list[1]/process[1]/@currentdbname', 'varchar(128)') AS [currentdb],
DeadlockReport.value('deadlock[1]/process-list[1]/process[1]/@waitresource', 'varchar(128)') AS [waitresource],
DeadlockReport.value('deadlock[1]/process-list[1]/process[1]/@status', 'varchar(128)') AS [status],
DeadlockReport.value('deadlock[1]/process-list[1]/process[1]/@transactionname', 'varchar(128)') AS [transactionname],
DeadlockReport.value('deadlock[1]/process-list[1]/process[1]/@clientapp', 'varchar(128)') AS [clientapp],
DeadlockReport.value('deadlock[1]/process-list[1]/process[1]/@hostname', 'varchar(128)') AS [hostname],
DeadlockReport.value('deadlock[1]/process-list[1]/process[1]/@loginname', 'varchar(128)') AS [loginname],
DeadlockReport.value('deadlock[1]/process-list[1]/process[1]/@lastbatchstarted', 'datetime') AS [lastbatchstarted]
INTO #DeadlockDetails
FROM [#RawDeadlocks]
ORDER BY lastbatchcompleted desc;
--rollup
SELECT
MIN([lastbatchcompleted]) AS EarliestDeadlock,
MAX([lastbatchcompleted]) AS ALtestDeadlock,
DATEDIFF(HOUR,MIN([lastbatchcompleted]),MAX([lastbatchcompleted])) AS SpanInHours,
VictimInfo,
MAX(VictimInfo2),
COUNT(*) AS TheCount
FROM [#DeadlockDetails]
GROUP BY VictimInfo
ORDER BY TheCount DESC
--details
SELECT * FROM [#DeadlockDetails] ORDER BY [lastbatchcompleted] DESC
Lowell
August 12, 2025 at 5:25 pm
thx lowell, do one or more lines in there tell you which resources deadlocked, on what indexes etc?
August 12, 2025 at 5:46 pm
never mind , i see that info in the deadlock report links.
Instead can you tell me 1) what the frame ids are?, 2) where in the deadlock report i might find the victim's index and the winner's index?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply