Enabling page compression on 1.5TB table

Question

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.

Answer 1

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.

Answer 2

Your plan sounds well except for this one:

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 ?

If you truncate a table and enable a page compression, you must do INSERT and not SELECT INTO and at this point you should use TABLOCK to get page compression and not row compression only.

Besides this, rebuilding with compression is much faster that an insert into a compressed table, we have a difference in 3-5 times on large tables.

Have a look here: The Data Loading Performance Guide, section Data Compression and Bulk Load:

When data is bulk loaded into a compressed table or partition, both page-level and row-level compression are generally done at bulk load time. However, you should be aware of an exception: When bulk loading into a page compressed heap, you must use the TABLOCK hint to achieve page compression. If the TABLOCK hint is not used, only row level compression is done on the heap. Using page compression on a bulk target will generally slow down bulk load speeds – especially if the I/O system is able to supply enough speed to saturate the CPUs.

Answer 3

I wonder if your table is partitioned (it would make sense to partition such a large table)?

If your main concern space then you can compress each partition separately. If you have 500 partitions on average it would be just 30-50GB. Another benefit of such approach that you can change compression on a subset of partitions, so you can do it at a different point in time.

ALTER INDEX <IX_Table_ClusteredIndex>
ON <Table> REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = PAGE); 

Here is a query which would generate you INDEX REBUILD for all partitions:

SELECT '    ALTER INDEX ['+ i.name + '] ON [' + s.name + '].[' + o.name + 
    '] REBUILD PARTITION = ' + CONVERT(VARCHAR(MAX), p.partition_number) + 
    ' WITH (DATA_COMPRESSION = PAGE, ONLINE = ON); '
FROM sys.partitions p
    INNER JOIN sys.objects o
        ON p.object_id = o.object_id
    INNER JOIN sys.schemas s
        ON o.schema_id = s.schema_id
    INNER JOIN sys.indexes i
        ON p.index_id = i.index_id
        AND p.object_id = i.object_id
WHERE o.name = 'LargeTable' 
ORDER BY p.partition_number

INDEX REBUILD can be a minimally logged operation. And since you are on Enterprise edition you can rebuild it with ONLINE = ON.