Why does the wait resource not exist?

DLapointe 2015-12-22 16:55:15

In my deadlock issue, the specified Wait Resource is 6:1:4812. DBID 6 makes sense, but I can't find a page 4812 (apparently nor can Sentry).
Since this deadlock issue has become a big deal (>20/hour), I can see the same resources involved, but I don't know what this one is.
DLapointe 2015-12-22 17:07:03
Found some info on another related posting.
I suspect the issue is running XML. And this instance is running 2005.
What still throws me off is how the object doesn't seem to exist yet is involved in a deadlock. Running Page (6,1,4812) returns no results to SSMS or the log.
Matak 2015-12-22 21:53:41
If you post the deadlock graph im sure someone will be able to help out.
DLapointe 2015-12-22 22:23:05
I should have supplied more information.
The question is about a block situation in the Blocking SQL tab on SQL Sentry and related to my deadlock.

The highlighted Wait Resource is what I am curious about. Very many blocks per hour and I'd love to know what resource the PAGELATCH_EX is on. There is only one table referenced in the code involved in the blocking, but I'd like to know what part of the table is involved here – and why a DBCC Page doesn't find anything about the specified page.

Perhaps this is less of a tuning question and more of a product question about SQL Sentry…

alt text

Aaron Bertrand 2015-12-28 16:18:48
Well we only report what SQL Server tells us as we collect information about the block. We don't make up page numbers. If the page doesn't exist when you try to get more information about it, is it possible that the rows on that page have been deleted and the page has been deallocated since the block happened?
DLapointe 2015-12-29 16:26:09
It's possible that the process allocated the page in memory then released it all within the processing of the transaction. As a latch lock, it may only ever exist in memory.
We recently started running this processing much more and these locks started showing up with frequency.

At this point, we are done with the surge in processing and we plan to upgrade the third part software triggering this. So it is already is no longer a current issue.

Thanks for the feedback.