Mistake: treating compression as an automatic performance loss. When SQL Server is I/O-bound and processor capacity is available, row compression can reduce the pages read and improve query performance. section 10.9
ImpressionArchive
Applying row compression to ImpressionArchive reduced storage from 46,982 pages to 33,687—about 28 percent. Row compression stores decimal values at variable length: BIGINT values such as 10 use one byte, 50,000 uses four bytes, and NULL uses no space. section 10.9
Use row compression deliberately:
- Check that the workload is I/O-bound and has spare processor capacity; compression trades storage and reads against additional processor cycles. section 10.9
- Estimate savings before changing the table, because the compression rate depends on the stored data. section 10.14
- Measure the result through I/O statistics or sys.dm_db_partition_stats, filtering index_id = 1 when counting clustered-index pages. section 10.9
Listing 10.17 Implementing row compression
ALTER TABLE ImpressionsArchive REBUILD WITH (DATA_COMPRESSION = ROW) ;
Use the ALTER TABLE rebuild pattern to apply row compression to a table. section 10.9
Listing 10.18 Determining the number of pages in a table
SELECT
in_row_used_page_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('ImpressionsArchive')
AND index_id = 1 ;
Use the metadata query to inspect pages used by the clustered index after compression. section 10.9
