Sanjay Mishra just published the following White Paper, "Data Compression: Strategy, Capacity Planning and Best Practices", highly recommended and much anticipated.

On this same topic, I received a question from a colleague the other day, asking what the impact of data compression on memory was.  The assumptions I had were as follows:

  • Page or Row compression allows for more data to fit in memory (compressed page on disk matches compressed page in the buffer cache)
  • When the data is read from memory for use in a query – it is uncompressed during that time

A little unsure of the specifics, I still wanted confirmation on these assumptions, so thank you to Sunil Agarwal for confirming that this was correct.  Compressed data does reside in the buffer pool in a compressed form (which you can test in a before/after data compression using
sys.dm_os_buffer_descriptors), but when compressed data is referenced, it is uncompressed as needed.  Sanjay's paper also discusses this topic in more detail in the "Application Workload" section.