Speed up large clustered index creation?

Question

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.

Answer 1

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.

Answer 2

Here are a few things to evaluate:

1. Turn on data compression: it looks like you're IO bound and have CPU to spare. Data compression might be a good trade-off here.
2. Turn SORT_IN_TEMPDB on. This can drastically improve IO patterns (more sequential IO, and less fragmentation in the final index).
3. Build the index into a fresh (presized) filegroup. A fresh filegroup is not fragmented so you can at least get rid of that problem. Use instant file initialization.

Or, load the data correctly sorted in the first place. Then you don't need to build an index at all. This has certain disadvantages but is worth considering. The best possible solution would be to load into a partitioned clustered index using the ORDER hint for bulk-loading. One bulk import stream per partition and one partition per CPU or per physical disk.

Answer 3

How I personally would do this:

• Rename TableA into TableA_Old and create a view named TableA. That way your users can keep on working.
• Create a new TableB, create your indexes on it and start copying data from TableA_Old into TableB (or first copy data and then create indexes; at least I would create the clustered index first and all other indexes after data was copied).
• After all this, drop view TableA, rename TableB -> TableA and drop TableA_Old. Make sure you got the last records that were added in TableA_Old. Disadvantage: you will need more diskspace, but downtime will be less.

Answer 4

Each time when you create/recreate cluster index, server starts to order pages, and this is quite resource-demanding procedure. Your table is a large one. I'd advise you to divide your table in several smaller tables (i.e. to perform data normalization), if it is possible. Or you can create an empty copy of this table, add cluster index on empty table, import all data from your main table and after that delete the main table.

I mean something like this -

CREATE TABLE dbo.tblFactFoo_New
(
      barKey INT NOT NULL
    , ...
)

CREATE UNIQUE CLUSTERED INDEX tblFactFoo_IXC ON tblFactFoo_New(barKey) ON [PRIMARY]

INSERT INTO dbo.tblFactFoo_New(barKey, ...)
SELECT barKey, ... 
FROM dbo.tblFactFoo
GROUP BY barKey, ... -- without duplicates
ORDER BY barKey

DROP TABLE dbo.tblFactFoo

sp_rename 'dbo.tblFactFoo_New', 'dbo.tblFactFoo'

Answer 5

To increase the speed of any SQL command you should have a properly set up database, thus I do hope that your database is stored on a different disk and that the master and tempdb are on their own disk.

That being said there are several factors that affect index creation: if the table is sorted already, and since it looks like you are building this on a HEAP table I would say it's not sorted, and the other component is the column type on which you are creating the index on. The information contained in a clustered index is limited by the amount of columns or the byte size of the columns (whichever comes first) and as such some columns aren't as good candidates for being clustered indexes.

Since you are creating a unique index on a heap table you should clean it up firs so that you don't have duplicate values. This will avoid you having to build the index again.

So before you run the index creation query run this first

SELECT 
   barKey
FROM
(
    SELECT 
        barKey, 
        COUNT(barKey) AS NoOfDuplicates 
    FROM 
        dbo.tblFactFoo WITH(NOLOCK)
    GROUP BY 
        barKey
)
WHERE
NoOfDuplicates > 1;

After you run this and handle the duplicate records you can run the following, please note that this will use extra disk space so you'll need at least as much space as the size of that table.

CREATE UNIQUE CLUSTERED INDEX IXC_MyTable ON dbo.tblFactFoo(barKey) WITH(SORT_IN_TEMPDB)
GO

This will force the sorting (which is needed when creating a index) to occur in the tempdb database and then have it transfered back and replace your data.

An alternative would be to create a duplicate table with the same name, clumns etc, add the clustered key on it, before you add any records and then execute this command:

MERGE INTO dbo.tblFactFoo AS source
USING dbo.tblFactFooIndexed AS destination ON source.barKey = destination.barKey
WHEN NOT MATCHED BY source THEN
INSERT INTO destination(col1, col2, barKey etc) VALUES (source.col1, source.col2, source.barKey etc)
WHEN MATCHED BY source AND (add extra conditions here if needed) THEN
-- INSERT / UPDATE or DELETE depending on how you want to handle duplicate keys

Since this is a set operation this should, theoretically, work much faster since the SQL server operates on sets faster then it operates on rows. Once you're done, drop the first table and rename the second table.

Should you need more help with the MERGE command here is the link to it on MSDN: http://msdn.microsoft.com/en-us/library/bb510625.aspx