Microsoft® SQL Server® 2012 Bible

1100

Part VIII: Performance Tuning and Optimization

ber of fi xed-length columns and the actual length of the data, this level may, or may not,

provide signifi cant gain.

Although you still see the columns as fi xed length when viewing the database, under the

covers the storage engine is actually writing the values as if the columns were variable

length. A char(50) column is treated as if it’s a varchar(50) column. Therefore, if the

value stored in the column requires less than that specifi ed by the fi xed length defi nition,

the amount stored will be reduced to what is needed.

When row compression is enabled, SQL Server also uses a new variable-length row format

that reduces the per-column metadata overhead from 2 bytes to 4 bits.

Row-level data compression is designed specifi cally for third-party databases that have

several fi xed-length columns but don’t allow schema changes. In addition, you can use it on

large look up tables in operational databases — more specifi cally, on tables that require few

updates.

Page Compression

SQL Server page compression automatically includes row compression and takes compres-

sion two steps further, adding prefi x compression and then dictionary compression. Page

compression applies only to leafl evel pages (clustered or heaps) and not to the B-tree root or

intermediate pages.

Prefi x compression may appear complex at fi rst, but it’s actually simple and effi cient. For

prefi x compression the storage engine follows these steps for each column:

1. The storage engine examines all the values and selects the most common prefi x
   value for the data in the column.


2. The longest actual value beginning with the prefi x is then stored in the compres-
   sion information (CI) structure.


3. If the prefi x is present at the beginning of the data values, a number is inserted at
   the beginning of the value to indicate n number of prefi x characters of the prefi x.
   The nonprefi x portion of the value (the part to the right of the prefi x) is left in
   place.

Prefi x compression actually examines bytes, so it applies to both character and

numeric data.

For example, assume the storage engine was applying prefi x compression to the following

data, as shown in Figure 48-1.

For the fi rst column, the best prefi x is gggh. The longest value beginning with the prefi x

is ggghii, so that value is written to the CI structure, as shown in Figure 48-2. For the

second column, the best prefi x is gggg and the longest value is ggggii. The prefi xes are

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

http://www.it-ebooks.info