SQL Server Database Performance Tuning: Page Compression

Page Compression has been around since SQL Server 2008 and until ColumnStore Indexes were introduced in SQL Server 2012, it was one of the most important features that you could use to improve the performance and scalability of your  SQL Server Data Warehouse Database.

It still amazes me that in 2012 that there are SQL Server data warehouse databases that do not use Page Compression.  If you are not lucky enough to  be able to upgrade your SQL Server  data warehouse to SQL Server 2012 and take advantage ColumnStore Indexes then Page Compression is the next best thing.

The only situation were I might not recommend using Page Compression is were the SQL Server data warehouse is not I/O bound. In other words the SQL Server Data Warehouse server has more than enough throughput (MB/sec) to handle the queries. (I find this to a very rare situation. If you don’t know what your SQL Server Data Warehouse server throughput is then chances are good that I/O is a bottleneck).

Even if you are not currently  experiencing a I/O bottleneck  currently, I would bet that in the very near future that you will! A SQL Server data warehouse database never seems to be static or shrinking but they are always going!!! Keeping up with that database data growth while still maintaining good performance is one of the hardest challenges of data warehousing.
SQL Server Page Compression is a quick and easy way tune SQL Server data warehouse database performance because it has the effect of reducing server throughput requirements (MB/sec). This is done by literally moving less data (because it is compressed) from storage to the processors. But SQL Server Page Compression is not magic and so there is a cost associated with it. SQL Server Page Compression does create a little bit more CPU overhead because the data going to and from  storage has to be compressed and decompressed respectively. In my experience it increases CPU utilization by about 1%-3% but in most cases this that is a lot better than having to go out a by more storage components (e.g. RAID arrays, HBAs, New SAN, Fiber Channel Switches).

Additionally the performance boost that you get will depend on closely you are able to follow SQL Server data warehouse database best practices. Example:

  1. Star Schema (Fact and Dimension Tables)
  2. Integer Surrogate Keys for Fact Tables
  3. Use short data types in Fact Tables – No strings (char/varchar)

So let’s prove it!

Create Test Tables

select top 10000000 a.*
into PageCompression_10m
from [dbo].[FactInternetSales] a ,[dbo].[FactInternetSales] b

select top 10000000 a.*
into NoPageCompression_10m
from [dbo].[FactInternetSales] a ,[dbo].[FactInternetSales] b

ALTER TABLE [dbo].[PageCompression_10m] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE)

Query Test Tables

DBCC dropcleanbuffers;
GO
SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON;
GO
SELECT [SalesTerritoryKey], sum([SalesAmount])
FROM [dbo].[NoCompression_10m]
group by [SalesTerritoryKey]
Option (MAXDOP 8);
GO
SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO
.

/**
Table ‘NoCompression_10m’. Scan count 9, logical reads 204082, physical reads 0, read-ahead reads 204082, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0

SQL Server Execution Times:
CPU time = 8016 ms,  elapsed time = 14522 ms.
**/

DBCC dropcleanbuffers;
GO
SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON;
GO

SELECT [SalesTerritoryKey], sum([SalesAmount])
FROM [dbo].[PageCompression_10m]
group by [SalesTerritoryKey]
Option (MAXDOP 8);
GO
SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO

/**
Table ‘PageCompression_10m’. Scan count 9, logical reads 21412, physical reads 0, read-ahead reads 21412, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 5664 ms,  elapsed time = 1569 ms.
**/

Non-compressed Table:

20,4082 logical reads * 8 K / 1024 = 1,594 MB

Page Compressed Table:

21,412 logical reads * 8 K / 1024 = 167 MB

Conclusion

Page Compression increases SQL Server data warehouse performance by reducing the amount of data traveling between storage and disk. It is a free way to reduce SQL Server data warehouse server throughput requirements and should be enabled for all database fact tables and possibly large dimension tables.

See: Data Compression: Strategy, Capacity Planning and Best Practices

Advertisements

Tags:

One Response to “SQL Server Database Performance Tuning: Page Compression”

  1. Performance Tuning SQL Server Database: Extended Events | Garrett Edmondson Says:

    […] can use IO Statistics to measure a query’s MB/sec as explained here: Page Compression, but that is tedious to do for a lot of queries and doesn’t give a good idea of the other queries […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: