Alter index is hanging

Question

Alter index is hanging

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
November 5, 2008 at 10:01 am

#195665

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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