Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I got a request to check out some errors on a 2008 sp3 server (10.0.5500.0 - Enterprise Edition (64-bit)) and see if I could find deadlocks that were happening earlier.

Traceflag 1222 wasn't enabled and the server owners did not want to enable it either. I used extended events and pulled out some info using the following query:

    SELECT  CAST(event_data.value('(event/data/value)[1]',
                               'varchar(max)') AS XML) AS DeadlockGraph
FROM    ( SELECT    XEvent.query('.') AS event_data
          FROM      (    -- Cast the target_data to XML 
                      SELECT    CAST(target_data AS XML) AS TargetData
                      FROM      sys.dm_xe_session_targets st
                                JOIN sys.dm_xe_sessions s
                                 ON s.address = st.event_session_address
                      WHERE     name = 'system_health'
                                AND target_name = 'ring_buffer'
                    ) AS Data -- Split out the Event Nodes 
                    CROSS APPLY TargetData.nodes('RingBufferTarget/
                                     event[@name="xml_deadlock_report"]')
                    AS XEventData ( XEvent )
        ) AS tab ( event_data )

I received a lot of info back that looked like this:

<deadlock>
  <victim-list>
    <victimProcess id="process14846ccbc8" />
  </victim-list>
  <process-list>
    <process id="process14846ccbc8" taskpriority="0" logused="0" waitresource="KEY: 25:72057594099728384 (0800dfc12261)" waittime="2842" ownerId="7035766745" transactionname="UPDATE" lasttranstarted="2012-11-20T07:50:25.023" XDES="0x14d9ade3b0" lockMode="U" schedulerid="8" kpid="17268" status="background" spid="19" sbid="0" ecid="0" priority="0" trancount="2">
      <executionStack>
        <frame procname="" line="53" stmtstart="2674" stmtend="2980" sqlhandle="0x030019002cf5fc35c2449200d2a000000100000000000000" />
      </executionStack>
      <inputbuf />
    </process>
    <process id="process3d9bdc8" taskpriority="0" logused="224" waitresource="KEY: 25:72057594099793920 (2f00faaf5d8e)" waittime="2315" ownerId="7035765940" transactionname="user_transaction" lasttranstarted="2012-11-20T07:50:24.913" XDES="0xdad541970" lockMode="U" schedulerid="6" kpid="15448" status="background" spid="23" sbid="0" ecid="0" priority="0" trancount="2">
      <executionStack>
        <frame procname="" line="549" stmtstart="32736" stmtend="33274" sqlhandle="0x03001900b9667f2bc41ade003ea000000100000000000000" />
        <frame procname="" line="549" stmtstart="34038" stmtend="35374" sqlhandle="0x03001900f28a732c051bde003ea000000100000000000000" />
        <frame procname="" line="81" stmtstart="4284" stmtend="4392" sqlhandle="0x030019002cf5fc35c2449200d2a000000100000000000000" />
      </executionStack>
      <inputbuf />
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594099728384" dbid="25" objectname="" indexname="" id="lock6b6a4ba00" mode="X" associatedObjectId="72057594099728384">
      <owner-list>
        <owner id="process3d9bdc8" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process14846ccbc8" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594099793920" dbid="25" objectname="" indexname="" id="lock74edcf180" mode="U" associatedObjectId="72057594099793920">
      <owner-list>
        <owner id="process14846ccbc8" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process3d9bdc8" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

How can I found out what the deadlocks are if objectname="" indexname="" ?

share|improve this question
up vote 7 down vote accepted

I can't answer as to why there is information missing, but I can point out how to extract it.

First, move to that database:

SELECT DB_NAME(25);

Take that result and:

USE <db_name>;

Now find out what these hobt_ids represent:

SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id) 
FROM sys.partitions 
WHERE hobt_id IN (72057594099728384, 72057594099793920);

