In a trace table generated by Profiler I tried to determine the origin of a lock using the column ObjectID2 joined to sys.allocation_units.

The results are not good. It identifies many tables that are NOT on the query that generated the trace.

Anyone knows what is that column means? My Google-fu failed me today.

The base query I used to find the object (the final one does not have the au.*, p.* part):

select object_name(p.object_id) AS name, tt.RowNumber, au.*, p.* 
  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

EDIT: The system health is a good indication of study. But for now I have to restrain myself to an SQL profiler trace table to study the locks of the query as I don't have easy access to customer's production server.
About the trace: the main event in trace is the Lock:Acquired.


1 Answer 1


The object ID is actually a partition id. This question over on SO provides a simple query to retrieve the needed information.

A side note, since you are using SQL Server 2008 R2 I would highly suggest taking a look into the System Health session that contains the recent deadlocks that have occurred, among other things. Depending on the activity of your instance you may need to pull this information out on a scheduled basis since it is buffer memory so does not stay there long. It is much more efficient to pull this information out than trying to setup a trace and waiting for it to occur again.

I actually wrote up a blog post on this area that includes a script to parse out the information where it is a bit more readable. The blog post is here, the script is below:

;WITH xDeadlock (Contents)
select CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) as DeadlockGraph
    (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
        [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('.'),
        [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,
        LockedObject = NULLIF(l.ObjectName, ''),
        LockProcess = p.ProcessID,
        LockedMode = l.LockMode,
    Locks l
    JOIN Process p ON p.ProcessID = l.LockProcessID
--WHERE p.TransactionTime > '2013-10-01'
  • +1 for mentioning System Health. It records deadlock events by default - learned at PASS Summit 2013 attending Jonathan's session.
    – Kin Shah
    Commented Dec 11, 2013 at 4:20
  • @Shawn I hate to kill someone's joy but your wonderful script is not working on sql 2014, master database. Msg 9421, Level 16, State 1, Line 1 XML parsing: line 5, character 60, illegal name character Commented Sep 8, 2015 at 13:20
  • @marcellomiorelli It is not going to because Extended Events XML changed with in 2012. This query only works for SQL Server 2008.
    – user507
    Commented Sep 8, 2015 at 13:53
  • @ShawnMelton that's a very useful script, when you have some time please, consider getting a sql 2014 version rolling Commented Sep 8, 2015 at 14:41
  • @marcellomiorelli you are more than welcome to ask a new question specific to that version of SQL Server, just link to this one.
    – user507
    Commented Sep 8, 2015 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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