SQL Server Compression: Reducing Storage and Improving Performance

In the world of enterprise database management, storage is rarely just about “space.” It’s about I/O throughput, memory efficiency, and the fine balance between physical hardware limits and CPU cycles. SQL Server Data Compression is a powerful feature designed to tip this balance in your favor by shrinking the physical footprint of your data.

What is SQL Server Data Compression?

At its core, data compression in SQL Server reduces the amount of physical space required to store data. While the primary goal is often to save on storage costs, the performance benefits are frequently more significant:

• Reduced Physical I/O: Data is stored in fewer pages, meaning queries need to read fewer pages from the disk.
• Enhanced Memory Efficiency: The buffer pool (RAM) can hold exponentially more data because compressed pages stay compressed in memory.
• Performance Trade-off: The “cost” of these gains is additional CPU cycles required to compress and decompress data as it moves between the database engine and your application.

Types of Compression

SQL Server offers a continuum of compression architectures, each suited for different workloads.

1. Row Compression

Row compression changes the physical storage format without altering the syntax or semantics of your data. It primarily works by:

• Trimming Fixed-Length Data: Converting fixed-length data types (like char(100)) into variable-length formats, stripping away trailing spaces.
• Optimizing Zeros and NULLs: These take up 0 bytes in a row-compressed state.
• Impact: Ideal for high-update OLTP (Online Transaction Processing) tables because it has minimal CPU overhead (~10%).

2. Page Compression

Page compression is more aggressive. It first applies row compression and then performs two additional steps on full data pages:

• Prefix Compression: Identifies common prefix values in a column and replaces them with short reference pointers.
• Dictionary Compression: Scans the entire page for repeated values across all columns and replaces them with tiny references stored in a “Compression Information” (CI) dictionary.
• Impact: Best for tables with high scan rates but low update volume, such as audit logs.

3. Columnstore and Columnstore Archive

• Columnstore: The default for analytical workloads, providing massive compression for column-based data.
• Columnstore Archive: Uses the Microsoft XPRESS algorithm for maximum reduction of “cold” analytical data. It offers the highest storage gain but carries an extreme CPU penalty upon retrieval.

Summary Comparison Table

1. Row Compression

Think of this as “data type optimization.” It focuses on the individual rows within a data page.

• The Logic: It converts fixed-length data types into variable-length types. For example, if you have a CHAR(100) column but only store the word "SQL," Row Compression will reclaim the 97 bytes of wasted space.
• Key Features: It handles zeros and NULLs efficiently (taking up almost no space) and strips out empty padding.
• Performance: It is very “lightweight” on the CPU. It’s perfect for OLTP (transactional) environments where you need a little more space without slowing down high-speed inserts and updates.

2. Page Compression

This is a more aggressive approach that builds on top of Row Compression. It evaluates the entire 8KB data page as a whole.

• The Logic: As seen in your previous diagram, it uses Prefix and Dictionary compression to find repeating patterns across all rows and columns on a single page.
• Storage Gain: Significantly higher than Row Compression (often 30% to 50% or more depending on data redundancy).
• Performance: It requires more CPU power to compress and decompress data. It’s best for “Warm” data — tables that are read often but not updated every second.

3. Columnstore Compression

This is the “heavy hitter” used primarily for Big Data and Data Warehousing.

• The Logic: Unlike the first two (which are Rowstore), this stores data in columns rather than rows. Because data in a single column is usually very similar (e.g., a “Country” column with many “USA” entries), SQL can use highly efficient algorithms to compress it.
• Storage Gain: Massive reduction, often reaching 10x or more.
• Performance: It is designed for massive scans and aggregations (like SUM or AVG). It is not efficient for looking up a single specific row.

Practical SQL Examples

Before jumping into implementation, you must evaluate and estimat

Step 1: Evaluate Your Workload

Use operational statistics to see if a table is a good candidate for the CPU penalty of page compression.

-- Checking for High Scan vs. High Update patterns
SELECT * FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('YourTable'), NULL, NULL);

• Prime Candidate: High range_scan_count and low leaf_update_count.

Step 2: Estimate Savings

Never compress “blindly.” Use the system stored procedure to simulate savings in tempdb.

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

• Warning: If the estimation shows a size increase, the overhead of compression outweighs the potential gains.

Step 3: Execute the Compression

Compression is applied via an index rebuild.

-- Applying Page Compression to an existing table
ALTER TABLE YourTable REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);

Pro Tip: For heaps, enabling compression online is strictly single-threaded. Use OFFLINE mode for multi-threaded performance.

Real-World Use Cases

• The “Data Temperature” Strategy: In large partitioned tables, you can apply different compression levels based on age.
• Hot Data (Current Month): No compression or Row compression for high-volume updates.
• Warm Data (Previous Quarter): Page compression for high-read reporting.
• Cold Data (Archival): Columnstore Archive for maximum storage savings.
• Log Files and Audit Trails: These tables are often read sequentially and rarely updated, making them perfect candidates for Page compression.

Performance Tips for Enterprise Systems

1. Monitor Physical Effectiveness: Use sys.dm_db_index_physical_stats in DETAILED mode to validate the actual compressed_page_count after implementation.
2. Explicit Index Compression: Nonclustered indexes do not automatically inherit the compression property of the base table; they must be compressed explicitly.
3. Mind the Export: While backup and restore operations remain compressed on disk, data exported via BCP or other tools will be output in an uncompressed format, requiring significantly more space at the destination.
4. Know the Limits: System tables, sparse columns, and out-of-row LOB (Large Object) data greater than 8,060 bytes cannot be compressed.

By strategically applying SQL Server Data Compression, you can significantly reduce your I/O bottleneck and increase the effective capacity of your server’s memory, ensuring your enterprise systems remain fast and cost-effective.

Get Dushyantha Kalehewatte’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

References
https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-ver15

https://community.relativity.com/s/news/aAZ1T000000CqYjWAK/an-introduction-to-sql-server-data-compression

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd894051(v=sql.100)?redirectedfrom=MSDN#_blank

https://www.sqlshack.com/use-sql-server-data-compression-save-space/