SQL Server data compression: implementation
A Deep Dive into Page and Row Compression
Introduction
As businesses continue to amass large quantities of data, efficient data management becomes increasingly important. One critical aspect of data management is data compression, which can help reduce storage costs, improve query performance, and optimize resource usage. Microsoft SQL Server, an industry-leading relational database management system (RDBMS), provides robust data compression capabilities, allowing organizations to effectively manage and utilize their data. In this article, we will explore the two primary table and index compression methods in SQL Server - page compression and row compression - and discuss their differences and potential benefits. (This is separate from backup compression and columnstore compression, which are different features.)
SQL Server Data Compression
Data compression in SQL Server allows you to store data more efficiently, reducing storage space and often improving I/O performance. By reducing the number of pages that must be read from or written to disk, data compression can reduce the overall I/O workload and help accelerate query execution. Compressed pages also stay compressed in memory, so the buffer pool can hold more data. SQL Server offers two data compression methods: page compression and row compression.
Page Compression
Page compression is a more aggressive and comprehensive compression technique compared to row compression. It works at the page level, meaning that it compresses the contents of an entire database page (8 KB) instead of individual rows. Page compression uses three main techniques: Row Compression, Prefix Compression, and Dictionary Compression.
Row Compression: As part of the page compression process, SQL Server first applies row compression to reduce the storage space required for each row within the page.
Prefix Compression: For each column, SQL Server looks for values on the page that start with the same bytes and stores that common prefix once in the page header. Each value then stores only a reference to the prefix plus whatever is left over, further reducing the storage space.
Dictionary Compression: Lastly, SQL Server looks for repeating values across all columns in the page and stores each unique value once in a shared dictionary, also in the page header. It then replaces the original values with references to the dictionary, leading to a significant reduction in storage space.
Page compression can lead to significant space savings and improved I/O performance, especially when dealing with large tables with repetitive data. However, it requires additional CPU overhead for compression and decompression, which can offset some of the performance gains in certain scenarios. A few details are worth knowing: SQL Server only keeps a page compressed when doing so saves enough space, in a B-tree index only the leaf-level pages are page compressed (the other levels get row compression), and rows inserted into a heap are not page compressed unless the insert is a bulk load or the heap is rebuilt.
Row Compression
Row compression focuses on compressing data at the row level, storing fixed-length data types in a variable-length format to reduce storage requirements. It does not employ the prefix and dictionary compression techniques used in page compression.
Row compression eliminates the storage of unused bytes, so values such as NULLs and zeroes take up no data space, and numeric values are stored in the smallest number of bytes that can hold them. While it generally results in smaller storage savings compared to page compression, it also requires less CPU overhead during compression and decompression. This makes row compression an attractive option for workloads where CPU resources are a limiting factor.
Row compression works by optimizing the storage of fixed-length data types such as INT, BIGINT, DECIMAL, DATETIME, CHAR and NCHAR. For example, a BIGINT column normally uses 8 bytes for every row, but if a particular row holds the value 100, row compression stores it in just 1 byte. Likewise, if a CHAR(50) column holds a 10-character value, row compression removes the 40 characters of trailing padding, so only the space required for the 10 characters is used.
It's important to note that data types which are already variable-length, such as VARCHAR and VARBINARY, get little or no benefit from row compression, because they only ever use the space their actual value requires. (NVARCHAR and NCHAR columns are an exception, since row compression also applies Unicode compression that can roughly halve the size of text that fits in a single-byte range.) Large object data stored off-row (for example VARCHAR(MAX) values that don't fit in the row) is not compressed by either method.
SQL Server Data Compression: Editions
Page and row compression were Enterprise-only features through SQL Server 2014. Starting with SQL Server 2016 SP1 they are also available in Standard Edition, so on current versions most organizations can use them without upgrading editions. If you are running an older version, the Enterprise edition may cost more, but the potential storage and performance benefits provided by data compression can justify the investment for many organizations with large-scale data processing needs.
Monitoring
Monitoring is an important aspect of using SQL Server data compression, most notably CPU time. Using tools such as the Vroom Performance Suite you can monitor your processors and plan capacity accordingly to avoid the negative impact of running without the cycles necessary for your workload.
Examples:
Estimate the savings before compressing:
Test your data! Compression ratios vary a lot from table to table, and this procedure samples your data to estimate the result for ROW or PAGE compression.
EXEC sp_estimate_data_compression_savings 'dbo', 'YourTableName', NULL, NULL, 'PAGE';
Enable row compression on an existing table:
ALTER TABLE dbo.YourTableName REBUILD WITH (DATA_COMPRESSION = ROW);
Enable row compression on a specific index in an existing table:
ALTER INDEX YourIndexName ON dbo.YourTableName REBUILD WITH (DATA_COMPRESSION = ROW);
Enable page compression on an existing table:
ALTER TABLE dbo.YourTableName REBUILD WITH (DATA_COMPRESSION = PAGE);
Enable page compression on a specific index in an existing table:
ALTER INDEX YourIndexName ON dbo.YourTableName REBUILD WITH (DATA_COMPRESSION = PAGE);
Enable page compression on every index in a table:
Compression is set per object. ALTER TABLE ... REBUILD only compresses the table itself (the heap or clustered index), so nonclustered indexes stay uncompressed until you rebuild them too. ALL handles the table and all of its indexes in one statement.
ALTER INDEX ALL ON dbo.YourTableName REBUILD WITH (DATA_COMPRESSION = PAGE);
Disable compression (row or page) on an existing table:
ALTER TABLE dbo.YourTableName REBUILD WITH (DATA_COMPRESSION = NONE);
Disable compression (row or page) on a specific index in an existing table:
ALTER INDEX YourIndexName ON dbo.YourTableName REBUILD WITH (DATA_COMPRESSION = NONE);
Check the current compression setting:
SELECT index_id, partition_number, data_compression_desc
FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.YourTableName');
Keep in mind that enabling or disabling compression using the ALTER TABLE or ALTER INDEX statements requires rebuilding the table or index, which can be resource-intensive and time-consuming for large tables. The rebuild uses CPU, transaction log and tempdb space, and by default it blocks access to the table while it runs (adding ONLINE = ON avoids this on editions that support online rebuilds). Schedule these operations during maintenance windows or periods of low database activity to minimize the impact on database performance.
Conclusion
Data compression is an essential feature of SQL Server, offering organizations significant storage savings and often improved query performance. By understanding the differences between page and row compression, businesses can make informed decisions about which compression method best suits their specific needs and resource constraints. As data volumes continue to grow, effective data compression will remain a critical component of successful data management strategies.