How is compressed data stored in the buffer cache, compressed or uncompressed?

Question

When using row-level or page-level compression with SQL Server 2008 R2, does SQL Server store the data into its buffer cache in its compressed form or its expanded form.

For example, let's say I have a table that is (page-level) compressed down to 20% of its original size:

Original size:  100 GB
Compressed size: 20 GB

Furthermore, the (dedicated) host that this particular instance of SQL Server is running on has 24 GB of memory. If a query performs a table scan, looking at all columns (for the sake of example) and SQL Server caches the data as compressed, theoretically it can have all of the data in its buffer cache and available for future queries. If the data is cached uncompressed, however, clearly 100 GB of data cannot fit into 24 GB of server memory.

So, how does SQL Server store compressed data in its buffer cache?

Answer 1

Compressed pages are persisted as compressed on disk and stay compressed when read into memory.

Ref: SQL Server 2008 Data Compression: Strategy, Capacity Planning and Best Practices:

Data is decompressed (not the entire page, but only the data values of interest) when it meets one of the following conditions:

• It is read for filtering, sorting, joining, as part of a query response.
• It is updated by an application.

There is no in-memory, decompressed copy of the compressed page. Decompressing data consumes CPU. However, because compressed data uses fewer data pages, it also saves:

• Physical I/O: Because physical I/O is expensive from a workload perspective, reduced physical I/O often results in a bigger saving than the additional CPU cost to compress and decompress the data. Note that physical I/O is saved both because a smaller volume of data is read from or written to disk, and because more data can remain cached in buffer pool memory.

• Logical I/O (if data is in memory): Because logical I/O consumes CPU, reduced logical I/O can sometimes compensate for the CPU cost to compress and decompress the data.