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.
Select db_name(9)
GO
use <dbname_from_previous_query>
GO
Select object_name(946818435)
Like this:
Like Loading...
Related
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.
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.
SQLZealots 