question

vijayakarsh avatar image
vijayakarsh asked

CXPACKET with High Waittime

I'm creating Non clustered Index on on table which was recently loaded and the rowcount is close to 600 million. The MAXDOP is set to 8 on the server and the SQL server instance 2005 SP1. there are no other index on table and when I'm trying to create a Index, the time taken to create the index is more than 20 minutes. There are no other users on the database running queries which could affect the index creation, on activity monitor i see 8 CXPACKET Wait types (if i'm not wrong this is because MAXDOP set to 8), however the wait time on these seems to be huge. My question would be that would a create index statement take more than 20 minutes on a table with 600 million rows, is this common? If not how can I work on decreasing the index creation time. Should I be creating the index with a fillfactor of 100.
maxdop
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vijayakarsh avatar image vijayakarsh commented ·
I'm creating a composite non clustered index with 5 column, well the delay in creating time does not seem to be only with composite index but also when i try creating a clustered index on a int column it takes more time than creating a composite index. One more interesting observation is when there are users on the system and when I try indexing the columns, different threads are assigned to my query and index creates in 20 mins but when there are no users the query status is Runnung( as expected) but the query index creation time doubles or sometime triples, is this because entire load is being handled by a single core. I should be able to change this behaviour by using the MAXDOP hint on my query right?
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Whilst the time seems high, it's not that surprising. How big is the index? In terms of width. A 600 million row index of a single int field will take less time than a 600 million row index of 10 a column, mixed datatype composite. The `WITH SORT_IN_TEMPDB = ON` option may hep if the tempdb database is on different disks to your user database.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.