One of the great new features of SQL Server 2008 is data compression which actually reduces storage and speeds up access due to less IO – a surprising combination. Normally compression strategies mean poor performance trade-offs, but not here. SSMS 2008 also has a useful wizard to help us decide on the level of compression we can choose.
SQL Server 2008 supports two levels of data compression – row and page level. There is also backup compression which is completely separate but very useful too. Row level compression looks at each column in a row and attempts to compress based on the data not filling the space provided for the column. Page level compression includes the features of row level compression but also looks at multiple rows on a data page for repetitions that can be replaced by a single code and save space that way. Both methods are handled transparently by SQL Server so that no existing code needs to change.
Each method saves space and also reduces I/O and processing time so that performance is enhanced. A great way to assess whether to use data compression and at what level is hidden away within SSMS 2008. Just right-click on your table and choose Storage then “Manage Compression”. This launches the Data Compression Wizard.
For instance, if you do this for the FactInternetSales table in the AdventureWorksDW database you will be able to calculate the space savings for both row and page level compression. By default, the 60,000 rows take up 17MB. Row level compression brings it down to 12.3MB. Page level compression brings it down to 8.6MB which is around 50% in storage savings. Not bad. You can then have the wizard generate a script to enable the compression or have it compress the table immediately.
I ran a simple query to get all the rows in the table both before and after the compression and the execution plan showed a subtree cost of .91 milliseconds before compression and .36 after. That’s quite impressive. Normally we have to make a choice between performance and storage but not in this case. The only bad news here is that compression is an Enterprise Edition feature so you have to pay for it. Oh well, you can’t win them all.
Cheers
Brian
Brian D. Egler, MCITP/MCSE/MCT 2009, is currently an instructor with Global Knowledge, teaching various Microsoft training courses. He is a SQL specialist with a focus on SQL Server, Windows, .Net and XML. Egler has been a technical instructor for over 20 years and has more than 10 years experience with SQL Server, data modeling, database design, application development including IMS, DB2, Sybase. Every year he runs the Boston Marathon for cancer research.
The Leader in Network Knowledge
Post new comment