Friday, July 10, 2009

Data Compression-SQL Server 2008

We have a sizeable data mart with around 50 tables. Initially they did not have any compression methods applied on them. In SQL Server 2008 there are two kinds of compression which are exposed at the table level: They are Page and Row compression. These techniques enable great space savings and also better query performances. One of things which i noticed was it takes lesser time to load the pages of the table into the memory when it is compressed, this has give me better query performances. In SQL Server 2008 there is a Data Compression Wizard available through SSMS. This feature allows the developers to test out the compression space savings. The Data compression option is set at the table level, this can be done at the CREATE TABLE or ALTER TABLE statements. The wizard is launched by right clicking on a table.

When you choose the Manage Compression Option, the Data Compression wizard comes up.

In this under the Compression Type dropdown there are the Page and Row Compression. When any one of these is chosen one can choose the calculate option and figure out the space savings as listed under the Current Space and Requested Compressed Space. In my Scenario i achieved more savings using Page Compression.

No comments:

Post a Comment