Yesterday, I drove the Smart down to Burlington/Colchester, Vermont to meet up with MVP Roman Rehak and speak to the local user group about SQL Server Row, Page and VarDecimal compression, originally touched during this post.
I had much more performance gains on the SAN before using vardecimal, so for those of you with SSDs, perhaps the test scripts may not really show a big difference for the SELECT times...however, at least major disk space will be gained. In the tests, after populating the tables from RedGate's Data Generator, we started with a table of 260MB and ended up dropping its size down to 80MB.
Here are the essential parts of the script for you to test out compression on your own databases:
-- all SQL Server internal compression is done at the table level -- testing started on a table that was 260MB, with a check on the storage used each time -- I/O , according to the Actual Execution Plan for a normal SELECT started at 24.17 (no compression) --first round of compression ALTER TABLE schema.TableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW) -- table became 180MB --second round of compression ALTER TABLE schema.TableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE) -- table now down to 80MB -- I/O cost, according to the Actual Execution Plan for a SELECT down to 7.62 (just under a third) -- after all your compression work is done, run a single console command DBCC SHRINKDATABASE(name,0) – replacing the zero with the amount you want to leave free