Reindexing Clustered Primary Key

Question

We need to reindex a Clustered Primary Key on quite a large table. Here is the definition of the table:

• Data Size: 22GB
• Index Size: 34GB
• Total Non-Clustered Indexes: 3
• Row Count: 54 Million
• Data File Free Space: Minimal
• Drive Free Space: 3GB

The indexes are all over 90% fragmented.

I know that reindexing the PK will lock the table, so I was wondering what best practice for this operation is? Since there is insufficient free space in the data file or disk drive to rebuild the index, I wrote a script which would:

1. Set the DB Recovery mode to Simple (to avoid growing the Log file too large)
2. Drop the non-clustered indexes
3. Rebuild the PK
4. Recreate the non-clustered indexes
5. Set the DB Recovery mode back to Full

I did a dummy run on a backup of the database on another server, which took 32 minutes in total, which is acceptable for a maintenance window for us.

The benefits I can see of dropping the non-clustered indexes are:

• The DB file does not need to autogrow to rebuild the PK as space would have been created when the indexes are dropped
• We will not need to shrink the DB file again (and cause additional fragmentation) following the maintenance
• The non-clustered indexes will also be defragmented when they are rebuilt.

This script suits our needs for now, but what is considered best practice usually for a table of this size assuming we had infinite available space?

Answer 1

I know that reindexing the PK will lock the table, so I was wondering what best practice for this operation is?

Well having some DB file size/growth and free disk space monitoring would be good so this situation never happens. As you are in this situation I think the plan you have outlined is your only choice until you get more disk space and you really need more disk space.

This script suits our needs for now, but what is considered best practice usually for a table of this size assuming we had infinite available space?

Regular index maintenance would be nice. Try looking at Ola Hallengren's maintenance scripts here to solve that. These scripts provide an almost fully automated methodology for maintenance of your SQL Server databases.

Answer 2

Set the DB Recovery mode to Simple (to avoid growing the Log file too large

This is not totally correct. Simple recovery is almost same as full recovery just the log truncations are taken care by Database engine in case of simple recovery. In simple recovery after transaction commits or log file grows 70 % of its size log truncation occurs while in full recovery you need to take log backup to truncate logs. So if you think log file would not grow in simple recovery model you are wrong it can grow and I have seen this often in Index rebuild case. This is because the operation still requires a portion of log and thus not allowing log truncation.

Are you aware that setting recovery model to simple and back to full will let you loose point in time recovery. If its is a critcal database with SLA set I would not advise you to use this. There is other option to decrease logging by using bulk logged recovery model but in this as well you loose point in time recovery. Index rebuild is minimally logged

Ola Hallengren solution would be good one. You can also choose option SORT_IN_TEMPDB to use tempdb as storage from intermedite index which is created thus avoiding space issue on drive on which index resides.

Your approach might work one time but is surely not suitable for long run