1098
Part VIII: Performance Tuning and Optimization
Don’t confuse data compression with backup compression — the two technologies are completely independent.You can compress the following data objects:■ (^) Entire heap
■ Entire clustered index
■ (^) Entire nonclustered index
■ Entire indexed view (specifi cally, the materialized clustered index of an indexed
view)
■ Single partition of partitioned table or index
Although indexes can be compressed, they are not automatically compressed with the table’s compression type. All
objects, including indexes, must be individually, manually enabled for compression.
Following are data compression limitations:
■ Heaps or clustered indexes with sparse data may not be compressed.
■ (^) File stream data or LOB data is not compressed.
■ Tables with rows that potentially exceed 8,060 bytes and use row overfl ow cannot
be compressed.
■ Data compression does not overcome the row limit. The data must always be able to
be stored uncompressed.
Data Compression Pros and Cons
Data compression offers several benefi ts and a few trade-offs, so although using data com-
pression is probably a good thing, it’s worth understanding the pros and cons.
The most obvious con is the fi nancial cost. Data compression, as mentioned earlier, is only
available with the Enterprise Edition. If you already use the Enterprise Edition, great; if
not, then moving from Standard to Enterprise is a signifi cant budget request.
Data compression uses CPU. If your server is CPU pressured, then turning on data compres-
sion will probably hurt performance. Depending on the data mix and the transaction rate,
enabling data compression might slow down the application.
Not all tables and indexes compress well. Some objects can compress up to 70 percent, but
many tables see little compression, or even grow in size when compressed. Therefore, you
shouldn’t simply enable compression for every object; it takes some study and analysis to
c48.indd 1098c48.indd 1098 7/31/2012 10:24:08 AM7/31/2012 10:24:08 AM
http://www.it-ebooks.info