Alter index is hanging

  • Using following command and it's hanging for ever. How to troubleshoot this. What else need to check if there is any issue.

    ALTER INDEX ALL ON DBO.invoice

    REBUILD WITH (MAXDOP = 8,

    FILLFACTOR = 95,

    SORT_IN_TEMPDB = ON,

    STATISTICS_NORECOMPUTE = ON)

    status : suspended

    wait_type : cxpacket

    requested_memory_kb : 6656

    granted memory kb : 6656

    requred memory kb: 4096

    query cost : 8053.32795674893

    timeout sec : 86400

  • cxpacket is a parallel skew. Drop the maxdop down and see if it helps

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sure, I will try this saturday and see how it goes. Do you think, I should drop to maxdop to 6 or 1.

    Thanks for your help.

    by the way, I checked your web site and it's great.

  • 1

    ... and is TempDB large enough for the task and is auto-shrink on TempDB turned off?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • balbirsinghsodhi (11/5/2008)


    Do you think, I should drop to maxdop to 6 or 1.

    Try 4 and see how it goes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply