1

from a blocked process report I get a wait resource as follows "OBJECT: 6:1647722064:12". Now I am trying to decode this. However I haven't found helpful information on this on the web or here in Stack Overflow.

The first two parts are possible to guess:

  • 6 should be the database id
  • 1647722064 probably is the object id

But what does the number 12 refer to? There is no index with id 12 and it is not a partitioned table either?

Any hints how to decode this wait information correctly?

Thanks a lot in advance

Martin

Martin Guth
  • 255
  • 4
  • 14

1 Answers1

1

That's the Page Number for the file. The query below would be a good start on how to get an idea of this. 

DBCC PAGE (@DatabaseID, @File_No, @Page_No) WITH TABLERESULTS

In your instance use this one

DBCC PAGE (6, 1647722064, 12) WITH TABLERESULTS

Page locks is locking a single 8k page of data within the database (probably a few rows) whereas an Object Lock will be locking an entire object (e.g. a table). Here's a link to an excellent explanation that may help with different lock types;

What are row, page and table locks? And when they are acquired?

When you pass your DBCC PAGE command one of the lines is Metadata: ObjectId which will tell you which parent object this page belongs to. This ObjectId can be used when you query sys.objects to see the parent object.

Community
  • 1
  • 1
Rich Benner
  • 7,873
  • 9
  • 33
  • 39
  • Thanks Rich! Interesting! What would be the difference between a PAGE lock (e.g. PAGE: 6:3:70133) and an object lock then? – Martin Guth Nov 15 '16 at 09:46
  • @MartinGuth I've edited my answer because it was a bit much for a comment, it should help explain the difference a bit better than re-writing it all out here. – Rich Benner Nov 15 '16 at 09:48
  • Well I am unable to get further insight as the page has already gone. After further looking I have seen that all these blocking ocurred during an index rebuild which makes perfect sense (unfortunately have just SQL 2008 Standard...so no online rebuilding :-/ ) – Martin Guth Nov 15 '16 at 09:58
  • Yeah, that'll do it. Do you have any overnight maintenance on index rebuilds running overnight? If not, check out Ola Hallengren's index scripts. Running these overnight should help them rebuilding automatically during the day. – Rich Benner Nov 15 '16 at 10:00
  • Thanks for the advice. Ola Hallengren's scripts are right in place and working like a charm nightly. The (table) rebuild however resulted in a change of the XSchema for an XML column which had to run regardless of the current fragmentation of that index. – Martin Guth Nov 15 '16 at 10:04
  • Yeah, it's unavoidable sometimes. I had this once and implemented Snapshot Isolation to help with blocking but that's got it's downsides too. – Rich Benner Nov 15 '16 at 10:16