Profiler: Deadlock Graph - Associated ObjectID

[Michael MacGregor]

I'm sure I asked this question on here before and pretty sure I got a very
useful answer to my question, however, being the dufus I am I didn't save
the answer and have long since forgotten what it was. So here I am again,
encamped upon your floor to ask once more what is the Associated ObjectaID
that gets displayed in the deadlock graph because it sure doesn't seem to be
an object_id from sys.objects?

Can anyone please explain to this forgetful old DBA?

An example of an associated object id would be like 72057647487385600.

Thanks,

Michael MacGregor
Database Architect

[Michael MacGregor]

Whilst I am on the subject? Is there a really good book, or article that
fully explains the deadlock graph because so far neither BOL nor anything
else I can find online does a satisfactory job of it.

TIA

MTM

[Aaron Bertrand [SQL Server MVP]]

That's the HoBt ID. (HoBt stands for "Heap or B-tree.") In SQL Server 2005
and 2008 the HoBt ID is identical to the partition_id. So, you can say:

SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE partition_id = 72057647487385600;

If you use non-dbo schemas then it is safer to say (assuming 2005 P2 or
higher):

SELECT OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
FROM sys.partitions
WHERE partition_id = 72057647487385600;

As for your follow-up question, Grant Fritchey does a great job demystifying
deadlock graph analysis in "SQL Server 2008 Query Performance Tuning
Distilled." (ISBN = 978-1-4302-1902-6)

Don't be scared by the version in the title; most of the concepts in the
book apply equally to 2005. Since you are looking at sys.objects I assume
you are not concerned about SQL 2000, but it never hurts to mention which
version(s) you are managing.

A

On 6/15/09 12:59 PM, in article #NgSkqd7...@TK2MSFTNGP03.phx.gbl,

[Aaron Bertrand [SQL Server MVP]]

This may be a useful primer also...

http://is.gd/12HnD

On 6/15/09 1:17 PM, in article #U2s00d7...@TK2MSFTNGP05.phx.gbl,

[Erland Sommarskog]

Michael MacGregor (macnokn...@noemailspam.com) writes:
> I'm sure I asked this question on here before and pretty sure I got a
> very useful answer to my question, however, being the dufus I am I
> didn't save the answer and have long since forgotten what it was. So
> here I am again, encamped upon your floor to ask once more what is the
> Associated ObjectaID that gets displayed in the deadlock graph because
> it sure doesn't seem to be an object_id from sys.objects?

Personally I have found little value of the deadlock graph in Profiler.
You get more information if you enable traceflag 1222.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Michael MacGregor]

Thanks Aaron.

MTM

[Michael MacGregor]

Thanks, but I wasn't asking about the HoBt ID, I was asking about the
associated objid that appears in the Resource Node box. According to that
article by Brad McGehee you gave me a link to it's "...the object ID of the
table associated with this index." This would appear to be incorrect as that
ID most definitely does not exist in the sys.objects table.

MTM

[Michael MacGregor]

Where would be the best place to find detailed information about that
traceflag?

MTM

[Aaron Bertrand [SQL Server MVP]]

http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx

On 6/16/09 11:09 AM, in article #mF3mRp7...@TK2MSFTNGP06.phx.gbl,

[Aaron Bertrand [SQL Server MVP]]

Yes I didn't read the article verbatim. But I believe I answered your
question already... The "object ID" is actually the HoBt ID, which (at least
in current versions 2005/2008) corresponds to partition_id in
sys.partitions...

On 6/16/09 11:13 AM, in article eZNn8Tp7...@TK2MSFTNGP04.phx.gbl,

[Michael MacGregor]

The HoBtID and the associated objid are not the same number.

MTM

[Michael MacGregor]

Scratch that too. I think my eyes are going squirrelly from looking at too
many really long numbers.

MTM

[Michael MacGregor]

Using 2005. Neither query you provided return any information at all.

MTM

[Michael MacGregor]

Ignore that. Looking in the wrong database.

[Aaron Bertrand [SQL Server MVP]]

That's why I put "ObjectID" in quotes, as earlier you claimed the ObjectID
was 72057647487385600, which was clearly the HoBt ID (partition_id).

On 6/16/09 3:16 PM, in article #kyCubr7...@TK2MSFTNGP04.phx.gbl,

[Michael MacGregor]

The number provided is given as both the HoBtID and the associated objid,
but I took it from the associated objid originally.

MTM

[Michael MacGregor]

Ok, I have a different resource node set of data for a Page Lock that has
the same associated objid number along with DB ID, File ID and Page ID. What
would the associated objid be in this case?

MTM

[Aaron Bertrand [SQL Server MVP]]

What does the following yield:

DBCC PAGE(DB ID, File ID, Page ID) WITH TABLERESULTS;

?

Hint: you should get a row with the following column values:

ParentObject = "PAGE HEADER:"
Field = "Metadata: ObjectId"
VALUE = <the real object_id>

On 6/20/09 7:40 PM, in article OaWGvBg8...@TK2MSFTNGP02.phx.gbl,