Compression is one of the major features introduced in SQL Server 2008, and one that can significantly reduce disk storage. Compression basically eliminates the “white space” in your data, using different internal algorithms depending on the type of compression the engine is using. There are both backup and data compression options available in SQL 2008 Enterprise Edition.
Traditionally, backup compression was the sole domain of third party software solutions such as Quest’s LiteSpeed, RedGate’s SQLBackup and Idera’s SQLSafe to name a few. These popular products are still viable and widely used in many sql server shops. Here is an article that compares the features of these major vendors that appeared sometime back on SQLServerCentral.com – “Four of a Kind – Backup Software Shootout” Again, while the new backup compression feature is only available in the Enterprise Edition, the good news is that any edition can RESTORE a compressed backup.
Data compression offers two types in SQL 2008, Row level compression and Page level compression.
Row level compression drastically reduces the meta-data needed for variable length columns, by essentially NOT storing Null and Zero values.
Page level compression uses a different algorithm and allows common data to be shared across rows. It optimizes storage of multiple rows in a page, by minimizing the data redundancy. Page compression uses two techniques called prefix compression and dictionary compression.
While compression can reduce disk space storage, increase memory utilization, and better I/O performance, there is a risk of CPU degradation. Therefore, one must take into account and consider the tradeoff between compression and performance.
So how much space can compression actually save you? Glad you asked, as there is a native SQL Server stored procedure available that can assist in this “sp_estimate_data_compression_savings”
For more info on the above-referenced stored procedure, and a good overall primer on data compression, see this good article on SQLServerCentral.com: “SQL Server 2008 and Data Compression”
Finally, as I keep in touch with many industry pros and SQL MVPS, as part of a regular feature I’d like to include in my blog is the “MVP Thoughts of the Week”, where I ask them to share some of their favorite features in SQL Server, or any topic of interest to them that they would like to share with the SQL community at-large.
One such SQL MVP, Uri Dimant, shares his thoughts and experiences on Data compression. Feel free to check out his profile on LinkedIN, and reach out to him if you are in need of any DBA expertise or project work. He is currently available on a consulting basis, whether it is locally on-site or remote.
I asked Uri, what is one of his favorite features in SQL Server 2008. Uri says:
“Yes, SQL Server 2008 is a great product. One feature that I'm really excited about is compressing. As you probably know the data compression feature in SQL Server 2008 helps compress the data inside a database, and it can help reduce the size of the database. Moreover, data compression provides another benefit: Because compressed data is stored in fewer pages, queries need to read fewer pages from the disk, thereby improving the performance of I/O intensive workloads. I have just had a client who's database is almost read only. Rebuild the huge tables to compress on PAGE level saved him almost 70 percent of IO.. and we did not compress the small tables.
If you are going to use compressing in OLTP database please take the below into account:
1) Before a page gets split, SQL Server tries to compress the page. This process is quite CPU intensive.
2) When you do an update or a delete, SQL Server will log the uncompressed record in the log file. This means that if a record lives on a page that is compressed, it will have to decompress the record.
3) For each insert that gets done, the record needs to be compressed when it is stored on a compressed page.
Microsoft has released the white kpaper about compressing , so I really encourage you read before your get in touch with the database 🙂
http://msdn.microsoft.com/en-us/library/dd894051.aspx --all about compression”
Thank you, Uri.
Look forward to bringing all of you more useful DBA tips, interesting articles, and insightful information on all things SQL. Please keep me bookmarked to see what else Pearl Knows.
-------------------------------------------------------------------------------------------------------
Coming soon! Our fully integrated SQL Server OS Live Performance Dashboard!
In the meanwhile for SQLCentric monitoring, and remote DBA services, check us out at
