Microsoft® SQL Server® 2012 Bible

1102

Part VIII: Performance Tuning and Optimization

As demonstrated, depending on the commonality of the data set, prefi x compression can

signifi cantly compress the data without any loss of data fi delity.

In this example, one value in the second column, hhhh, doesn’t match the prefi x at all.

It’s stored as 0hhhh, which increases the length. If this is the case for most of the rows,

and prefi x compression offers no benefi t for a given column, the storage engine leaves the

anchor row null and does not use prefi x compression for that column. This is one reason

why sometimes tables actually grow when compressed.

After the data is prefi x compressed, the storage engine applies dictionary compression. Every

value is scanned and any common values are replaced with a token that is stored in the

compression information area of the page. Prefi x compression occurs on the column level,

whereas dictionary compression occurs across all columns on the page level.

Data warehouses are good candidates for this level of compressions. Because the data in a

warehouse is typically updated infrequently, the cost of decompressing it during updates is

minimized. Therefore, you can apply page compression to the entire data warehouse — con-

sidering only those tables that are eight pages or larger. However, as with any new imple-

mentation, you should fully test this scenario before applying it to your production system.

Compression Sequence

The cool thing about data compression is that it’s completely handled by the storage engine

and transparent to every process outside of the storage engine. This means that the data

is compressed on the disk and is still compressed when it’s read into memory, as previously

mentioned. The storage engine decompresses the data as it’s passed from the storage engine

to the query processor, as shown in Figure 48-3.

FIGURE 48-3

The storage engine compresses and decompresses data as it’s written to and read from

the buffer.

Relational

Database

Engine

Query Optimizer

Query Processor

Data Compression

Storage Engine (buffer) Disk or SAN

c48.indd 1102c48.indd 1102 7/31/2012 10:24:09 AM7/31/2012 10:24:09 AM

http://www.it-ebooks.info