Compression in real life

I have compressed my volume two notches on my belt, over a couple of months, by letting go of soft beverages and most of the candy intake, but I guess you rather read about SQL Server compression so here it comes:

You seldom see actual number crunches of real life projects on blogs since that could be sensitive information, but since I don’t reveal the companys name or anything else related to the numbers, I will not be prosecuted. I hope.

I have compressed two databases in one of the projects of a total size (sp_spaceused) of 700 GB, down to 400 GB, by using this process:

1. Use the built in estimate stored procedure to check which (or NONE) compress types (ROW/PAGE) to use on individual indexes/tables. I also used DMV’s to check for which tables were hotspots for SELECT/INSERT/UPDATE/DELETE (to additionaly pinpoint some of the tables / indexes for NONE compression). Some tables actually was estimated to grow instead of shrink in size when compressed with either ROW or PAGE.
2. Compare with a witty script which tables were compressed at least 5 % (ROW) and if PAGE was compressed 15% better than ROW chose PAGE compression instead.
3. Generate to a #temp table with another witty script the ALTER TABLE and ALTER INDEX commands that were to be executed during compression of the objects.
4. Run sp_spaceused before and after: Use a third short script to execute all the ALTER commands from the #temp table Compare the results with the estimated or with expected results. The good thing is that if you find afterwards that the compression actually is not for you, you can as easy generate ALTER scripts with NONE compression. That decompress process went 6 times as fast as compressing during tests. One hour instead of six. But overall I see a shrinkage percent of 40-60%. That is half of the disk. Omitting some production tables because they either were to small or caused cpu% usage to climb to much I ended up with a 43% decrease in disk space usage. Not bad! Then decide if you leave it that way so the datafiles can be used for a longer time before extending or if you want to shrink them. If you shrink – don’t forget to regorganize since shrinking causes fragmentation. If you haven’t already: Compress away I say. But keep watching those cpu% usage numbers. And dont hesitate to contact any of us SQL Server consultants if you need any help in the process. 

/Jonas Bergström