Sql-server – Enabling page compression on 1.5TB table

compressionsql serversql-server-2012

I need to come up with the best approach possible to enable page compression on a table 1500 GB in size, with 363,957,740 rows. The database size itself is 1.71 TB and holds archive data.

If I understand correctly, it needs disk space (possibly the same amount, just to be on the safer side) so that it can create a copy of the table with page compression enabled and release the space. This is in FULL recovery model so, it will be heavily logged.

I have spoken to my capacity planning resource and he agreed to give additional temporary required space for this maintenance, and take back the space once this activity is complete. Having said this, do you think the best approach to be:

  1. Take a full backup
  2. Restore the backup onto new temp drives
  3. Change the recovery model to SIMPLE
  4. Enable page compression
  5. Take a full backup after compression and restore original database
  6. Change recovery model to FULL

Also, instead of enabling page compression. After Step #3, truncate the largest table, enable page compression and then do a

SELECT * INTO ReplicaDB.dbo.ReplicaTbl

Does this enable page compression on existing indexes?

I do not have Test environment to test the above steps. Alternatively, if any better approach is available then please let me know.

The goal is to minimize the future disk space required at its current growth. We are an ERP software company and we have licensed Enterprise Edition. This table is only for archive when some checks are performed and all the data resides in this table. I have 2 indexes (1 CI, 1 NON-CI). None of the columns are VARCHAR (MAX), they are either NVARCHAR, int or date type.

Best Answer

Page compression is enabled at the table or index level. You can have an uncompressed table with page compressed indexes or a compressed table with uncompressed indexes. If you want your indexes to be page compressed you'll need to do that separately.

I get the impression that the most important consideration for this move is to be able to give back all of the temporary space that you were allocated. SELECT INTO isn't a good option for what you're trying to do. The table will be built uncompressed and without a clustered index. Building that will require a REBUILD which will grow your data files which is exactly what you want to avoid.

Here is how I would consider doing it:

  1. Take full backup of old database.
  2. Restore backup to your temporary database on the same server.
  3. Change the recovery model to simple on the temporary database.
  4. Truncate the log.
  5. Truncate the target table.
  6. Create the target table with a page compressed clustered index and no nonclustered indexes.
  7. Insert all rows from the old database into the target table with a TABLOCK hint. This operation may take a long time, but it should be minimally logged and not require a sort.
  8. Build nonclustered indexes one at a time with page compression.
  9. Switch to a full recovery model.
  10. Take a full backup. Doing this after switching the recovery model means you won't have to take another backup.
  11. Restore your temporary database over the original database.
  12. Drop the backup for the old database after you've tested and you're confident that everything checks out.
Related Question