SQL Server 配置文件跟踪中的列的解釋

在 Profiler 生成的跟踪表中，我嘗試使用連接到 sys.allocation_units 的列 ObjectID2 來確定鎖的來源。

結果並不好。它標識了許多不在生成跟踪的查詢上的表。

誰知道那一欄是什麼意思？我的 Google-fu 今天讓我失望了。

我用來查找對象的基本查詢（最後一個沒有au.*, p.* 部分）：

select object_name(p.object_id) AS name, tt.RowNumber, au.*, p.* 
from 
 Trc_Tables_20131210 tt
 INNER JOIN sys.allocation_units au
   ON tt.ObjectID2 = au.allocation_unit_id  AND tt.ObjectID = 0
 INNER JOIN sys.partitions p
   ON au.container_id = p.hobt_id

WHERE au.type IN (1,3) AND tt.EventClass = 24

編輯：系統健康是學習的一個很好的跡象。但是現在我必須限制自己使用 SQL 分析器跟踪表來研究查詢的鎖定，因為我無法輕鬆訪問客戶的生產伺服器。

關於trace：trace中的主要事件是Lock:Acquired。

對象 ID 實際上是一個分區 ID。這個關於 SO 的問題提供了一個簡單的查詢來檢索所需的資訊。

附帶說明一下，由於您使用的是 SQL Server 2008 R2，因此我強烈建議您查看包含最近發生的死鎖等內容的系統健康會話。根據您的實例的活動，您可能需要按計劃提取此資訊，因為它是緩衝記憶體，因此不會在那裡停留很長時間。提取此資訊比嘗試設置跟踪並等待它再次出現要有效得多。

我實際上寫了一篇關於這個領域的部落格文章，其中包括一個腳本來解析出更易讀的資訊。部落格文章在這裡，腳本如下：

;WITH xDeadlock (Contents)
AS
(
select CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) as DeadlockGraph
FROM
   (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') AS Data
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
), Victims AS
(
SELECT    ID = Victims.List.value('@id', 'varchar(50)')
FROM xDeadlock
CROSS APPLY xDeadlock.Contents.nodes('//deadlock/victim-list/victimProcess') AS Victims (List)
), Locks AS
(
SELECT  --xDeadlock.DeadlockID,
       MainLock.Process.value('@id', 'varchar(100)') AS LockID,
       OwnerList.Owner.value('@id', 'varchar(200)') AS LockProcessId,
       REPLACE(MainLock.Process.value('local-name(.)', 'varchar(100)'), 'lock', '') AS LockEvent,
       MainLock.Process.value('@objectname', 'sysname') AS ObjectName,
       OwnerList.Owner.value('@mode', 'varchar(10)') AS LockMode,
       MainLock.Process.value('@dbid', 'INTEGER') AS Database_id,
       MainLock.Process.value('@associatedObjectId', 'BIGINT') AS AssociatedObjectId,
       MainLock.Process.value('@WaitType', 'varchar(100)') AS WaitType,
       WaiterList.Owner.value('@id', 'varchar(200)') AS WaitProcessId,
       WaiterList.Owner.value('@mode', 'varchar(10)') AS WaitMode
FROM    xDeadlock
       CROSS APPLY xDeadlock.Contents.nodes('//deadlock/resource-list') AS Locks (list)
       CROSS APPLY Locks.List.nodes('*') AS MainLock (Process)
       CROSS APPLY MainLock.Process.nodes('owner-list/owner') AS OwnerList (Owner)
       CROSS APPLY MainLock.Process.nodes('waiter-list/waiter') AS WaiterList (Owner)
), Process AS 
(
-- get the data from the process node
SELECT  --xDeadlock.DeadlockID,
       [Victim] = CONVERT(BIT, CASE WHEN Deadlock.Process.value('@id', 'varchar(50)') = ISNULL(Deadlock.Process.value('../../@victim', 'varchar(50)'), v.ID) 
                                    THEN 1
                                    ELSE 0
                               END),
       [LockMode] = Deadlock.Process.value('@lockMode', 'varchar(10)'), -- how is this different from in the resource-list section?
       [ProcessID] = Process.ID, --Deadlock.Process.value('@id', 'varchar(50)'),
       [KPID] = Deadlock.Process.value('@kpid', 'int'), -- kernel-process id / thread ID number
       [SPID] = Deadlock.Process.value('@spid', 'int'), -- system process id (connection to sql)
       [SBID] = Deadlock.Process.value('@sbid', 'int'), -- system batch id / request_id (a query that a SPID is running)
       [ECID] = Deadlock.Process.value('@ecid', 'int'), -- execution context ID (a worker thread running part of a query)
       [IsolationLevel] = Deadlock.Process.value('@isolationlevel', 'varchar(200)'),
       [WaitResource] = Deadlock.Process.value('@waitresource', 'varchar(200)'),
       [LogUsed] = Deadlock.Process.value('@logused', 'int'),
       [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
       [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
       [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
       [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
       [BatchStarted] = Deadlock.Process.value('@lastbatchstarted', 'datetime'),
       [BatchCompleted] = Deadlock.Process.value('@lastbatchcompleted', 'datetime'),
       [InputBuffer] = Input.Buffer.query('.'),
       xDeadlock.[Contents],
       [QueryStatement] = Execution.Frame.value('.', 'varchar(max)'),
       TranCount = Deadlock.Process.value('@trancount', 'int')
FROM    xDeadlock
       CROSS APPLY xDeadlock.Contents.nodes('//deadlock/process-list/process') AS Deadlock (Process)
       CROSS APPLY (SELECT Deadlock.Process.value('@id', 'varchar(50)') ) AS Process (ID)
       LEFT JOIN Victims v ON Process.ID = v.ID
       CROSS APPLY Deadlock.Process.nodes('inputbuf') AS Input (Buffer)
       CROSS APPLY Deadlock.Process.nodes('executionStack') AS Execution (Frame)
)
-- get the columns in the desired order
SELECT  p.Victim,
       p.LockMode,
       LockedObject = NULLIF(l.ObjectName, ''),
       l.database_id,
       l.AssociatedObjectId,
       LockProcess = p.ProcessID,
       p.KPID,
       p.SPID,
       p.SBID,
       p.ECID,
       p.TranCount,
       l.LockEvent,
       LockedMode = l.LockMode,
       l.WaitProcessID,
       l.WaitMode,
       p.WaitResource,
       l.WaitType,
       p.IsolationLevel,
       p.LogUsed,
       p.ClientApp,
       p.HostName,
       p.LoginName,
       p.TransactionTime,
       p.BatchStarted,
       p.BatchCompleted,
       p.InputBuffer
FROM    
   Locks l
   JOIN Process p ON p.ProcessID = l.LockProcessID
--WHERE p.TransactionTime > '2013-10-01'
ORDER BY p.Victim DESC,
       p.ProcessId;

引用自：https://dba.stackexchange.com/questions/54810