sql-server - 压缩数据如何存储在缓冲区高速缓存中，压缩的还是未压缩的？

Question

在 SQL Server 2008 R2 中使用行级或页级压缩时，SQL Server 是否将数据存储到其 buffer cache 中以其压缩形式或扩展形式。

例如，假设我有一个表(页面级)压缩为其原始大小的 20%:

Original size:  100 GB
Compressed size: 20 GB

此外，运行此特定 SQL Server 实例的(专用)主机拥有 24 GB 内存。如果查询执行表扫描，查看所有列(为了示例)，并且 SQL Server 将数据缓存为压缩数据，理论上它可以将所有数据存储在其缓冲区缓存中并可用于将来的查询。但是，如果数据未压缩地缓存，则 24 GB 的服务器内存显然无法容纳 100 GB 的数据。

那么，SQL Server 如何在其缓冲区缓存中存储压缩数据？

Answer 1

压缩的页面以压缩形式保留在磁盘上，并在读入内存时保持压缩状态。

引用号: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.