built in deadlock information capture

  • 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:

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • thx lowell, do one or more lines in there tell you which resources deadlocked, on what indexes etc?

  • 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