Mistake: treating compression as an automatic performance loss. Page compression can reduce storage and reads for large tables when SQL Server is I/O-bound and spare processor capacity is available; it combines row compression with prefix and dictionary compression. section 10.9
ImpressionArchive
The uncompressed ImpressionArchive table occupies 46,982 pages. Row compression reduces this to 33,687 pages, an approximate 28% reduction. Implement page compression with: ALTER TABLE ImpressionsArchive REBUILD WITH (DATA_COMPRESSION = PAGE); section 10.9
Use a measurement-first checklist:
- Estimate savings before changing the table with sp_estimate_data_compression_savings. section 10.9
- Measure page counts through sys.dm_db_partition_stats, filtering index_id = 1 when counting the clustered index. section 10.9
- Prefer compression for large, I/O-bound workloads with spare processor capacity, because savings depend on the stored data. section 10.14
Listing 10.19 Implementing page compression
ALTER TABLE ImpressionsArchive REBUILD WITH (DATA_COMPRESSION = PAGE) ;
Run the supplied ALTER TABLE rebuild to implement page compression on ImpressionsArchive. section 10.9
