SQL Server Performance

How to read sp_lock info?

Discussion in 'General DBA Questions' started by pcsql, Jun 9, 2004.

  1. pcsql New Member

    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.
  2. Luis Martin Moderator

    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.

  3. pcsql New Member

    Hi Luis,

    I know the Type PAG or EXT but I don't know what ObjId = 0 and IndId = 0 are.


  4. Luis Martin Moderator

    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.

  5. pcsql New Member

    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?
  6. Luis Martin Moderator

    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 />
  7. pcsql New Member

    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.





  8. thomas New Member

    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
  9. pcsql New Member

    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.

Share This Page