I recently came across a situation that really surprised me. For a table with 11.2 million rows and a poor design, I figured turning on data compression would provide a performance boost until a design improvement could be implemented.

The table has a PK with 6 columns, 4 of them are varchar(100).

CREATE TABLE [dbo].[some_narley_table](
[EntityIDChild] [varchar](100) NOT NULL,
[EntityIDParent] [varchar](100) NOT NULL,
[EntityIDSource] [varchar](100) NOT NULL,
[TestIDSource] [int] NOT NULL,
[GLAccountID] [varchar](100) NOT NULL,
[RelationshipChainID] [int] NOT NULL,
[AmountUSD] [money] NULL,
[AmountLocal] [money] NULL,
[RelationshipCodeID] [varchar](100) NULL,
[EntityChildCurrencyID] [int] NULL,
[EntityChildAmount] [money] NULL,
[EntityParentCurrencyID] [int] NULL,
[EntityParentAmount] [money] NULL,
[EntitySourceCurrencyID] [int] NULL,
[EntitySourceAmount] [money] NULL,
[OriginalAmount] [money] NULL,
[RoundingVariance] [decimal](25, 5) NULL,
CONSTRAINT [PK_some_narley_table] PRIMARY KEY NONCLUSTERED 
(
[EntityIDChild] ASC,
[EntityIDParent] ASC,
[EntityIDSource] ASC,
[TestIDSource] ASC,
[GLAccountID] ASC,
[RelationshipChainID] ASC
)
)

I ran sp_estimate_data_compression_savings on the heap table, and found a compression ratio of 7.0 with page compression; and 1.5 compression ratio for row compression. So we applied page compression, since it showed the best potential savings, and ran some tests.

There was no significant improvement. On both OLTP and report workloads using this table that ran for a few minutes each, only one or two second improvements were observed on each.

With the help of a Microsoft DSE, this explanation makes sense. The version store, which uses tempdb, keeps copies of rows to support how snapshot isolation and READ-COMMITTED SNAPSHOT work. The version store does not support page compression, so any rows on a compressed page are uncompressed to be stored in the version store. The version store does support row compression.

In summary, when snapshot isolation is used, if your goal is to reduce disk space usage, page compression may be worth it, but if your goal is a performance improvement, page compression is not recommended. It may be worth testing with row compression.

With this limitation in mind, and the following link on how to decide which tables to compress, you will be well prepared to make some good choices. But as always, test, test, test.

Link to Microsoft white paper “Data Compression: Strategy, Capacity Planning and Best Practices” , which provides some great details about compression and guidance on how to determine what tables and indices to compress, considering workspace, CPU, I/O, reclaiming space, application performance, partitions, TDE, and more. http://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx