Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696

    Unanswered: Status of Suspended SQL2005x64

    Can anyone tell me why when rebuilding an index, the process gets split to 16 parallel tasks, but most of them have a status of SUSPENDED at any given period when I run sp_who2. I understand these may be due to i/o waits, but is there anyway to resolve this issue by tweaking settings (other than disabling parallel processes) ?

    all user mdfs, ldfs and tempdb are on the same SAN partitions, but they are on separate logical drives.

    For something that took an hour to run on a crappy SQL2000 system has been running for 2+ hrs on 4XQuadCore CPU, 64GB RAM, 15000RPM RAID 1+0 SAN, etc.

    SP2 had been applied.

    Thanks
    Last edited by PMASchmed; 06-11-08 at 16:02.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    First, what command did you issue to rebuild the index? You can definitly use maxdop in index rebuild option, but that would make the rebuild process even longer. 2K vs. 2K5 is in excessive usage of tempdb, even if you don't specify sort_in_tempdb.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Got lazy and did it via SSMS, I'll try it the right way with alter index...rebuild. Did notice though there were consistently 5 processes of the 16 that were not suspended when doing sp_who2, maybe I'll try specifying with a MAXDOP of 5.

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    with MAXDOP=5 I just get 5 suspended processes, instead of 16, granted not all the time.

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Rebuilt the index with alter index.., 3 minutes. Whatever, I will go that route in the future.

  6. #6
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    I am a little curious why you have separate logical drives on the same SAN partition? Isn't the idea of splitting tempdb etc. onto it's own drive to cut down on disk head thrashing?

  7. #7
    Join Date
    Sep 2005
    Posts
    161
    We all get lazy sometimes. Last night I drank my beer straight from the bottle instead of pouring it into a glass first.

    Quote Originally Posted by PMASchmed
    Got lazy and did it via SSMS, I'll try it the right way with alter index...rebuild. Did notice though there were consistently 5 processes of the 16 that were not suspended when doing sp_who2, maybe I'll try specifying with a MAXDOP of 5.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Now that's lazy, cascred! Even I don't do that! Drinking beer straight from the bottle? That's like grilling burger patties without unwrapping them!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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