Sql-server – Reindexing Clustered Primary Key

clustered-primary-keyindexsql server

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?

Best Answer

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.

Related Question