Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106

    SQL Server 2000 spids blocking themselves

    Hey guys,

    I've recently noticed some strange behaviour with sql server 2000 spid's blocking themselves. The spid will appear to be blocked for short periods of time, and then the block will disappear. I'm not sure how this could occur. It started appearing around the same time as I applied SP4.

    If anyone could provide any insight into this, it would be greatly appreciated.

    Cheers,
    -Kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  2. #2
    Join Date
    May 2002
    Posts
    299
    this can happen on a multi-cpu server. basically, the engine tries to create multi-thread to do work. they can step on one another.

    you can force the work to be done on 1 cpu by using option(maxdop 1)
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    thanks oj_n,

    I'm guessing this hadn't appeared earlier because the load wasn't as great ? Or maybe that there is some new query that's doing some parallel processing. If that's the case, there can't be much done about it.

    Thanks again oj_n,
    -Kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  4. #4
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    Hey oj_n,

    It just occured to me that I did indeed have parallelism in SQL server turned off and still I encounter this situation. I don't know what else to suspect now that Parallelism is out of the question. Is it possible that it's a worker thread thing ?

    Thanks,
    -Kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,536
    The "self-blocking" spids is new in SP4. These are not truly blocking themselves, but are waiting for a latch. You should be able to confirm this, by

    Code:
    select spid, blocked, waittype
    from master..sysprocesses
    where spid = blocked
    Then looking up the waittype in this article:

    http://support.microsoft.com/?kbid=822101

  6. #6
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    Thanks MCrowley,

    is this a bad thing ? What are the implications of a spid waiting on memory ? It doesn't currently seem to hurt our performance, but I'm wondering if it will in the future...

    -Kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,536
    No difference, other than the fact you can see it happen, more easily, now. Microsoft probably could have come up with a better way of displaying this, but this is what we have.

    EDIT: I should point out this waiting was going on for SPIDs before SP4. It just was not displayed in this fashion.

  8. #8
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    Thanks MCrowley,

    your insight is appreciated.

    -Kilka
    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •