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:
- Set the DB Recovery mode to Simple (to avoid growing the Log file too large)
- Drop the non-clustered indexes
- Rebuild the PK
- Recreate the non-clustered indexes
- 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?