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.

The final version of the presentation is here.

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