Optimized sqlserver--data compression

Data compression is an enhancement to storage and performance benefits. Reducing the amount of disk space consumed by the database reduces overall data file storage space and increases throughput in several ways:

1. Better I/O utilization, each page can read and write more data.

2. Better memory utilization, buffers can cache more data.

3. Reduce the blocking of the page, each page can contain more data.

Because data compression must take into account the balance between I/O and the CPU, both compression and decompression require CPU processing. Data compression is therefore more meaningful for old data and infrequently queried data.

Here we discuss two kinds of compression: one is row compression, and the other is page compression.

row compression: compression can change the format of the stored fixed value data type--also say that a 4-byte column containing a value of one byte can be compressed to 1 bytes, 1-byte columns cannot be compressed, but null and 0 values do not account for bytes.

syntax: creating a CREATE Table table name () with (Data_comperssion=row)

Add ALTER table name REBUILD with (Data_comperssion=row)

page Compression: page compression in the implementation of row compression, also implemented two compression, one is the prefix compression (for each page and column prefix value is the same, can be used to reduce the storage requirements, by reference to replace the duplicate prefix), and the second is Dictionary compression (search page of duplicate values, This is replaced by a reference to CI).

syntax : Creating a CREATE Table table name () with (Data_comperssion=page)

Add ALTER table name REBUILD with (data_comperssion=page)

Estimated space savings: syntax sp_estimate_data_compression_savings

[@schema_name =]--schema name

, [@object_name =]--the name of the indexed table or indexed view.

, [@index_name =]--index ID number

, [@partition_number =]--object partition number, NULL indicates non-partitioned

, [@data_compression =]--Compression type (none\row\page)

[;]

monitoring data compression:There are two counters in the Sqlserver:access method object for Windows performance monitoring:

Page Compression Attempts/sec: Calculates the number of page compression attempts per second;

Pages Compressed/sec: Calculates the number of pages compressed per second.

Finally, note the data compression considerations:

1. Enabling and disabling table or cluster index compression reconstructs all fee cluster indexes.

2. You cannot use compression in sparse columns.

3. Lob beyond the line cannot be compressed.

4. Non-leaves in the index can also be compressed using row compression only.

5. Non-clustered indexes do not inherit the standard compression settings.

6. When you delete a clustered index, the table retains these compression settings.

7. Unless specifically specified, creating a clustered index inherits the compression settings of the table.

Summary: We have already learned the basic usage and some considerations, by compressing the implementation of high-performance database system. At the same time we have to take into account some of the cost of hardware, and finally we have to note that for high-availability systems, changing the compression settings may result in additional transaction log operations.

Optimized sqlserver--data compression