为什么死锁图中没有填充对象名和索引名？

我无法回答为什么缺少信息，但我可以指出如何提取它。

首先，移动到该数据库：

SELECT DB_NAME(25);

拿那个结果，然后：

USE <db_name>;

现在找出这些 hobt_id 代表什么：

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

您还可以使用以下方法找到所涉及的批次（如果它们仍然存在）：

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

偏移量在那里，ExecutionStack因此您可以根据需要更深入地研究该输出（许多在线使用偏移量的示例）。

编辑Sandwiches 先生指出了以下 Connect 项（不再可用），其中 Microsoft 已声明此错误已在 SQL Server 2012 中修复。它不会在 2008/R2 中修复。

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

正如 Aaron 已经说过的，这是由于 SQL Server 2008 / 2008 R2 中的一个错误造成的。该错误已在更高版本中标记为已修复。它不会在 SQL 2008 / 2008 R2 中修复。

我编写了一个 PowerShell 脚本，用于从事件缓冲区中提取死锁，如果可能，添加丢失的信息，并将死锁图写为 .xdl 文件。可以使用文本编辑器或在 SQL Server Management Studio 2012 及更高版本中打开 .xdl 文件。下面的代码适用于 PowerShell 3.0。如果将文件保存为 .psm1，则可以使用 Import-Module 导入它并提供命令行开关 Get-SqlDeadlockHistory。此脚本也适用于更高版本的 SQL Server。

我还发布了这个作为原始错误的解决方法以及可在 PowerShell 2.0 中运行的版本。我在这里发布的版本包含一些改进。不幸的是，我无法编辑我在原始错误上发布的解决方法以匹配。

