VIII. IO optimization (4) Data Compression

I. Overview

1. Why Data Compression?

The performance of SQL Server depends mainly on the disk I/O efficiency. Improving the I/O efficiency means improving the performance of a program. SQL Server 2008 provides the data compression function to improve disk I/O.

Data Compression reduces the disk usage of data. Therefore, data compression can be used in tables, clustered indexes, non-clustered indexes, view indexes, partition tables, and partition indexes.

2. Enable Compression

Enabling compression only changes the physical storage format of the data associated with the data type, without changing its syntax or semantics. When compression is enabled for one or more tables, you do not need to change the application.

Ii. Data Compression

Data Compression can be achieved at two levels: Row-level and page-level.

Page-level compression includes row-level compression.

1. Row Compression

The new record storage format has the following major changes:

1) reduced the metadata overhead associated with the record. This metadata contains information about the column, column length, and offset. In some cases, the metadata overhead may be greater than the old storage format.

2) It uses a variable length storage format for numeric types such as integer, decimal, and float, and numeric-based types such as datetime and money.

3) It stores fixed-length strings by using a variable-length format that does not store null characters.

4) The NULL and 0 values of all data types are optimized so that they do not occupy any bytes.

2. Page Compression

The process of compressing the table and indexing by PAGE compression consists of three operations in the following order:

1) Row Compression

2) prefix Compression

Take page compression as an example. As shown in, a sample page of the table before prefix compression is displayed on the left, and a page after prefix compression is displayed on the right. You can see that the prefix is moved to the page header, and the column value is changed to a reference pointing to the prefix. In the first column of the first row, the value 4b indicates the first four characters (aaab) and character B with the prefix displayed for the row. In this case, the result value is aaabb, which is the original value.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/210UW093-0.png "title =" prefix compressed .png "alt =" 113111954.png"/>

3) Dictionary Compression

After the prefix is compressed, the application dictionary is compressed. Compress the duplicate values at any location on the search page in the dictionary and store them in the CI area. Unlike prefix compression, dictionary compression is not limited to one column. Dictionary Compression can replace repeated values at any position on the page. Displays the same page after dictionary compression.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/210UQ140-1.png "title =" Dictionary Compression .png "alt =" 113546432.png"/>

Note that the value 4b has been referenced by other columns on the page.

Description:

1) page compression for tables, table partitions, indexes, and index partitions is similar.

2) When page compression is used, only row compression is used to compress the non-leaf-level pages of the index.

Iii. compressing tables and Indexes

1. Estimate the compression result

To determine the impact of changing the compression status on tables or indexes, use the sp_estimate_data_compression_savings stored procedure.

2. Enable Compression

1) SSMS

You can use SSMS to change the table and compression options.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/210UW247-2.png "title =" manage compressed .png "alt =" 114633225.png"/>

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/210UQ964-3.png "style =" float: none; "title =" 01.png" alt = "115219546.png"/>

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/210UV341-4.png "style =" float: none; "title =" 02.png" alt = "115222108.png"/>

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/210UQ158-5.png "style =" float: none; "title =" 03.png" alt = "115224507.png"/>

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/210UUF4-6.png "style =" float: none; "title =" 04.png" alt = "115226277.png"/>

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/210URQ1-7.png "style =" float: none; "title =" 05.png" alt = "115229693.png"/>

2) T-SQL

Tables and indexes can be compressed when they are created.

Create table T1 (C1 int, c2 nvarchar (50 )) WITH (DATA_COMPRESSION = ROW)

You can also use the Alter Table Rebuild With or Alter Index Rebuild With syntax to compress existing tables or indexes.

USE [db01] Alter table [dbo]. [EmpTable2] rebuild partition = ALL WITH (DATA_COMPRESSION = PAGE)

3. Compression of new pages

When creating a new table with page compression, It is not compressed. However, the metadata of the table indicates that page Compression should be used. When data is added to the first data page, row compression is performed on the data. Because this page is not full, page compression cannot be used to obtain any benefits. If the page is full, add the next line to compress the boot page. The page is displayed. Compress each column with a prefix and compress all columns for dictionary compression. If the page compression has created enough space for the row to be added on the page, add the row, compress the data row, and compress the page. If the space obtained by PAGE compression minus the space required by the CI structure is insufficient, the page will not be compressed on this page. Later, the row will be added to the new page. If the new page cannot accommodate more rows, a new page will be added to the table. Similar to the first page, new pages are not compressed at first.

When an existing table that contains data is converted to page compression, each page is regenerated and computed. Re-generating all pages will result in the re-generation of tables, indexes, or partitions.

Iv. Notes

Note the following when using row compression and page compression:

1) compression can be used only in SQL Server 2008 and later versions, Enterprise Edition and Developer Edition.

2) more rows can be stored on one page through compression, but the maximum row size of the table or index is not changed.

3) when the maximum row size plus the compression overhead exceeds the maximum row size by 8060 bytes, the compression function cannot be enabled for the table.

4) Non-clustered indexes do not inherit the compression attribute of the table. To Compress an index, you must explicitly set the compression attribute of the index. By default, when an index is created, the index compression setting is set to NONE.

5) To change the heap compression settings, you must regenerate all non-clustered indexes on the table so that they have pointers pointing to the new row position in the heap.

Conclusion:

Data Compression reduces I/O pressure and improves I/O efficiency.

This article is from the "MSSQL we have Chased Together" blog. For more information, contact the author!