Storage Crisis? Compression is here to bail us out

Data Compression is available at last in SQL Server 2008. Even Backup Compression is available. This will help us save space and time. So maybe my comment about comparing storage and the financial credit market was a little hasty. Looks like Microsoft is taking some proactive steps to bail us out of this storage crisis before it happens. We have two types of compression available in SQL Server 2008: Data Compression and Backup Compression. Let's take a look...

Data Compression can be performed on tables, indexes and partitions. It can be defined at the row level or at the data page level. Both levels of data compression work especially well with variable length columns. Page level compression will also provide savings based on column values that start with similar data. You can estimate and enable data compression from T-SQL but the easiest way is through the Data Compression Wizard which can be started from SSMS , for instance, by right-clicking a table and selecting "Storage" then "Manage Compression..." from the context sensitive menu. Testing this out with the FactInternetSales table in the AdventureWorksDW sample database I could see that row level compression would reduce the space requirements of that table down to around 12MB from 16MB. With page level compression that estimate dropped to 8MB.  The trade-off is the extra processing to de-compress the data but this is rated as minimal overhead.

Backup compression can be configured at the Server level. It is switched off by default but can be turned on as the default using sp_configure or SSMS. The default can be overridden on a backup by backup basis, if necessary. In my tests, I backed up the AdventureWorks2008 database and the .bak file took up 187MB uncompressed. When using backup compression it came down to 45MB. Pretty impressive. That translates to around 75% compression. And of course, the backup ran faster too.

All in all, these new compression features in SQL Server 2008 are very positive with little or no overhead. Plenty of upside and no downside. Looks like the bailout will work. Now that's what I'm talking about!

Cheers

Brian

Recent posts

Merger mania with SQL Server 2008

Data Partitioning is even better in 2008

Change Data Capture in SQL Server 2008

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.
Related:

Copyright © 2008 IDG Communications, Inc.