SQL Server Performance

Interpreting Waitresource on Sysprocesses

Discussion in 'Performance Tuning for DBAs' started by rerichards, Nov 9, 2006.

  1. rerichards New Member

    I have a blocking issue, running SQL 2000, SP4, on Windows 2003.<br /><br />The SPID at the head of the blocking chain = 226. SPID 226 has the following<br />from SYSPROCESSES:<br />Open_Tran = 2<br />WaitResource = 40:1:41356<br />WaitType = 0x0000<br />cmd = INSERT<br />LastWaitType = PAGELATCH_SH<br /><br />The SPID getting blocked = 394. SPID 394 has the following from SYSPROCESSES:<br />Open_Tran = 2<br />WaitResource = TAB: 40:514100872<br />WaitType = 0x0008<br />cmd = INSERT<br />LastWaitType = LCK_M_IX<br /><br />I have three questions. From the above, do I interpret:<br />#1 - The WaitResource on SPID 226 to be [DBID:INDEXID<img src='/community/emoticons/emotion-4.gif' alt=':p' />AGEID]?<br />#2 - The WaitResource on SPID 394 to be [DBID<img src='/community/emoticons/emotion-3.gif' alt=':O' />BJECTID]?<br />#3 - If the WaitResource on SPID 394 is [DBID<img src='/community/emoticons/emotion-3.gif' alt=':O' />BJECTID], is the object in this<br />case the locked object?<br />
  2. satya Moderator

    http://www.sqldev.net/misc/waittypes.htm is best resource to interpret such information.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. rerichards New Member

    Sorry Satya, but I feel a bit insulted. I have already been to that link, and that link does not even begin to address the three questions I presented. I have already Googled and searched several forums like this, to try to answer my three questions posted here. I have found no site that outright addresses my three questions, but many that are close, but not close enough to give me the confidence I need to move forward.


    quote:Originally posted by satya

    http://www.sqldev.net/misc/waittypes.htm is best resource to interpret such information.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  4. rerichards New Member

    OK. I have answered #1 and #2 on my own, but am still seeking an answer on #3. Any help in answering #3 would be appreciated.<br /><br />#1<br />DBCC TraceOn (3604)<br />GO<br />DBCC PAGE (40, 1, 41356)<br />GO<br />SELECT OBJECT_NAME(&lt;m_objId value&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />#2<br />The WaitResource on SPID 394 is [DBID<img src='/community/emoticons/emotion-3.gif' alt=':O' />BJECTID], therefore :<br />SELECT OBJECT_NAME(OBJECT_ID)<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by rerichards</i><br /><br />I have a blocking issue, running SQL 2000, SP4, on Windows 2003.<br /><br />The SPID at the head of the blocking chain = 226. SPID 226 has the following<br />from SYSPROCESSES:<br />Open_Tran = 2<br />WaitResource = 40:1:41356<br />WaitType = 0x0000<br />cmd = INSERT<br />LastWaitType = PAGELATCH_SH<br /><br />The SPID getting blocked = 394. SPID 394 has the following from SYSPROCESSES:<br />Open_Tran = 2<br />WaitResource = TAB: 40:514100872<br />WaitType = 0x0008<br />cmd = INSERT<br />LastWaitType = LCK_M_IX<br /><br />I have three questions. From the above, do I interpret:<br />#1 - The WaitResource on SPID 226 to be [DBID:INDEXID<img src='/community/emoticons/emotion-4.gif' alt=':p' />AGEID]?<br />#2 - The WaitResource on SPID 394 to be [DBID<img src='/community/emoticons/emotion-3.gif' alt=':O' />BJECTID]?<br />#3 - If the WaitResource on SPID 394 is [DBID<img src='/community/emoticons/emotion-3.gif' alt=':O' />BJECTID], is the object in this<br />case the locked object?<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  5. joechang New Member

    i have never found trying to interpret the wait resources to be particularly helpful

    considering that both sql are inserts, and its just blocking, and not deadlocks
    then the chances are that your disks are slow, if the blocking is excessive
    also assuming the query plans are efficient
  6. rerichards New Member

    While I appreciate the info, I am still not getting question #3 addressed, unless you are in a round about way saying definitively that the object_id in question #3 is not the locked object. I just do not know how you are answering my question.


    quote:Originally posted by joechang

    i have never found trying to interpret the wait resources to be particularly helpful

    considering that both sql are inserts, and its just blocking, and not deadlocks
    then the chances are that your disks are slow, if the blocking is excessive
    also assuming the query plans are efficient
  7. satya Moderator

    Richard
    That was not my intention, I feel that site there is no other 'useful' information available in this regard.

    I will post this query in MVP newsgroups for an update.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  8. westermanm New Member

    Richard,


    That's how I would interpet it, SPID 394 is waiting to place an Intent Exclusive lock on Tabled ID 514100872 (from the waittype and lastwaittype data). However itlooks odd though as 226 doesn't look like it's waiting to me?

    Mark

Share This Page