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.*
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
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.
Best Answer
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: