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.
 
 
Search WWH ::




Custom Search