Recently, we had a performance issue in one of our testing environment and while we are troubleshooting the issue with Live Monitoring Query
, we identified a locking scenario which is the root cause for the performance problem.
Now, we know there is a locking and the wait resource is showing as PAGE: 9:1:401776. What does it mean? How do we know which object is being locked? Let us try to answer these questions.
We know there is a LCK_M_IX which means an intent exclusive modification lock happened on the object “PAGE: 9:1:401776”. If we have a page number like this, then there is an easy way to get the object information with the help of a DBCC command – DBCC PAGE
DBCC TRACEON (3604);
DBCC PAGE (9, 1, 401776, 0);
DBCC TRACEOFF (3604);
The result of DBCC PAGE provides lots of information, however, we should specifically look at PAGE HEADER section for “Metadata: ObjectId “. It would provide us the ObjectId. Once we have the object_id, it is an easy to get the object name as below. Please note, we should select the database name while executing the command otherwise, we might end up no result or wrong information.
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!