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.