1099
Chapter 48: Data Compression
48
choose compression wisely. More specifi cally, data that contains the following data patterns
are not good candidates for compression:■ (^) FILESTREAM data
■ Data that has nonrepeating prefi xes
■ (^) Data that does not have many repeatable values
■ Fix-length character data types whose value consumes all the bytes available for
the specifi ed data type
With these possible drawbacks understood, plenty of reasons exist to enable data compres-
sion (assuming the data compresses well):
■ Data compression signifi cantly reduces the I/O bottleneck for a high-transaction
database.
■ Data compression signifi cantly reduces the memory footprint of data because the
data remains compressed in memory, thus increasing the amount of data that can
cache in memory and probably improving overall performance.
■ (^) More rows on a page mean that scans and count(*) type operations are faster.
■ Compressed data means SAN shadow copies are smaller.
■ (^) Database snapshots are smaller and more effi cient with data compression.
■ SANS and high-performance disks are expensive. Compressed data means less disk
space is required, which means more money is left in the budget to attend a SQL
Server conference in Maui.
■ (^) Compressed data means backup duration and restore duration is reduced, and less
storage space is used for backups.
Hardware-based data compression solutions compress data as it’s written to disk. Although these can reduce disk
space and off-load the CPU overhead of compression, they fail to reduce the I/O load on SQL Server, or reduce the
data’s memory footprint within SQL Server.
Two types, or levels, of data compression exist in SQL Server 2012: row level and page level.
Each has a specifi c capability and purpose. So that you can best understand how and when
to employ data compression, the following sections describe how they work.
Row Compression
Row compression converts the storage of every fi xed-length data type column (both charac-
ter and numeric data types) to a variable-length data type column. Row compression grew
out of the vardecimal compression added with SQL Server 2005 SP2. Depending on the num-
c48.indd 1099c48.indd 1099 7/31/2012 10:24:09 AM7/31/2012 10:24:09 AM
http://www.it-ebooks.info