SQL Server Performance Optimization-compression

Http://blog.csdn.net/tuoxie5431/archive/2010/01/19/5214010.aspx

 

When executing SQL queries, the main bottlenecks are: CPU computing speed, memory cache size, and disk Io speed. For queries of large data volumes, the bottleneck is generally concentrated on disk Io and memory cache. In order to improve the efficiency of SQL queries, we need to minimize the number of data entries designed for queries-create an index and set up a partition; on the other hand, we can also consider effectively reducing the physical size of data tables, this reduces the IO size.

In SQL Server 2008, the latest feature "compression" is designed to reduce the physical size of data tables and indexes.

SET COMPRESSION

In the Enterprise Manager, right-click the table or index to be compressed and choose storage-> Manage compression:

Here we can see the following points:

1. The list below lists all the partitions of the table, that is, different compression policies can be applied to different partitions of the same table.
2. Compression type can be divided into row and page.
   
   Row-level compression (ROW ):
   On the one hand, it reduces the size of the dynamic length field metadata (such as varchar and varbinary). For example, the actual length of the stored field needs 2 bytes, and only 3 bits is needed after compression.
   On the other hand, it also directly reduces the size of the content stored in each field. For example, if the value 1 is stored in an int type field, only one byte is occupied after compression.

   Page compression ):The same data can be shared across different rows. Two technologies are included: column prefix and page dictionary ).
   Column prefixThe field values with the same prefix can have a structure similar to the foreign key to store the same prefix and the remaining parts. For example, if a table stores the URLs of all pages of a website, the stored values of the URL fields are '. After compression, they are prefixed '.
   Page dictionaryComment '(fields without repeated values will not be compressed), '3', '2', '2 '.

3. Click "Calculate" to calculate the current space occupied by the table, andSize of space required for compression. Note that this is different from the Expected One. to compress a table with data but not yet compressed, you need an extra space first.

Perform Compression

After setting, you can choose whether to generate a script or execute it immediately. Generally, the compression execution time is affected by the amount of original table data and the compression method. The author compresses pages of a table with tens of millions of records, which takes about 10 minutes.

Check the database size after compression. The database size will become larger! This is also related to the extra space calculated during the setup phase. But in fact, most of the space here is pre-occupied, and there is no actual data. To save disk space, performShrink)Operation.

Unlike compression, shrink is used to release unused space occupied by the database. It is generally used to contract useless log files (if the operation is frequent, log files may be larger than the actual data size of the database ). Here we perform the shrink operation on the database file (MDF). After the operation, we can check the size of the database again, which reduces the size of the database. After compression and shrink, the size of the database is generally reduced to 1/3 ~ About 1/2. Of course, the specific compression ratio depends on the compression method, the field characteristics of the compressed table, and the proportion of the compressed table to the entire database data.

Notes

1. Since the table is compressed, the process of decompression is inevitable during query execution. This process will take up the CPU time, that is, while reducing the disk space and IO time through compression, it will increase the CPU consumption. Therefore, before compression, consider whether the query bottleneck is disk Io, memory, or CPU. In addition, if the table is compressed, it is similar to creating an index, which will also affect the addition, deletion, modification, and other operations. Therefore, we also need to consider which type of table-based operations should be applied.
2. Page compression is performed independently, and the page dictionary and column prefix are also stored in each page. Compression is only performed when the data page is full. Because the size of a page is fixed, the performance of half-page compression is not improved.
3. The compression option is also available in database backup, but this uses the System File compression technology and can only be applied to the entire database.
4. It is easy to ignore that indexes can also be compressed and independent from Table compression, which also improves the query performance from all applications to indexes.
5. In the shrink stage, a large number of index fragments may occur. Therefore, you can re-create or re-organize indexes after shrink is complete. However, these operations will also increase the size of the database ...... That is to say, the index of the smallest database volume and the minimum fragment ratio is a bonus.