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

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

This may be a useful primer also...

http://is.gd/12HnD


On 6/15/09 1:17 PM, in article #U2s00d7JHA.3***@TK2MSFTNGP05.phx.gbl,
Show quoteHide quote

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 #NgSkqd7JHA.***@TK2MSFTNGP03.phx.gbl,
Show quoteHide quote

Thanks Aaron.

MTM

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

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 eZNn8Tp7JHA.4***@TK2MSFTNGP04.phx.gbl,
Show quoteHide quote

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

MTM

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

MTM

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 #kyCubr7JHA.5***@TK2MSFTNGP04.phx.gbl,
Show quoteHide quote

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

MTM

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

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 OaWGvBg8JHA.1***@TK2MSFTNGP02.phx.gbl,
Show quoteHide quote

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

MTM

Ignore that. Looking in the wrong database.

Michael MacGregor (macnoknifespam@noemailspam.com) writes:
Personally I have found little value of the deadlock graph in Profiler.
You get more information if you enable traceflag 1222.

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

MTM

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




On 6/16/09 11:09 AM, in article #mF3mRp7JHA.1***@TK2MSFTNGP06.phx.gbl,
Show quoteHide quote

Enterprise Manager displays nvarchar as <binary>
How to manage the transaction log
VS 2005 (BIDS) deploys the wrong data source connection string
BCP Error 08001, Nativeerror 2
Appending Data via BCP
No management Studio
SQL Server 2005 Error
2008 SSMS object explorer
script to cliboard in microsoft SQL server management studio
Backup database