December 10, 2010 at 11:50 am
I am doing some deadlock investigating on SQL2000 SP4 and both locks involved are page locks and the only info I have on the SQL run is both commands were sp_executesql;1, so I am a bit stuck.
I am trying to match the pages listed in the deadlock graph to an object but cannot find a way to do this. Using DBCC PAGE the value for m_objid returns no results from sysobjects or sysindexes. DBCC IND will give me pages in a table but that would be like finding a needle in a haystack as I have many tables and the db is 6GB.
Am I missing something?
DBCC PAGE results
AGE: (1:64174)
---------------
BUFFER:
-------
BUF @0x015B00C0
---------------
bpage = 0x6EB76000 bhash = 0x00000000 bpageno = (1:64174)
bdbid = 5 breferences = 1 bstat = 0x9
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x6EB76000
----------------
m_pageId = (1:64174) m_headerVersion = 1 m_type = 3
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId = 1237579447 m_indexId = 255 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 0 m_slotCnt = 1
m_freeCnt = 0 m_freeData = 8190 m_reservedCnt = 0
m_lsn = (18681:1597:5) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 1
Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:56616) = 0x44 ALLOCATED 100_PCT_FULL DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED
---------------------------------------------------------------------
December 10, 2010 at 12:58 pm
The number given as m_objId should match to a table in sysobjects. The index id of 255 means this is a LOB page.
Sure you're in the right database? Easy mistake to make since DBCC page takes a parameter for the database id
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2010 at 4:41 pm
thankyou Gail for confirming the value I should be checking..
I have gone back and revisited it and got a hit on the objid this time. I don't have the original session but I think my error was not being in master when I ran dbcc page, would that have done it? I certainly got a different value for objid.
all the deadlocks pointed to the same table, and as it happened a simple updatestats fixed it.
The table has 9 indexes (inc. a cluster), one of the non-clustered is on the same column as the clustered index, and a single column NC is on the same column as the leading edge in a composite NC, so I will look at removing those.
---------------------------------------------------------------------
December 10, 2010 at 10:43 pm
george sibbald (12/10/2010)
I have gone back and revisited it and got a hit on the objid this time. I don't have the original session but I think my error was not being in master when I ran dbcc page, would that have done it? I certainly got a different value for objid.
DBCC Page can be run from any database. The first three params are database ID, file ID and Page No. Hence 7, 1, 27152 (as an example) refers to the same page no matter which database it's run from. The same, of course, cannot be said for querying sysobjects unless 3-part naming is used
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 11, 2010 at 12:06 pm
<sigh>. I know what I did. I was doing the investigations on the failover server, the database has a different dbid there!
---------------------------------------------------------------------
December 11, 2010 at 12:11 pm
🙂
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply