SQL Server
When you think about data compression, the first reaction is: don’t do it. Usually the trade-off of extra performance cycles needed to de-compress the data is not worth the space saved. SQL Server 2008 Data Compression is different. The Data Compression feature in SQL Server saves space at the Row level or the Page level. Row level compression looks for fixed data types that contain values that do not use the full width of the data type and replaces the wasted space with a compression code. Page level compression is a superset of Row level compression with the added benefit of compressing repetitive data in different rows on the same data page. You can compress tables, indexes and partitions on a case by case basis. Some data objects benefit more than others based on the data values stored. One way to forecast the savings is to use the Data Compression wizard in SSMS which will give you an estimate based on Row or Page level compression. Just right-click on the table or index and choose Storage/Manage Compression. Or use the stored procedure sp_estimate_data_compression_savings. Extra CPU cycles are needed to compress and decompress the data but according to SQLCAT.com the decompress overhead is usually below 10% based on real customer experiences. With Page level compression you may save over 50% in storage which actually reduces the number of data pages needed and therefore reduces I/O. So a little bit of extra CPU may give you a big net gain in performance based on reduced I/O and memory usage. With less storage. A major drawback is that you will need to use SQL Server 2008 Enterprise (or Developer) Edition since data compression is not compatible with other editions or releases. So replicating down-level will not work. Replicating with other editions will not either. You will have to go without data compression or pay up the big bucks. Another drawback is that large object data types stored outside of the row are not compressed. That includes Filestream data. SQLCAT.com has a great white-paper which is now published on MSDN entitled “Data Compression: Strategy, Capacity Planning and Best Practices”. Here’s the URL: http://msdn.microsoft.com/en-us/library/dd894051.aspx Of course, everything needs to be tested with your real data. As they say, “actual results may vary”. Sounds like one of those weight-loss commercials, doesn’t it? With some dedicated planning and effort you may just give your data a new lease on life. cheers Brian
