SQL Server 2012 notes Sharing-10: Understanding Data compression

Key Concepts

Configuration data compression can be implemented in both SSMS and T-SQL statements.

Table and Index compression

For row storage tables and indexes, using data compression can help reduce the size of the database. In addition to saving space, data compression can also help improve the performance of I/O-intensive workloads because the data is stored in fewer pages and queries require fewer pages to read from disk. However, when exchanging data with an application, additional CPU resources are required on the database server to compress and decompress the data.

For Columnstore tables and indexes, all columnstore tables and indexes always use Columnstore compression, and users cannot configure this. Using Columnstore archive compression can further reduce the size of your data when you can spend additional time and CPU resources to store and retrieve data.

Row compression and page compression

Enabling compression only changes the physical storage format of the data associated with the data type, without changing its syntax or semantics.

implementation of row compression: http://msdn.microsoft.com/zh-cn/library/cc280576.aspx

Page compression is similar for tables, table partitions, indexes, and index partitions.

implementation of page compression: http://msdn.microsoft.com/zh-cn/library/cc280464.aspx

Sp_estimate_data_compression_savings

Returns the current size of the requested object and estimates the size of the object under the requested compression state. Compression can be evaluated for all tables or for some tables. This includes heaps, clustered indexes, nonclustered indexes, indexed views, and table and index partitions. You can compress these objects by using row compression or page compression. If the table, index, or partition is compressed, you can use this procedure to estimate the size of the table, index, or partition in the case of re-compression.

You can use Sp_estimate_data_compression_savings to estimate the amount of savings that can be achieved when row compression or page compression is enabled on a table or partition. For example, if the average size of a row can be reduced by 40%, you might be able to reduce the object size by 40%. You may not be able to save space because it depends on the fill factor and the row size. For example, if a row is 8000 bytes long and you reduce the size of the row by 40%, the data page can still hold only one row. Therefore, it will not save space.

If the result of running sp_estimate_data_compression_savings indicates that the size of the table will grow, it means that many rows in the table use almost the full precision of the data type, and thus the small amount of overhead that is required to meet the compression format is greater than the amount of savings that the compression brings. In this very rare case, do not enable compression.

If you enable compression on a table, use Sp_estimate_data_compression_savings to estimate the average size of the row without compressing the table.

The (IS) lock of the table is acquired during this operation. If the (IS) lock cannot be obtained, the procedure is blocked. The table will be scanned at the Read Committed isolation level.

If the requested compression setting is the same as the current compression setting, the stored procedure returns the estimated size when there is no data fragmentation and the existing fill factor is used.

If the index or partition ID does not exist, no results will be returned.

Reference: http://msdn.microsoft.com/zh-cn/library/cc280574.aspx

Save disk IO and memory

Unicode compression

SQL Server uses the Unicode Standard compression scheme (Compression scheme for Unicode, SCSU) algorithm implementation to compress Unicode values stored in a row or page-compressed object. For these compressed objects, Unicode compression is automatic for nchar (n) and nvarchar (n) columns. The database engine stores Unicode data as 2 bytes, regardless of locale. This is called UCS-2 encoding. For some locales, implementing SCSU compression in SQL Server can save up to 50% of storage space.

Http://msdn.microsoft.com/zh-cn/library/ee240835.aspx

References: http://msdn.microsoft.com/zh-cn/library/cc280449.aspx

This article from "Zeng Hung Xin Technical column" blog, declined to reprint!