May 16, 2012 at 11:03 am
I'm doing some deadlock investigations where the locks are page locks. I have the table name involved from the deadlock trace but dbcc page returns metadata : ObjectID = 0 and IndexID = -1, I was wondering if anyone could cast some light on what these values mean?
m_type = 1 so its a data page.
cheers
george
---------------------------------------------------------------------
May 16, 2012 at 11:05 am
Please post the DBCC page output, just the header's enough.
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
May 17, 2012 at 3:17 am
morning gail, thanks. the database has been reindexed since the deadlock trace captured the information so I wonder if the page is still used by the same object, but anyway my curiosity is piqued as to the meaning of these values. Here is the page header:
PAGE HEADER:
Page @0x00000000E7E44000
m_pageId = (3:55182) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 6271 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594448904192 Metadata: PartitionId = 0
Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (3:57153)
m_nextPage = (0:0) pminlen = 61 m_slotCnt = 1
m_freeCnt = 8017 m_freeData = 2098 m_reservedCnt = 0
m_lsn = (726488:616:195) m_xactReserved = 0 m_xdesId = (0:540343325)
m_ghostRecCnt = 1 m_tornBits = 0
Allocation Status
GAM (3:2) = NOT ALLOCATED SGAM (3:3) = NOT ALLOCATED PFS (3:48528) = 0x8 0_PCT_FULL
DIFF (3:6) = NOT CHANGED ML (3:7) = NOT MIN_LOGGED
---------------------------------------------------------------------
May 17, 2012 at 3:34 am
Here's the reason:
george sibbald (5/17/2012)
Allocation StatusGAM (3:2) = NOT ALLOCATED SGAM (3:3) = NOT ALLOCATED PFS (3:48528) = 0x8 0_PCT_FULL
DIFF (3:6) = NOT CHANGED ML (3:7) = NOT MIN_LOGGED
The page is unallocated.
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
May 17, 2012 at 4:11 am
thanks for the prompt response Gail.
Can I impose and ask from your experience if there is a likeliest root cause when the two processes involved are both dynamic SQL running the same update statement (but presumably different parameter values). No problem if thats to open a question to answer.
---------------------------------------------------------------------
May 17, 2012 at 4:31 am
Not without seeing the deadlock graph
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