Data Compression

Data compression is an enhanced feature available in SQL Server 2008. Database compression in SQL Server is introduced with SQL Server 2005 SP2 onwards where we have the capability to compress VARDECIMAL data type. This compression works when the actual value is less than the declared value, all the remaining values will be removed there by compressing the empty space. In SQL Server 2008 we have native compression techniques added to reduce the size of your database.

Editions Supporting Compression

SQL Server 2008 Enterprise
SQL Server 2008 Developer

In this article we are going to focus on need of compression and overview of compression enhancement.

Why we require compression in SQL Server?

Cost spent to Disk Space

This is one of the main focus for compressing the data in SQL Server. When you compress your data the amount of storage space required will get reduced there by reducing money spent on storage devices. You folks can think that in current market trend the prices are reduced however when you go for server environment we will be focusing on SAN disks and not the regular disks, so the cost is high for these type of disks. In addition to this your database backup size is also reduced to a greater extent hence again space used will be reduced for backups.

data_compression_part1_1

Low Disk IO

When your data storage size is reduced obviously there will be a great decrease in disk IO. After the compression you will be reading writing a small set of data size there by reducing the disk IO.

data_compression_part1_2

Easier to maintain compressed database

This is nothing but your maintenance plan. Lets say your rebuild or reorganize will take more time based on the size of your table, in this case the time taken will be reduced.

BEFORE Compression
==================
SQL Server Execution Times:
CPU time = 1904 ms, elapsed time = 4323 ms.

AFTER Compression
==================
SQL Server Execution Times:
CPU time = 6880 ms, elapsed time = 3663 ms.

Save cost spent on Memory

When you retrieve the compressed data it will be stored in buffer manager in compressed state only hence the size of physical memory required will be reduced. Obviously if you require less physical memory then you dont need to spend money in RAM for your server.

Works better than NTFS compression

NTFS compression considers database physical files as just files. There is no way to compress particular database table using NTFS compression.

Overview of Compression Technique Enhancement in SQL Server

I think no explanation is needed, the image will give you an clear idea on compression technique enhancement in SQL Server family.

data_compression_part1_3

Comments

4 responses to “Data Compression”

Ganapathy

June 20, 2012

In compression table is having all varchar columns. are these columns also compresses or only fixed datatype column. like char,decimail, bigint

1. VidhyaSagar
  
  June 20, 2012
  
  It depends on compression type you choose. In varchar it wont allocate more space, it will allocate space only for the characters stored. So there wont be any compression if you choose row level compression however if you choose page level then you can attain some compression.
  
Ganapathy

June 21, 2012

Oh yeah, got it. Thank you

Data Compression (SQL Server 2008, 2008R2 Enterprise Edition) « dbadiary

October 3, 2012

[…] good starting point: “Types of Data Compression” by Vidhya Sagar (that was Part 3; Part 1, Part 2 and Part 4 […]

Comments

4 responses to “Data Compression”

Leave a Reply Cancel reply