Compression is not automatically a performance penalty. For I/O-bound workloads with spare processor capacity, row or page compression can reduce pages read and relieve pressure on the I/O subsystem. section 10.9
Avoid the mistake of leaving large tables uncompressed when their workload fits these conditions:
- Check whether the workload is I/O bound and has spare processor capacity; compression exchanges processor cycles for fewer pages read. section 10.9
- Measure before changing the table. Compression savings depend on the data stored, so estimate row and page options before implementation. section 10.14
- Verify the result through I/O statistics or metadata page counts rather than assuming that an ALTER TABLE operation delivered useful savings. section 10.9
ImpressionArchive
An uncompressed ImpressionArchive table required 46,982 pages. Rebuilding it with row compression reduced storage to 33,687 pages, an approximate 28% reduction. Row compression uses variable-length decimal storage: a BIGINT value of 10 can use one byte, 50,000 can use four bytes, and NULL uses no space. section 10.9
Listing 10.17 Implementing row compression
ALTER TABLE ImpressionsArchive REBUILD WITH (DATA_COMPRESSION = ROW) ;
Apply row compression with ALTER TABLE ... REBUILD and DATA_COMPRESSION = ROW. section 10.9
Listing 10.20 Estimating table size with compression
EXEC sp_estimate_data_compression_savings @schema_name = 'dbo', @object_name = 'ImpressionsArchive', @index_id = 1, @partition_number = NULL, @data_compression = 'none' ;
Estimate compressed table size with sp_estimate_data_compression_savings before implementation. section 10.9
