SQL Server Performance Tuning table compression for SQL Server 2008

When the database is larger, and we want to make a backup, we can start the compression of the database backup, thus reducing the consumption of disk space.

In fact, not only can be compressed at the time of backup, in SQL Server 2008, we can also compress the table, the following compression and compression before the comparison, to demonstrate the SQL Server 2008 powerful table compression capabilities.

Here in the test database to find a larger table, a total of 9,439,661 records,

1, the size of the pre-compression table

sp_spaceused ' TB_WCB '/*name            rows    reserveddata   index_sizeunusedtb_wcb            9439661 746016    KB412216 KB329944 KB3856 kb*/

2. Compact Table size

The row-level compression is enabled here

ALTER TABLE Tb_wcbrebuildwith (data_compression =row)

3. The size of the table after compression

sp_spaceused ' TB_WCB '/*name            rows    reserveddata   index_sizeunusedtb_wcb            9439661 497784    KB167168 KB328120 KB2496 kb*/

4, the comparison after the table compression

As can be seen from 1, the data size before compression is 412M, and after compression is 167M, the size of the table after compression is only 40% of the original table, the effect is obvious, and because most of the table's fields are only IDs, the relative repetition value is not too much.

However, we see that the size of the index varies substantially, so we continue to compress the index:

5. Compression index

Alter index idx_tb_wcb_id on Tb_wcbrebuildwith (Data_compression=row)

6, the comparison after the index compression

sp_spaceused ' ms_visit_qst_opt '/*name            rows    reserveddata    INDEX_SIZEUNUSEDTB_WCB            9439661    317208 KB167168 KB149872 KB168 kb*/

We found that the size of the index before compression is 329M, and after compression is 149M, compression ratio of 45%, the effect is also obvious.

With the compression of tables and indexes, we can reduce the disk space consumed by the table, and allow more data pages to be cached in memory, significantly improving IO efficiency.

SQL Server Performance Tuning table compression for SQL Server 2008