Microsoft® SQL Server® 2012 Bible

1106

Part VIII: Performance Tuning and Optimization

With T-SQL, compression may be initially set when the object is created by adding the data

compression setting to the CREATE statement with the following option:

WITH (DATA_COMPRESSION = [none, row, or page])

Use the following to create a new table with row compression:

CREATE TABLE CTest (col1 INT, Col2 CHAR(100))

WITH (Data_Compression = Row);

To change the compression setting for an existing object, use the ALTER statement:

ALTER object REBUILD

WITH (DATA_COMPRESSION = [none, row, or page])

For instance, the following code changes the BillOfMaterials table to page

compression:

ALTER TABLE [Production].[BillOfMaterials]

Rebuild with (data_compression = Page);

Data Compression Strategies

Data compression is new to SQL Server and at this early stage, applying compression is more

an art than a science. With this in mind, Following are recommendations on how to best

use data compression:

1. Establish a performance baseline.


2. Use the estimate store procedure to identify tables that can benefi t (space will be
   saved) from compression.


3. Analyze application workload to determine what level of compression to use.


4. Carefully monitor the use of data compression on high-transaction tables, in case
   the CPU overhead exceeds the I/O performance gains.

In practice row compression alone might offer disk space gains up to 50 percent, but some-

times it actually increases the size of the data. If there is a signifi cant space savings and

your server can accommodate approximately an 8 percent to a 12 percent increase in CPU

usage, you should consider row compression as your compression method. Remember, you

need to consider other factors prior to holistically compressing all tables in the database.

Page compression, on the other hand, can increase CPU usage signifi cantly as compared to row

compression. As a result, it is a little more diffi cult to decide what to page compress. If you

don’t have any CPU headroom, you should avoid any compression, especially page compression.

Typical data warehouse queries and workloads are scan-intensive. Scanning these large

sets of data can be both physically and logically I/O intensive. Even though page compres-

sion may increase CPU cycles, this is often offset by the large reduction in I/O. Therefore,

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

http://www.it-ebooks.info