I have a large table, the row count of the table is more than 3 billion, the data space for this table is about 120 GB.
And Intel Xeon CPU E5645 @2.4GHz(2 processors), 24 CPUs, 64G memory, 64bit windows server 2008 R2 enterprise.
I run
create unique clustered index MyTable_IXC on tblFactFoo(barKey) on [PRIMARY]
But it took more than 6 hours (actually, it reported an error of duplicated key after 6 hours).
When running it, the cpu was less than 10%, and disk IO was less than 20M/s, normally about 15M/s, I wonder how to improve the performance of creating a clustered index with such powerful hardware.
Best Answer
You'll need to look at the wait type when the query is running. Odds are you need faster disks as building an index on a table that large is going to cause MASSIVE amounts of reads and writes.
In a nutshell you'll need to read the 120 Gig table, sorting it based on the clustering key (which is going to cause a ton of spill to tempdb writing probably 100 Gigs to tempdb), then write the clustered index to the database which causes 120 Gigs of writes.
On top of this any non-clustered indexes on the table will need to be rebuilt at the same time so those all need to be read and rewritten as well (along with all the memory spill that goes with rebuilding the non-clustered indexes).
You can speed things up by disabling the non-clusterd indexes, then manually building them after the clustered index has been rebuilt. You'll want to make sure that end users aren't able to touch the system while the non-clustered indexes are being built as their query performances will be awful until the non-clustered indexes are created.
Assuming Enterprise Edition you can create the commonly used indexes then let the users back in, then create the other indexes online so that users an work.
No matter what you'll be looking at a REALLY long maintenance window where you'll be slamming the disks as hard as they can go.