Home / Databases / SQL Server

DatabasesHow-toIntermediate

Optimization: SQL Server data compression and columnstore - smaller tables, faster scans, lower storage cost

Row and page compression shrink data on disk and in memory, and columnstore indexes make large analytical scans far faster. Estimate savings first, choose page or row compression by update pattern, use columnstore for large fact and history tables, and watch CPU and maintenance impact.

Deepak Bhardwaj2 min read

Applies toSQL Server 2016 SP1 and later (compression in all editions), Azure SQL Database and Managed Instance

Options

Option Best for Trade-off
ROW compression OLTP tables with frequent updates Modest savings, low CPU
PAGE compression Read-mostly tables, large indexes Bigger savings, more CPU on writes
Clustered columnstore Large fact/history tables, analytics Excellent compression and scan speed; point lookups and singleton updates slower
Nonclustered columnstore on OLTP table Real-time analytics on operational data Extra maintenance; test write impact
COLUMNSTORE_ARCHIVE Cold history partitions Highest compression, slower queries

Estimate before changing

EXEC sp_estimate_data_compression_savings
     @schema_name = 'dbo', @object_name = 'OrderLines',
     @index_id = NULL, @partition_number = NULL,
     @data_compression = 'PAGE';

Compare size_with_current_compression_setting(KB) vs size_with_requested_compression_setting(KB). SQL Server 2019+ can also estimate for COLUMNSTORE.

Find candidates

SELECT TOP 20 OBJECT_SCHEMA_NAME(p.object_id) AS sch, OBJECT_NAME(p.object_id) AS tbl,
       i.name AS idx, p.data_compression_desc,
       SUM(a.total_pages) * 8 / 1024 AS size_mb
FROM sys.partitions p
JOIN sys.allocation_units a ON a.container_id = p.partition_id
JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE OBJECTPROPERTY(p.object_id, 'IsUserTable') = 1
GROUP BY p.object_id, i.name, p.data_compression_desc
ORDER BY size_mb DESC;

Then check update rate (sys.dm_db_index_operational_stats leaf updates vs scans) to choose ROW vs PAGE.

Apply

ALTER INDEX ALL ON dbo.OrderLines REBUILD WITH (DATA_COMPRESSION = PAGE, ONLINE = ON);

-- Columnstore for a large history table
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesHistory ON dbo.SalesHistory WITH (DROP_EXISTING = OFF);

Rebuilds need space and log; do them in a window and watch AG/log shipping lag.

Columnstore health

  • Load in batches of ≥ 102,400 rows to go straight to compressed rowgroups.
  • Many open/small rowgroups or many deleted rows → ALTER INDEX ... REORGANIZE (with COMPRESS_ALL_ROW_GROUPS = ON where needed).
  • Check sys.dm_db_column_store_row_group_physical_stats for state and deleted rows.

Measure

Metric Expect
Table/index size Lower
Logical reads for key queries Lower (fewer pages)
CPU Slightly higher for writes; often lower overall due to fewer reads
Backup size/time Lower

Tip

Compression also reduces memory pressure because compressed pages stay compressed in the buffer pool. On memory-constrained servers it can be one of the cheapest performance wins.

Checklist

  • Largest tables and update patterns identified
  • Savings estimated
  • ROW/PAGE/columnstore chosen per table
  • Rebuilds scheduled with log impact considered
  • Columnstore rowgroup health monitored
  • Size, reads and CPU compared

Related fixes