Data compression

In one line: compression trades CPU for pages. It’s a great trade on data that repeats and is read far more than it’s written, a bad one on hot, heavily updated tables, and no trade at all on random data or big off-row values.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/data-compression on GitHub.

What you’ll see

The same million rows, compressed every way SQL Server offers:

compressionsizereads per scanCPU per scan
NONE116.8 MB14,95247 ms
ROW55.5 MB7,09886 ms
PAGE22.9 MB2,93097 ms
COLUMNSTORE13.2 MB37513 ms
COLUMNSTORE_ARCHIVE6.4 MB

Why it happens

  • ROW stores each value in as few bytes as it needs.
  • PAGE adds a per-page dictionary of repeated values.
  • Columnstore stores columns separately and encodes runs.
  • Compressed rowstore reads fewer pages but decompresses every row it reads. PAGE read a fifth of the pages for twice the CPU.

What to do

  • Estimate first with sp_estimate_data_compression_savings. In the demo it was within 3% of the real result.
  • Use PAGE for data that repeats and is mostly read, especially where memory or I/O is short.
  • Prefer ROW for tables updated all day. Updating 300,000 rows of a PAGE-compressed table took 6.8× the CPU, and the table grew 30% from page splits until it was rebuilt.
  • Rebuild PAGE-compressed heaps, or load them with TABLOCK. Ordinary inserts into a heap get only ROW compression: 1,415 pages instead of 590.
  • Use COMPRESS() for big documents you store and fetch whole. 5,000 JSON documents went from 15,044 pages to 838, at the price of a varbinary(max) column that every reader has to DECOMPRESS().

What doesn’t help

  • Compressing random data. GUIDs, hashes, encrypted values and images saved 1.6%, and every read still pays to decompress.
  • ROW or PAGE on off-row LOB values. nvarchar(max) documents compressed with PAGE saved nothing (15,044 pages to 15,051).

Check your server

-- Size and compression of every table and index partition in this database.
SELECT OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
       OBJECT_NAME(p.object_id)        AS table_name,
       i.name                          AS index_name,
       i.type_desc,
       p.partition_number,
       p.data_compression_desc,
       p.rows,
       UsedMB = CAST(ps.used_page_count * 8 / 1024.0 AS decimal(12, 1)),
       LobMB  = CAST(ps.lob_used_page_count * 8 / 1024.0 AS decimal(12, 1))
FROM sys.partitions AS p
JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.dm_db_partition_stats AS ps ON ps.partition_id = p.partition_id
WHERE OBJECTPROPERTY(p.object_id, 'IsUserTable') = 1
ORDER BY ps.used_page_count DESC;

A large LobMB won’t shrink with ROW or PAGE. A HEAP marked PAGE that gets ordinary inserts needs regular rebuilds.

See it happen

The data-compression demo compresses one table five ways, checks the estimate, and runs the same report on each copy. It then tries data that won’t compress, updates compressed rows, loads a PAGE heap, and stores big documents with COMPRESS().

Related: columnstore and the delta store · heaps and forwarded records · index fragmentation

Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights