Interpreting Waitresource on Sysprocesses

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='' />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 />

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.

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='' />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">

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

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

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