SQL Server 2008 Data Compression – Space savings, CPU cost, links and the new Unicode/NVARCHAR compression coming in R2 - Greg's Cool [Insert Clever Name] of the Day

Monday, August 17, 2009

SQL Server 2008 Data Compression – Space savings, CPU cost, links and the new Unicode/NVARCHAR compression coming in R2

SQL Server Storage Engine - Update on Data Compression as provided in SQL Server 2008 RTM and links to white papers

“It has been a while since I blogged about data compression so I thought it will be good to provide an update on data compression usage within SQL Server community. I am happy to say that the Data compression feature has been a tremendous success in SQL Server 2008 with customers. Many customers have been able to reduce the size of their database significantly leading to reduction in the cost of hardware and storage management. The table below shows the actual space savings that some of the customers have achieved with ROW and PAGE compression in their production environment. Couple of key observations; First, the space savings can be huge. For example, one customer has been able to achieve 81% space savings with PAGE compression. Second, not surprisingly, the PAGE compression provides better compression at the cost of additional CPU cycles. As I had emphasized in my earlier blogs, the space savings achieved will depend on the schema and the data distribution.

…

SQL Server Storage Engine - A Unicode Compression example

“Now that we have Unicode compression available in SQL Server 2008 R2 as described in, let me take a simple example using AdventureWorksDW database to show you the additional compression that can be achieved on tables with one or more columns of type NCHAR or NVARCHAR.

…”

Since SQL Server 2008 came out I’ve been trying to get it deployed, focusing on building a case for the Enterprise Edition. IMHO the security (i.e. transparent disk encryption and thereby log/backup encryption, etc) and Row/Page compression in SQL Server 2008 makes it an easy sell as we’re fighting disk usage and toward better at rest security.

Yet what’s an easy sell to me isn’t to everyone when money is tight, and Enterprise is not cheap. So over the last year and a half I have been keeping my eyes open for information to help me prove the ROI and build the business case (sigh… I’d MUCH rather be coding. Isn’t being a Manager fun sometimes?…sigh). The above posts are great for that…

When R2 ships I hope I can get a test deployment of it and see what kind of compression numbers we get… As we’ve a number of DB’s in the 1TB range, 25%-50% would result in some serious savings, both in backup time and disk space.