You can also find the batches involved (if they're still around) using:

SELECT * FROM sys.dm_exec_sql_text(0x03001900b9667f2bc41ade003ea000000100000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x03001900f28a732c051bde003ea000000100000000000000);
SELECT * FROM sys.dm_exec_sql_text(0x030019002cf5fc35c2449200d2a000000100000000000000);

The offsets are there in the ExecutionStack so you can drill deeper into that output if you like (many examples of using offsets online).

EDIT Mr. Sandwiches pointed out the following Connect item, where Microsoft has stated that this bug is fixed in SQL Server 2012. Want them to address it in 2008 / 2008 R2? Vote! And more importantly, comment!

http://connect.microsoft.com/SQLServer/feedback/details/635391/objectname-indexname-not-populated-in-deadlock-graph-in-sql2008-sp1-and-sql2008-r2

share|improve this answer

As Aaron has already said, this is due to a bug in SQL Server 2008 / 2008 R2. The bug has been marked as fixed in a later version. It will not be fixed in SQL 2008 / 2008 R2.

I have a PowerShell script that I wrote to pull deadlocks from the event buffer, add the missing information if possible, and write the deadlock graphs out as .xdl files. The .xdl files can be opened with a text editor or in SQL Server Management Studio 2012 and above. The code below works in PowerShell 3.0. If you save the file as a .psm1 it can be imported using Import-Module and provides the commandlet Get-SqlDeadlockHistory.

I have also posted this as a workaround on the original bug along with a version that will work in PowerShell 2.0. The version that I posted here contains some improvements. Unfortunately, I can't edit the workaround I posted on the original bug to match.

function ConvertFrom-Hexadecimal([string] $hexString)
{
    [byte[]] $data = @()

    if ([string]::IsNullOrEmpty($hexString) -eq $true -or $hexString.Length % 2 -ne 0)
    {
        throw New-Object FormatException("Hexadecimal string must not be empty and must contain an even number of digits to be valid.");
    }

    $hexString = $hexString.ToUpperInvariant()
    $data = New-Object byte[] -ArgumentList ($hexString.Length / 2)

    for ([int] $index = 0; $index -lt $hexString.Length; $index += 2)
    {
        [int] $highDigitValue = if ($hexString[$index] -le ([char] '9')) { $hexString[$index] - ([char] '0') } else { $hexString[$index] - ([char] 'A') + 10 }
        [int] $lowDigitValue = if ($hexString[$index + 1] -le ([char] '9')) { $hexString[$index + 1] - ([char] '0') } else { $hexString[$index + 1] - ([char] 'A') + 10 }

        if ($highDigitValue -lt 0 -or $lowDigitValue -lt 0 -or $highDigitValue -gt 15 -or $lowDigitValue -gt 15)
        {
            throw New-Object FormatException("An invalid digit was encountered. Valid hexadecimal digits are 0-9 and A-F.")
        }
        else
        {
            [byte] $value = [byte](($highDigitValue -shl 4) -bor ($lowDigitValue -band 0x0F))
            $data[$index / 2] = $value;
        }
    }

    return ,$data
}

function Get-SqlDeadlockHistory
{
    <#
    .SYNOPSIS
    Gets the deadlock history from the system health extended events on the specified SQL Server instance.
    .DESCRIPTION
    Gets the deadlock history from the system health extended events on the specified SQL Server instance and writes them to the specified folder or to a Deadlocks directory in the current user's documents folder if no folder is specified.
    When the process completes, SQL server startup date, most recent deadlock date, total number of deadlocks, and number of deadlock graph files written will be displayed.
    .EXAMPLE
    Get-SqlDeadlockHistory "SqlServerName\Instance"
    .EXAMPLE
    Get-SqlDeadlockHistory "SqlServerName"
    .PARAMETER $SqlServerInstance
    The SQL Server name and instance that Get-SqlDeadlockHistory will connect to.
    .PARAMETER $OutputDirectory
    If specified the deadlock files will be written to the given directory; otherwise, deadlock files are written to Deadlocks\<SqlServerInstance> under the current user's My Documents folder.
    .PARAMETER $Overwrite
    If Overwrite is present deadlock files will overwrite any pre-existing deadlock files that have the same name; otherwise, deadlocks corresponding to pre-exsiting files will be skipped.
    #>
    [CmdletBinding(SupportsShouldProcess = $False)]
    param
    (
        [Parameter(Mandatory = $True, ValueFromPipeLine = $True)]
        [string] $SqlServerInstance,
        [Parameter(Mandatory = $False, ValueFromPipeLine = $False)]
        [string] $OutputDirectory = $null,
        [switch] $Overwrite
    )

    process
    { 
        if (([string]::IsNullOrWhiteSpace($OutputDirectory) -eq $true) -or ((Test-Path $OutputDirectory -PathType Container) -eq $false))
        {
            $OutputDirectory = Join-Path $env:USERPROFILE "Documents\Deadlocks\$($SqlServerInstance.Replace(":", "_"))"
        }

        [System.Data.SqlClient.SqlConnectionStringBuilder] $connectionStringBuilder = New-Object "System.Data.SqlClient.SqlConnectionStringBuilder"
        $connectionStringBuilder.PSBase.DataSource = $SqlServerInstance
        $connectionStringBuilder.PSBase.InitialCatalog = "master"
        $connectionStringBuilder.PSBase.IntegratedSecurity = $true
        $connectionStringBuilder.PSBase.WorkstationID = $env:COMPUTERNAME
        $connectionStringBuilder.PSBase.ApplicationName = $MyInvocation.MyCommand.Name

        if ((Test-Path $outputDirectory -PathType Container) -eq $false)
        {
            New-Item $outputDirectory -ItemType Container
        }

        [string] $startTimeQuery = "SELECT sqlserver_start_time FROM sys.dm_os_sys_info"

        [string] $deadlockQuery = "SELECT        ringBuffer.event.value(N'./@timestamp', N'datetimeoffset') AS timestamp
            , CONVERT(xml, CASE WHEN CONVERT(varchar(3), SERVERPROPERTY(N'ProductVersion')) = '10.' THEN ringBuffer.event.value(N'(./data/value/node())[1]', N'nvarchar(MAX)') ELSE ringBuffer.event.query(N'(./data/value/node())[1]') END) AS deadlockGraph
FROM            (
                    SELECT        CONVERT(xml, sys.dm_xe_session_targets.target_data) AS target_data
                    FROM            sys.dm_xe_session_targets INNER JOIN
                                             sys.dm_xe_sessions ON sys.dm_xe_session_targets.event_session_address = sys.dm_xe_sessions.address
                    WHERE        (sys.dm_xe_sessions.name = 'system_health')
                ) AS sessionTarget
                CROSS APPLY sessionTarget.target_data.nodes(N'/RingBufferTarget/event[@name=`"xml_deadlock_report`"]') AS ringBuffer(event)"

        [string] $partitionQuery = "CREATE TABLE #allPartitions
(
    partition_id bigint NOT NULL,
    database_id int NOT NULL,
    hobt_id bigint NOT NULL,
    object_id int NOT NULL,
    index_id int NOT NULL,
    database_name sysname NOT NULL,
    object_schema_name sysname NOT NULL,
    object_name sysname NOT NULL,
    index_name sysname NULL
)
ALTER TABLE dbo.#allPartitions ADD CONSTRAINT
    PK_#allPartitions PRIMARY KEY CLUSTERED 
    (
    database_id,
    partition_id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

DECLARE @partitionQuery nvarchar(2000)
DECLARE @dbName sysname

SET @partitionQuery = 'USE [?];
INSERT INTO [#allPartitions]
                         (partition_id, database_id, hobt_id, object_id, index_id, database_name, object_schema_name, object_name, index_name)
SELECT        sys.partitions.partition_id, DB_ID(N''?'') AS database_id, sys.partitions.hobt_id, sys.partitions.object_id, sys.partitions.index_id, ''?'' AS database_name, 
                         OBJECT_SCHEMA_NAME(sys.partitions.object_id) AS object_schema_name, OBJECT_NAME(sys.partitions.object_id) AS object_name, sys.indexes.name AS index_name
FROM            sys.partitions LEFT OUTER JOIN
                         sys.indexes ON sys.indexes.object_id = sys.partitions.object_id AND sys.indexes.index_id = sys.partitions.index_id'

EXEC sp_MSforeachdb @partitionQuery

SELECT        partition_id, database_id, hobt_id, object_id, index_id, database_name, object_schema_name, object_name, index_name
FROM            #allPartitions

DROP TABLE #allPartitions
"

        [string] $objectQuery = "
CREATE TABLE #allObjects
(
    database_id int NOT NULL,
    object_id int NOT NULL,
    database_name sysname NOT NULL,
    object_schema_name sysname NOT NULL,
    object_name sysname NOT NULL
)
ALTER TABLE dbo.#allObjects ADD CONSTRAINT
    PK_#allObjects PRIMARY KEY CLUSTERED 
    (
    database_id,
    object_id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

DECLARE @objectQuery nvarchar(2000)
DECLARE @dbName sysname

SET @objectQuery = 'USE [?];
INSERT INTO [#allObjects]
                         (database_id, object_id, database_name, object_schema_name, object_name)
SELECT        DB_ID(N''?'') AS database_id, object_id, ''?'' AS database_name, SCHEMA_NAME(schema_id) AS object_schema_name,
                         name AS object_name
FROM            sys.objects'

EXEC sp_MSforeachdb @objectQuery

SELECT        database_id, object_id, database_name, object_schema_name, object_name
FROM            #allObjects

DROP TABLE #allObjects
"

        $procIDQuery = "SELECT dbid, objectid FROM sys.dm_exec_sql_text(@sqlHandle)"

        [System.Data.SqlClient.SqlConnection] $connection = New-Object "System.Data.SqlClient.SqlConnection" -ArgumentList @($connectionStringBuilder.ConnectionString)

        [System.Data.SqlClient.SqlCommand] $startTimeQueryCommand = $connection.CreateCommand()
        $startTimeQueryCommand.CommandText = $startTimeQuery
        $startTimeQueryCommand.CommandType = [System.Data.CommandType]::Text

        [System.Data.SqlClient.SqlCommand] $deadlockQueryCommand = $connection.CreateCommand()
        $deadlockQueryCommand.CommandText = $deadlockQuery
        $deadlockQueryCommand.CommandType = [System.Data.CommandType]::Text

        [System.Data.SqlClient.SqlDataAdapter] $deadlockQueryDataAdapter = New-Object "System.Data.SqlClient.SqlDataAdapter" -ArgumentList $deadlockQueryCommand
        [System.Data.DataTable] $deadlockTable = New-Object "System.Data.DataTable"

        [System.Data.SqlClient.SqlCommand] $partitionQueryCommand = $connection.CreateCommand()
        $partitionQueryCommand.CommandText = $partitionQuery
        $partitionQueryCommand.CommandType = [System.Data.CommandType]::Text

        [System.Data.SqlClient.SqlDataAdapter] $partitionQueryDataAdapter = New-Object "System.Data.SqlClient.SqlDataAdapter" -ArgumentList $partitionQueryCommand
        [System.Data.DataTable] $partitionTable = New-Object "System.Data.DataTable"

        [System.Data.SqlClient.SqlCommand] $objectQueryCommand = $connection.CreateCommand()
        $objectQueryCommand.CommandText = $objectQuery
        $objectQueryCommand.CommandType = [System.Data.CommandType]::Text

        [System.Data.SqlClient.SqlDataAdapter] $objectQueryDataAdapter = New-Object "System.Data.SqlClient.SqlDataAdapter" -ArgumentList $objectQueryCommand
        [System.Data.DataTable] $objectTable = New-Object "System.Data.DataTable"

        [System.Data.SqlClient.SqlCommand] $procIDQueryCommand = $connection.CreateCommand()
        $procIDQueryCommand.CommandText = $procIDQuery
        $procIDQueryCommand.CommandType = [System.Data.CommandType]::Text
        $procIDSqlHandleParam = $procIDQueryCommand.Parameters.Add("@sqlHandle", [System.Data.SqlDbType]::VarBinary, 64)

        $connection.Open()
        [DateTime] $startTime = $startTimeQueryCommand.ExecuteScalar()
        [int] $deadlockRowsReturned = $deadlockQueryDataAdapter.Fill($deadlockTable)
        [int] $partitionRowsReturned = $partitionQueryDataAdapter.Fill($partitionTable)
        [int] $objectRowsReturned = $objectQueryDataAdapter.Fill($objectTable)

        $startTimeQueryCommand.Dispose()
        $deadlockQueryDataAdapter.Dispose()
        $deadlockQueryCommand.Dispose()
        $partitionQueryDataAdapter.Dispose()
        $partitionQueryCommand.Dispose()
        $objectQueryDataAdapter.Dispose()
        $objectQueryCommand.Dispose()

        $deadlocksWritten = 0
        [DateTimeOffset] $mostRecentDeadlock = [DateTimeOffset]::MinValue

        ForEach ($deadlockRow in $deadlockTable.Rows)
        {
            $deadlockLocalTime = $deadlockRow.timestamp.ToLocalTime()

            if ($deadlockLocalTime -gt $mostRecentDeadlock)
            {
                $mostRecentDeadlock = $deadlockLocalTime
            }

            [string] $xdlFilePath = Join-Path $OutputDirectory "Deadlock_$($deadlockLocalTime.ToString("yyyyMMdd_HHmmss_fff")).xdl"

            if (((Test-Path $xdlFilePath) -eq $false) -or ($Overwrite -eq $true))
            {
                [XML] $deadlockGraphDocument = $deadlockRow.deadlockGraph

                $missingObjectNameNodes = $deadlockGraphDocument.deadlock.'resource-list'.SelectNodes("child::node()[@objectname='']")

                ForEach ($missingObjectNameNode in $missingObjectNameNodes)
                {
                    $objectName = $null
                    $indexName = $null

                    if ($missingObjectNameNode.hobtid -ne $null)
                    {
                        $objectRow = $partitionTable.Where({ ($_.hobt_id -eq $missingObjectNameNode.hobtid) -and ($_.database_id -eq $missingObjectNameNode.dbid) })
                        $objectName = "$($objectRow.database_name).$($objectRow.object_schema_name).$($objectRow.object_name)"
                        $indexName = $objectRow.index_name
                    }
                    elseif ($missingObjectNameNode.associatedObjectId -ne $null)
                    {
                        $objectRow = $partitionTable.Where({ ($_.partition_id -eq $missingObjectNameNode.associatedObjectId) -and ($_.database_id -eq $missingObjectNameNode.dbid) })
                        $objectName = "$($objectRow.database_name).$($objectRow.object_schema_name).$($objectRow.object_name)"
                    }

                    if ($objectName -ne $null)
                    {
                        $missingObjectNameNode.objectname = $objectName
                    }

                    if ($indexName -ne $null)
                    {
                        $missingObjectNameNode.indexname = $indexName
                    }
                }

                $executionStackFrames = $deadlockGraphDocument.deadlock.'process-list'.SelectNodes('//process/executionStack/frame[@procname=""]')

                ForEach ($executionStackFrame in $executionStackFrames)
                {
                    if ([string]::IsNullOrWhiteSpace($executionStackFrame.sqlhandle) -eq $False)
                    {
                        $procIDSqlHandleParam.Value = ConvertFrom-Hexadecimal $executionStackFrame.sqlhandle.Replace("0x", "")
                        $procReader = $procIDQueryCommand.ExecuteReader()

                        if ($procReader.HasRows -eq $true)
                        {
                            $currentDatabaseID = $executionStackFrame.ParentNode.ParentNode.currentdb

                            $rowAvailable = $procReader.Read()
                            $dbid = $procReader["dbid"]
                            $procID = $procReader["objected]"

                            if ($procID -ne $null -and $procID -ne [System.DBNull]::Value)
                            {
                                if ($dbid -eq $null -or $dbid -eq [System.DBNull]::Value)
                                {
                                    $dbid = $currentDatabaseID
                                }

                                $procRow = $objectTable.Where({ ($_.database_id -eq $currentDatabaseID) -and ($_.object_id -eq $procID) })
                                $executionStackFrame.procname = "$($procRow.database_name).$($procRow.object_schema_name).$($procRow.object_name)"
                            }
                        }

                        $procReader.Close()
                        $procReader.Dispose()
                }

                [System.Xml.XmlWriterSettings] $xdlWriterSettings = New-Object "System.Xml.XmlWriterSettings"
                $xdlWriterSettings.OmitXmlDeclaration = $false
                $xdlWriterSettings.Indent = $true
                $xdlWriterSettings.NewLineHandling = [System.Xml.NewLineHandling]::Replace
                $xdlWriterSettings.CloseOutput = $true

                [System.Xml.XmlWriter] $xdlWriter = [System.Xml.XmlWriter]::Create($xdlFilePath, $xdlWriterSettings)

                $deadlockGraphDocument.Save($xdlWriter)

                $xdlWriter.Close()
                $xdlWriter.Dispose()

                $deadlocksWritten++
            }
        }

        $procIDQueryCommand.Dispose()
        $connection.Close()
        $connection.Dispose()
        $deadlockTable.Dispose()
        $partitionTable.Dispose()
        $objectTable.Dispose()

        Write-Host "$($SqlServerInstance) up since $($startTime)."

        if ($mostRecentDeadlock -gt [DateTimeOffset]::MinValue)
        {
            Write-Host "Most recent deadlock occurred at $($mostRecentDeadlock)."
        }

        Write-Host "$($deadlocksWritten) of $($deadlockRowsReturned) deadlock graphs written to $($outputDirectory)."
    }
}
share|improve this answer
    
This is pretty awesome. I'm definitely going to be using this. Thank you. – SqlSandwiches Feb 11 at 15:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.