Database Reference
In-Depth Information
A
Header
is a bitmask that indicates the CI record version and if it has an anchor record and
dictionary entry present.
The
PageModCount
indicates the number of changes on the page after it has been
compressed. SQL Server tries to recompress the page and rebuild the CI records, either
after 25 modifications or when number of modifications exceeds 25 percent of the number
of rows on the page.
Offsets
is the array that stores the beginning and ending offsets of the anchor record and
dictionary entry in the CI record.
The
Anchor
Record is another row in CD format, with each column representing the anchor
value for a particular table column.
The Dictionary
stores an array of dictionary entries and consists of three parts: number of
entries, their offsets, and actual values.
As already mentioned, SQL Server stores the data in page compression format only when the page is full and
compression leads to significant space savings. When a page is full, SQL Server performs compression and evaluates if
the newly compressed page can store either 5 more rows or 25 percent more rows than before the compression. If that
is the case, the page compression is retained and SQL Server stores the data in page compressed format. Otherwise,
the page compression is discarded and data is stored in row compression format.
The same process occurs when
PageModCount
in the CI record exceeds the threshold. SQL Server tries to
recompress a page, evaluating the space savings and either keeping or discarding results.
■
You can see page compression statistics in the
page_compression_attempt_count
and
page_compression_
success_count columns
in the
sys.dm_index_operational_stats
dMF.
Note
Finally, neither transaction log records for data modifications nor version store in
tempdb
supports page
compression. SQL Server needs to decompress the page and remove anchor and dictionary records every time a row
needs to be placed in version store or written to the transaction log. This can introduce an additional performance
impact when optimistic isolation levels or
AFTER
triggers are used, or when compressed data is frequently modified.
■
We will discuss version store in more detail in Chapter 8, “triggers” and Chapter 21, “optimistic isolation levels.”
transaction log internals are covered in Chapter 29, “transaction log internals.”
Note
Performance Considerations
Data compression can significantly reduce storage space for data at the cost of extra CPU load. SQL Server needs
more time to access row data regardless of the compression type used. It does not necessarily mean that the query
execution time would increase, as in some cases queries will work even faster due to fewer data pages to scan and less
I/O reads to perform. However, the performance of batch data modifications and index maintenance routines could
be negatively affected.
Let's do some tests and check out how data compression affects storage size and execution time of queries.
I am using the data from one of the production tables with a decent number of fixed- and variable-length columns.
Obviously, different table schema and data distribution will lead to slightly different results. However, in most cases,
you would see similar patterns.