I am wondering what is the best approach for dropping and creating a new clustered index on a table within SQL Server 2008. My primary concern is the length of time it will take to perform this operation, as it is estimated to be greater than our maintenance window.

Background The table is an 8 million row table, with 250 columns and 15 non-clustered indexes, 8 of which are larger covering indexes holding about 15 to 30 columns each, these are mainly used near month ends on reporting.

I have already reviewed unused and duplicate indexes and removed them.
The table has numerous websites, web services and polling processes selecting, inserting and updating records at a very high rate. Things slowdown in the late hours but not fully.

The Index The current clustered index is on 6 columns 4 of which are varchars and 3 of which are flags that are updated throughout the life of the record. A record goes through about 18 states before it can be considered complete, meaning those flags are changing rapidly and constantly.
Everything about this clustered index is wrong, the combination is NOT unique, it’s on dynamic columns and it’s large.

I want to DROP this existing index and replace it with a new clustered index that is just the Id column, a non-identity int that is unique. Note, this is not the Primary Key of the table, the PK has three columns.

In a lesser used environment dropping and creating the new index took just over 3 hours to do on a 2012 SQL Server. I used the ONLINE mode which from my understanding does cause the process to run slower, but its required for any process that requires the table.

The questions - Since the time to perform this action will be lengthy, I am trying to identify the proper steps to keep the length of time as short as possible.

  1. Should I drop all the non-clustered indexes and create them after the process is done?
  2. Should I copy the table perform the actions and then replace the existing table with the copy? My hesitation is we have replication and data backups etc. that need to be considered.
  3. Should I run with ONLINE mode off, would that make a sizable difference in the length of time?
  4. What other steps should I do to prepare for this? I am identifying records we can remove and any duplicate or unused Non clustered indexes.

We do have maintenance windows on our DB but they are usually shorter then this process takes and do not happen often so I am trying to determine the best course of action to take.

Edit: Also is there any way I can monitor this process to know the progress, beyond just the "Executing Query..." in the status bar.

  • Since many of the non-clustered indexes aren't being used now, you could consider dropping those, then perhaps your rebuild will run much quicker. You could add one or two non-clustered indexes each night until the reporting period starts. Your #2 isn't going to accomplish much at all, except you have additional I/O involved with making the initial copy. ONLINE = ON means you don't have to wait for a maintenance window. – Aaron Bertrand Jun 22 '15 at 15:39
  • @AaronBertrand, I have never used the ONLINE option before on a production db, or on a process as "expensive" as this. Are you saying the performance impact is almost negligible? – treeNinja Jun 22 '15 at 16:10
  • I'm not saying the performance impact is negligible, because there way too many "it depends" factors for that. I'm saying that your users can continue using the original index while the new one is being built, so they won't be blocked by the rebuild operation. – Aaron Bertrand Jun 22 '15 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.