Thursday, 2 July 2009

SQL 2008 Table Compression - Real World Tryout

Taking an existing import system, I ran the same test 3 times against an empty databases.
The database was presized to 25MB and shrunk afer each test so that data growth operations would not effect timings.

The test was simply running some existing code for 1 iteration.
It inserted 76015 rows across 15 separate tables.

  • Data Size : 14656 KB
  • Duration : 79s (962 rows/second)
Row Compression
  • Data Size : 12224 KB (83.4%, saving of 16.6%)
  • Duration : 82s (increase of 4%, 927 rows/second)
Page Compression
  • Data Size : 8512 KB (58.1%, saving of 41.9%)
  • Duration : 87s (increase of 10%, 873 rows/second)
So, substantial disk savings can be had using page based compression at a cost of 10% in import throughput.

This is purely to demonstrate the effects of compression.
The figures are only representative of the test system.

Further efficiency gains can be made with the import
  1. simplifying it
  2. making import processes run in parallel.
  3. placing it on a faster disk subsystem

No comments: