powershelldba.de

SQL Server Table Compression: When to Use ROW vs. PAGE

Compression saves storage and reduces I/O — but costs CPU. Learn when it's worth it and how to implement it safely.

What is Table Compression?

SQL Server can compress table and index data, reducing storage and I/O at the cost of CPU. Instead of storing each row or page in full, SQL Server removes redundancy:

Uncompressed (8 KB page):
Row 1: OrderID=1000, Status='Pending', Amount=NULL, Region='USA'
Row 2: OrderID=1001, Status='Pending', Amount=NULL, Region='USA'
Row 3: OrderID=1002, Status='Pending', Amount=NULL, Region='USA'
... (repeated data uses 8 KB)

With Compression:
Prefix: Status='Pending', Amount=NULL, Region='USA'
Row 1: OrderID=1000
Row 2: OrderID=1001
Row 3: OrderID=1002
... (shared prefix uses less space)

SQL Server offers two types of compression: ROW and PAGE.

ROW Compression

How It Works

ROW compression targets the storage format of individual rows:

ROW compression is relatively cheap (low CPU cost) and is the first level of compression.

Typical Space Savings

Implementation

-- Apply ROW compression to existing table
ALTER TABLE Orders REBUILD WITH (DATA_COMPRESSION = ROW);

-- Create new table with ROW compression
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    Status VARCHAR(50),
    Amount DECIMAL(10,2)
)
WITH (DATA_COMPRESSION = ROW);

-- Apply to partitions
ALTER TABLE Orders REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = ROW);

PAGE Compression

How It Works

PAGE compression includes ROW compression plus additional optimizations:

PAGE compression is more expensive (higher CPU cost) but can save significantly more space.

Typical Space Savings

Implementation

-- Apply PAGE compression
ALTER TABLE Orders REBUILD WITH (DATA_COMPRESSION = PAGE);

-- Create with PAGE compression
CREATE TABLE Orders (
    OrderID INT,
    Status VARCHAR(50)
)
WITH (DATA_COMPRESSION = PAGE);

ROW vs. PAGE Compression

Aspect ROW PAGE
Space Savings 10–30% 30–80%
CPU Overhead Low (2–5%) Medium-High (5–15%)
I/O Reduction Moderate Significant
Query Decompression Cost Minimal Noticeable
Best For OLTP, large fixed-length columns Data warehouse, archive, read-heavy
Downside Limited savings on sparse data CPU spike on reads, maintenance
Enterprise Only? ROW: Standard Edition+ PAGE: Enterprise Edition only

When to Use Compression

Good Candidates for Compression

Bad Candidates for Compression

The CPU-Storage Trade-Off

You Save Storage, You Spend CPU

Every row read requires decompression. Every row written requires compression. Here's the math:

Uncompressed:
100 GB table, 10 million rows
Query: SELECT * WHERE id = 123
Time: Read 1 page (8 KB), no decompression = 0.1 ms

With PAGE Compression:
30 GB table (70% savings), 10 million rows
Query: SELECT * WHERE id = 123
Time: Read 1 page (8 KB), decompress = 0.5 ms

Cost: CPU spike + 5x slower query
Benefit: 70 GB storage saved, 3x faster backup/restore

Measuring Compression Impact

Estimate Space Savings

-- See estimated compression savings
EXEC sp_estimate_data_compression_savings
    @schema_name = 'dbo',
    @table_name = 'Orders',
    @index_id = NULL,  -- NULL = all indexes
    @partition_number = NULL,
    @data_compression = 'ROW';  -- or 'PAGE'

Returns: size_with_current_compression_setting, size_with_requested_compression_setting, sample_size_with_compression, sample_size_without_compression.

Monitor Compression Overhead

-- Check CPU before/after compression
-- Enable DMV collection
DBCC DROPCLEANBUFFERS;  -- Clear cache
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Query before compression
SELECT * FROM Orders WHERE Status = 'Pending';

-- Note CPU/I/O, then apply compression and repeat

Check What's Compressed

-- List all compressed objects
SELECT
    OBJECT_NAME(ps.object_id) AS TableName,
    i.name AS IndexName,
    ps.data_compression_desc
FROM sys.partitions ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.data_compression > 0
ORDER BY OBJECT_NAME(ps.object_id);

Best Practices

1. Test Before Production

Apply compression to a test table. Measure CPU, I/O, and query times. If query latency increases > 10%, consider ROW-only or no compression.

2. Start with ROW Compression

ROW is low-cost, decent savings. If you need more, then evaluate PAGE.

3. Compress Cold Data Only

Compress old partitions in a partitioned table. Leave recent partitions uncompressed for fast writes.

-- Partition 1-11 (old data): PAGE compression
-- Partition 12 (current month): No compression

4. Use Compression for I/O-Bound Scenarios

If your bottleneck is I/O (slow disks, large scans), compression helps. If bottleneck is CPU, avoid it.

5. Monitor Fragmentation

Compressed tables fragment during INSERTs/UPDATEs. Monitor and rebuild periodically.

6. Plan for Backup/Restore

Compression speeds backups (less data to write) but slows restore (must decompress). Calculate net time savings.

7. Document Compression Strategy

-- Comment in code
/*
  Orders table is compressed with PAGE compression.
  Rationale: 500 GB table, read-heavy (reporting),
  expected 60% space savings, acceptable CPU cost for I/O reduction.
  Monitoring: CPU spike <5%, query latency <2% increase.
  Last reviewed: 2024-01-15
*/
ALTER TABLE Orders REBUILD WITH (DATA_COMPRESSION = PAGE);

Real-World Example: Archive Table

A company archived 2 billion historical transaction records in a separate table:

They applied PAGE compression:

Clear win: storage savings far outweighed CPU cost for a read-only archive table.

The Verdict

Compression is a tool, not a default setting. Use it strategically: compress cold data, archives, and read-heavy tables. Avoid it on OLTP tables with high write rates.

Before applying compression to production:

Done right, compression saves storage and I/O without breaking performance. Done wrong, it adds CPU cost for minimal savings. The difference is testing and measurement.

← Back to Blog