How to read sp_lock info? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to read sp_lock info?

I’m reading the result from sp_lock when I’m executing a delete query. There is a lot of rows (over 100,000 rows) which have ObjId = 0 and IndId = 0 and dbid = user database and Type = PAG or EXT. What are these rows? Are there any special hint to control these locks? Note: I’m the only one will access the user database during that time. If needed, I can be the only one accessing the server.
Read: Displaying Locking Information in BOL to find each kind of lock.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Hi Luis, I know the Type PAG or EXT but I don’t know what ObjId = 0 and IndId = 0 are.

spid dbid ObjId IndId Type Resource Mode Status
1 1 0 0 DB S GRANT That’s information from sp_lock.
ObjId, IndId=0 means DB (not TAB, PAG…) lock. The Status column shows if the lock has been obtained (GRANT) Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Hi Luis, This is the sp_lock result: spid dbid ObjId IndId Type Resource Mode Status
51 9 0 0 PAG 3:3441716 X GRANT
51 9 0 0 EXT 3:3479416 X GRANT
51 9 574781255 0 TAB X GRANT
I understand the last entry but not the 1st and 2nd since the ObjIds are 0. What objects are being locked?
PAG<br />Page number. The page is identified by a fileid<img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />age combination, where fileid is the fileid in the sysfiles table, and page is the logical page number within that file.<br /><br />EXT<br />First page number in the extent being locked. The page is identified by a fileid<img src=’/community/emoticons/emotion-4.gif’ alt=’:p‘ />age combination.<br /><br />I don’t know why ObjId = 0, with PAG or EXT Type.<br />I suggest to whait for others oppinions.<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br />
I find something about ObjId = 0 in Inside Microsoft SQL Server 7.0 and in the Inside SQL Server 2000 books: http://archive.devx.com/upload/free/bkchapters/delaney/delaneych13.asp search for "ObjId is 0" to find the paragraph But the scenario mentioned is during an insert and that makes sense. For my case, it is during a delete and it does not make sense.
I expect this is related to page deallocation during a delete. It may be the server locking the pages while it’s removing them from the table, and it’s got them locked under objid 0 and indid 0 because they no longer belong to the table. Tom Pullen
DBA, Oxfam GB
Tom, Your analysis seems to be reasonable. I don’t have the knowledge what actually happens in term of space allocation during insert/delete/update.

]]>