3

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?

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.

  • As well as defragmenting the indexes, the idea of this maintenance was to free up space. We have an archiving policy on the table, however since the PK is a uniqueidentifier column the indexes do not recover the disk space automatically. Simply throwing more disk space at it isn't an answer since we operate on expensive high-performance managed SAN disk, which means additional monthly overheads. Ola's scripts assume infinite space, as I requested, but would my method of creating space within the DB file not be considered a good approach? – aaroncatlin Jul 30 '14 at 14:50
0

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

  • Sorry, I should have mentioned that we will be taking a full backup following the maintenance anyway, so the point in time recovery will be able to continue. Thanks for the notes on recovery model, though, I will use the Bulk-Logged option instead of Simple. – aaroncatlin Jul 30 '14 at 14:47
  • I guess you are mistaken by point in time technet.microsoft.com/en-us/library/ms179451(v=sql.105).aspx. OK that you are taking backup but what if database fails after index rebuild or during index rebuild. I am assuming you are doing ONLINE index rebuild so database would be available. You woould loose data or precisey small data change. I am just diverting your attention to scenario. If its ok with you go ahead. – Shanky Jul 30 '14 at 14:52
  • Unfortunately we do not have Enterprise edition so we cannot rebuild the index ONLINE. We have a full backup and transaction log backups are taken every 15 minutes, so these will be available up until just before the maintenance begins. – aaroncatlin Jul 30 '14 at 14:56
  • Ok thanks, how about first identifying fragmanted index list then dividing the list into many chunks and then running each separately. This would avoid space issue and more logs being generated. Seems reasonable ? – Shanky Jul 30 '14 at 15:20
  • The other indexes that need rebuilding are 11 GB each so again there is insufficient space. – aaroncatlin Jul 30 '14 at 15:55

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.