(re) Clustering a Large Online Table

One of the most important performance controls you have on a relational database is the clustering sequence of your large tables. Unfortunately, it is often not considered until there is a performance issue. Clustered indexes are important on their own, but they are especially significant when you are joining multiple large tables. There are often orders of magnitude performance gains by clustering the large tables in the same sequence. However if you have a large table that needs to be online 24 hours a day, seven days a week, it can be difficult to make such a big change.

I have achieved this a few times now, on tables that are only inserted into (ie transaction tables). The only requirements are that the table is inserted into only (no updates) and that the table has a column that is inserted into in ascending sequence only (eg transactionid, datetime etc). Essentially, this is done by creating a new table, which has all the live table's records inserted into it using bulk load (SSIS package task). This makes it very efficient, but it will take a long time on a large table, so the new table won't be up to date. The trick is to keep low and high water marks, then you can open a transaction to copy in the last few records and rename the tables within the same transaction. In this way, at the point of committing, both tables will have the same content, and any active transactions will, at worst, wait a couple of seconds for the last insert and renaming to occur.

Here are the steps involved in the SSIS package

1. Create the new table with the clustered (and non clustered) indexes you want.
2. Create a control table to maintain high-low watermarks. Set the high water mark to the maximum ascending key value in the live table.
3. Perform a bulk load (SSIS Data Flow task) for all the live table rows up to and including the high water mark. This task might take several hours.
4. Set the low water mark to the high water mark, then re set the high water mark to the new maximum ascending key value in the live table.
5. Perform the bulk load again. This time it will just be the rows that have been inserted during our earlier bulk load. After this task, the new table will be very close, but not quite, up to date to the live table.
6. Within the scope of a transaction lock the live table, insert records above the high water mark, rename the tables (and PKs) and commit the transaction. The new table is now live.

A few notes on the process

• You will need to have sufficient space in your database to accommodate two copies of the table.
• You will need to have sufficient space in your database logs to hold log records from the start of the Main Bulk Load to the end of the Main Bulk Load. So, if the bulk load takes 4 hours, their will be a transaction open for 4 hours and a log truncation won't truncate anything within the 4 hours.
• You will need to have sufficient space in tempdb to perform the sorts for the clustered index and nonclustered indexes.
• If you have sufficient space in the database files and logs, you should set the maximum commit size on the destination to 0 (zero). Having other values will tend to slow down the load process (although less disk space is required) since the indexes will be maintained at each commit.
• It is quite likely that your large tables are partitioned and compressed. This doesn't need to make any difference to the reclustering process above. However, if the table is already partitioned, you could improve performance by looping through each partition. If your table isn't already partitioned, this process could be the opportunity to partition your table.
• Don't be tempted to use NOLOCK when reading the live table.
• Test your package before you run it in production.

There is no special code in the package I have created. The most significant task is the very last one, which is performed within the scope of a transaction. To ensure there is no opportunity of a deadlock, the task first asks for an X lock on the live table. Here is some sample SQL from each of the tasks.

Create Control table

create table ReclusteringControl( ReclusteringControlID int default 1, LowWater bigint not null default 0, HighWater bigint not null default 0, CONSTRAINT [PK_ReclusteringControl] PRIMARY KEY NONCLUSTERED (ReclusteringControlID ASC))

Insert into ReclusteringControl values(1,0,0)

update ReclusteringControl set HighWater=(select max(rid) from MyLargeTable)

Input for Main Bulk Load

select * from MyLargeTablewhere rid<=(select HighWater from ReclusteringControl)

Set New HighWatermark

update ReclusteringControl set LowWater=HighWater
update ReclusteringControl set HighWater=(select max(rid) from MyLargeTable)

Second Bulk Load (input statement)

select * from MyLargeTablewhere rid<=(select HighWater from ReclusteringControl) and rid>(select LowWater from ReclusteringControl)

Final INSERT and Rename

--set identity_insert MyLargeTable_new on

--This is required if there is an identity column on the table

GO

begin transaction

update ReclusteringControl

set LowWater=HighWater

select top 0 * from MyLargeTable with (tablockx)

update ReclusteringControl set HighWater=(select max(rid) from MyLargeTable)

insert into MyLargeTable_new --columns need to be explicitly declared if identity cols are inserted

select * from MyLargeTable where rid<=(select HighWater from ReclusteringControl) and rid>(select LowWater from ReclusteringControl);

exec sp_rename MyLargeTable,XXXX_MyLargeTable;--should delete this table

exec sp_rename MyLargeTable_new,MyLargeTable;

exec sp_rename PK_MyLargeTable, PK_MyLargeTable_XXXX

exec sp_rename PK_MyLargeTable_new, PK_MyLargeTable

commit transaction

--set identity_insert MyLargeTable off