SQL Server performance Tuning SQL Server 2008 This table compression

When the database is relatively large, and when you want to back up, we can start the database backup compression. This is because the backup file is smaller compression, so the entire backup faster, but also lower the consumption of disk space.

There is, of course, one aspect. The CPU consumption will definitely be added. Just the general server is multi-core. So there's really no big impact on the system.

In fact. Not only can it be compressed at the time of backup, but in SQL Server 2008. We are also able to compress tables and indexes, which are compared by compression before and after compression. To demonstrate the compression capabilities of SQL Server 2008 's powerful tables and indexes.

Here in the company test database to find a medium-sized table, together with more than 9.43 million 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. Compression table

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, table compression before and after the control

The data size before compression is 412M (data field value is the size of the table, and index_size is the size of the index, the two are different), and after compression is 167M, the size of the table is only 40% of the original table, the effect is very obvious, and because the table most of the fields are just ID , relatively repeated values are not too much, assuming a lot of repetition value of the other, then the compression effect will be better.

However, we see that the size of the index (index_size) basically does not change. So we proceed with the compression of the index:

5. Compression index

Alter index idx_tb_wcb_id on Tb_wcbrebuildwith (Data_compression=row)

6, after the index compression of the control

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

we found that the size before index compression was 329M. And after compression is 149M, the compression ratio is 45%. The effect is also very obvious.

Summarize:

Compression through tables and indexes. We can reduce the disk space occupied by the table, this is only part of it, and more importantly, reading the same amount of data, just need to read less data pages, that is, only need less IO. The reading speed will certainly be faster, and because of the small footprint, it allows many other numbers to be cached on the Web page, further improving the overall performance of the system.

Copyright notice: This article Bo Master original article. Blog, not reproduced without consent.

SQL Server performance Tuning SQL Server 2008 This table compression