Data Compression– a unique feature of SQL Server TIP #91

A part from performance many times we faced challenges related to space  of our database. Sometimes our database is actually taking huge space and sometimes it our mistake due to which it took space.

It is worst condition when you are on a dedicated hosting or cloud hosting with limited space. In such case “Compression” is one of the best option.

This feature introduce in SQL SERVER 2008 onwards with Enterprise & Developer Edition.

By the name it is clear that it compress the data and provide more space on disk.

There are 2 options available in Data compression

Row level compression:-

 Row level compression primarily turns fixed-length data-types into variable data-types, thereby saving space. It also ignores zero and null values saving additional space. Because of this, more number of rows can be accommodated in a single data page.

Page level compression:-

Page level compression initially performs Row Level compression and adds two additional compression features – Prefix and Dictionary Compression. Page level compression offers better space saving than row level compression. Although the page level compression produce more space but obvious the CPU utilization is higher in page level compression. Overall we can say the page level compression is highly CPU consuming operation means it involves lot of  CPU efforts for compress & de compression. So choosing this option requires a high CPU & RAM configuration machine. Due to this feature it mostly used in data warehouse database where repeated values occurs. using this option with a table where lots of write operation occurs in day to day activity will be a bad idea.

Now we aware of data compression and its two options. Now lets discussed some of the major points before using this valuable feature

1) Data compression is SQL SERVER feature so we don’t need to do any tweak or rewrite our application code which is a good thing.

2) Compression is real time so again you don’t need to re run a maintenance job each time to compress the data.

3) As mention earlier you have to pay bit CPU cost for compression.

4) with Compression data files MDF files, ldf files and buffer cache size can be improve.

5) Compression not compress FILESTREAM data object which is obvious.

6) Although maintenance of compressed index is higher.

In next post I will show simple steps for implementing this best feature.

Enjoy !!!

Thanks

RJ!!!