# Begin hexadecimal conversion function
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
}
# End hexadecimal conversion function

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 $UserName
    If specified a SQL autenticated account will be used to connect to the SQL Server instance.
    .PARAMETER $Password
    If a UserName has been specified, uses the given password for the SQL authenticated account; otherwise, the user will be prompted for a password. If UserName has not been specified, this parameter is ignored.
    .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] $UserName = $null,
        [Parameter(Mandatory = $false, ValueFromPipeLine = $false)]
        [string] $Password = $null,
        [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(":", "_"))"
        }

        Write-Host "Deadlock events will be retrieved from $($SqlServerInstance) and written to $($OutputDirectory)."

        [System.Data.SqlClient.SqlConnectionStringBuilder] $connectionStringBuilder = New-Object "System.Data.SqlClient.SqlConnectionStringBuilder"
        $connectionStringBuilder.PSBase.DataSource = $SqlServerInstance
        $connectionStringBuilder.PSBase.InitialCatalog = "master"

        if ($PSBoundParameters.ContainsKey("UserName") -eq $true)
        {
            $connectionStringBuilder.PSBase.UserID = $UserName

            if ($PSBoundParameters.ContainsKey("Password") -eq $true)
            {
                $connectionStringBuilder.PSBase.Password = $Password
            }
            else
            {
                $connectionStringBuilder.PSBase.Password = (Read-Host "Password: ")
            }
        }
        else
        {
                $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
"

        $sqlTextQuery = "SELECT dbid, objectid, text 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] $sqlTextQueryCommand = $connection.CreateCommand()
        $sqlTextQueryCommand.CommandText = $sqlTextQuery
        $sqlTextQueryCommand.CommandType = [System.Data.CommandType]::Text
        $sqlTextSqlHandleParam = $sqlTextQueryCommand.Parameters.Add("@sqlHandle", [System.Data.SqlDbType]::VarBinary, 64)

        Write-Verbose "Connecting to $($SqlServerInstance)."
        $connection.Open()

        Write-Verbose "Getting SQL Server start time."
        [DateTime] $startTime = $startTimeQueryCommand.ExecuteScalar()

        Write-Verbose "Getting SQL deadlock events from the ring buffer."
        [int] $deadlockRowsReturned = $deadlockQueryDataAdapter.Fill($deadlockTable)

        Write-Verbose "Getting partiton information."
        [int] $partitionRowsReturned = $partitionQueryDataAdapter.Fill($partitionTable)

        Write-Verbose "Getting object information."
        [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

        [int] $deadlockCount = $deadlockTable.Rows.Count
        [int] $deadlockItem = 0;
        Write-Verbose "Processing deadlock $($deadlockCount) graphs."

        ForEach ($deadlockRow in $deadlockTable.Rows)
        {
            Write-Progress -Id 1 -Activity "Processing Deadlock Graphs" -PercentComplete ($deadlockItem / $deadlockCount * 100)
            $deadlockItem++

            $deadlockLocalTime = $deadlockRow.timestamp.ToLocalTime()

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

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

            Write-Verbose "Using deadlock graph file $($xdlFileName)."

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

                $missingObjectNameNodes = $deadlockGraphDocument.deadlock.'resource-list'.SelectNodes("child::node()[@objectname='']")
                Write-Verbose "Found $($missingObjectNameNodes.Count) resource node(s) with no object names."

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

                    if ($missingObjectNameNode.hobtid -ne $null)
                    {
                        Write-Verbose "Looking up Heap or B-Tree ID $($missingObjectNameNode.hobtid)."
                        $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)
                    {
                        Write-Verbose "Looking up Partition ID $($missingObjectNameNode.associatedObjectId)."
                        $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)
                    {
                        Write-Verbose "Found object $($objectName)."
                        $missingObjectNameNode.objectname = $objectName
                    }

                    if ($indexName -ne $null)
                    {
                        Write-Verbose "Found index $($indexName)."
                        $missingObjectNameNode.indexname = $indexName
                    }
                }

                $executionStackFrames = $deadlockGraphDocument.deadlock.'process-list'.SelectNodes('//process/executionStack/frame[@procname="" or text() = "" or normalize-space(text())="unknown" or contains(text(), "*password----")]')
                Write-Verbose "Found $($executionStackFrames.Count) execution stack frame(s) with no proc name or no statement text."

                ForEach ($executionStackFrame in $executionStackFrames)
                {
                    if ([string]::IsNullOrWhiteSpace($executionStackFrame.sqlhandle) -eq $false)
                    {
                        Write-Verbose "Looking up sql handle $($executionStackFrame.sqlhandle)."

                        $sqlTextSqlHandleParam.Value = ConvertFrom-Hexadecimal $executionStackFrame.sqlhandle.Replace("0x", "")
                        $sqlTextReader = $sqlTextQueryCommand.ExecuteReader()

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

                            $rowAvailable = $sqlTextReader.Read()
                            $dbid = $sqlTextReader["dbid"]
                            $objectID = $sqlTextReader["objectid"]
                            $queryText = $sqlTextReader["text"]

                            if ([string]::IsNullOrWhitespace($executionStackFrame.procname) -eq $true -and $objectID -ne $null -and $objectID -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 $objectID) })
                                $procName = "$($procRow.database_name).$($procRow.object_schema_name).$($procRow.object_name)"
                                Write-Verbose "Found proc $($procName)."

                                $executionStackFrame.procname = $procName
                            }

                            $isQueryTextMissing = [string]::IsNullOrWhitespace($executionStackFrame.InnerText) -eq $true -or $executionStackFrame.InnerText.Trim() -eq "unknown" -or $executionStackFrame.InnerText -match "\*password-+\s*$"
                            $hasQueryLookupResult = $queryText -ne $null -and $queryText -ne [DBNull]::Value

                            if ($isQueryTextMissing -eq $true -and $hasQueryLookupResult -eq $true)
                            {
                                $statementStart = 0
                                if ([string]::IsNullOrWhiteSpace($executionStackFrame.stmtstart) -eq $false)
                                {
                                    $statementStart = [int]::Parse($executionStackFrame.stmtstart) / 2
                                }

                                $statementEnd = $queryText.Length - 1
                                if ([string]::IsNullOrWhiteSpace($executionStackFrame.stmtend) -eq $false -and $executionStackFrame.stmtend -ne '-1')
                                {
                                     $statementEnd = [int]::Parse($executionStackFrame.stmtend) / 2
                                }

                                $statementLength = ($statementEnd - $statementStart) + 1
                                $statementText = $queryText.Substring($statementStart, $statementLength)
                                Write-Verbose "Found statement text."

                                $executionStackFrame.InnerText = $statementText
                            }
                        }

                        $sqlTextReader.Close()
                        $sqlTextReader.Dispose()
                    }
                }

                Write-Verbose "Writing deadlock graph to file."
                [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++
            }
            else
            {
                Write-Verbose "Pre-existing deadlock graph file will not be overwritten."
            }
        }
        Write-Progress -Id 1 -Activity "Processing Deadlock Graphs" -Completed

        $sqlTextQueryCommand.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)."
    }
}