Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server 2008 Data Compression Example

I was experimenting with the effects of row and page compression in SQL Server 2008 Enterprise Edition.

First, I identified a row in a production database called so_hdr_tbl that I wanted to compress. Before compressing it, I checked the size of the table, and it had 54,840 rows and took up 32.094 MB of space.

Second, I ran a query against the database (see below) and noted that it took 3,293 logical reads to execute the query.

Third, I compressed the table using row compression. After compression, the table took up only 18.883 MB of space, a 42% reduction in size of the original table.

Fourth, I ran the same query on the row compressed table, and this time it only took 1,804 logical reads to execute the query.

Fifth, I compressed the table using page compression. After compression, the table took up only 9.688 MB of space, a 70% reduction in size of the original table.

Sixth, I ran the same query on the page compressed table, and this time, it only took 937 logical reads to execute the query.

Seventh, I uncompressed the table and returned it to its original size. Then I ran the same query, and it now took the original 3,293 logical reads to execute the query.

The simple example shows the dramatic potential that data compression in SQL Server 2008 has to offer. Of course, the results you get will depend on the data that is being compressed. In my particular case, the data compressed very well and provided improved query performance.

 

--Page and Row Compression Demo

USE Big_Database
GO

SET STATISTICS IO ON
SET STATISTICS
TIME ON
GO


--Run query against table before any compression
SELECT  so_hdr_tbl.gl_cmp_key,
       
so_hdr_tbl.so_brnch_key,
       
so_hdr_tbl.so_hdr_key,
       
so_hdr_tbl.en_cust_key,
       
so_hdr_tbl.ar_bill_key,
       
so_hdr_tbl.ar_bill_adkey,
       
so_hdr_tbl.ar_ship_key,
       
so_hdr_tbl.ar_ship_adkey,
       
so_hdr_tbl.ar_ship_adtxt,
       
so_hdr_tbl.so_hdr_revno        
FROM    dbo.so_hdr_tbl
GO

--(54840 row(s) affected)
--Table 'so_hdr_tbl'. Scan count 1, logical reads 3293, 
--physical reads 0, read-ahead reads 0, lob logical reads 
--0, lob physical reads 0, lob read-ahead reads 0.

--SQL Server Execution Times:
--CPU time = 78 ms,  elapsed time = 1106 ms.


--Turn on row compression
USE [Big_Database]
ALTER TABLE [dbo].[so_hdr_tbl] REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = ROW)
GO

--Run same query as before
SELECT  so_hdr_tbl.gl_cmp_key,
       
so_hdr_tbl.so_brnch_key,
       
so_hdr_tbl.so_hdr_key,
       
so_hdr_tbl.en_cust_key,
       
so_hdr_tbl.ar_bill_key,
       
so_hdr_tbl.ar_bill_adkey,
       
so_hdr_tbl.ar_ship_key,
       
so_hdr_tbl.ar_ship_adkey,
       
so_hdr_tbl.ar_ship_adtxt,
       
so_hdr_tbl.so_hdr_revno        
FROM    dbo.so_hdr_tbl
GO

--(54840 row(s) affected)
--Table 'so_hdr_tbl'. Scan count 1, logical reads 1804, 
--physical reads 0, read-ahead reads 0, lob logical reads 
--0, lob physical reads 0, lob read-ahead reads 0.

--SQL Server Execution Times:
--CPU time = 78 ms,  elapsed time = 1089 ms.


--Turn on page compression
USE [Big_Database]
ALTER TABLE [dbo].[so_hdr_tbl] REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = PAGE)
GO


--Run same query as before
SELECT  so_hdr_tbl.gl_cmp_key,
       
so_hdr_tbl.so_brnch_key,
       
so_hdr_tbl.so_hdr_key,
       
so_hdr_tbl.en_cust_key,
       
so_hdr_tbl.ar_bill_key,
       
so_hdr_tbl.ar_bill_adkey,
       
so_hdr_tbl.ar_ship_key,
       
so_hdr_tbl.ar_ship_adkey,
       
so_hdr_tbl.ar_ship_adtxt,
       
so_hdr_tbl.so_hdr_revno        
FROM    dbo.so_hdr_tbl
GO
--(54840 row(s) affected)
--Table 'so_hdr_tbl'. Scan count 1, logical reads 937, 
--physical reads 0, read-ahead reads 0, lob logical reads 
--0, lob physical reads 0, lob read-ahead reads 0.

--SQL Server Execution Times:
--CPU time = 31 ms,  elapsed time = 1117 ms.


--Remove Compression From Table

USE [Big_Database]
ALTER TABLE [dbo].[so_hdr_tbl] REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = NONE)
GO


--Run query against table now that compression has been turned off
SELECT  so_hdr_tbl.gl_cmp_key,
       
so_hdr_tbl.so_brnch_key,
       
so_hdr_tbl.so_hdr_key,
       
so_hdr_tbl.en_cust_key,
       
so_hdr_tbl.ar_bill_key,
       
so_hdr_tbl.ar_bill_adkey,
       
so_hdr_tbl.ar_ship_key,
       
so_hdr_tbl.ar_ship_adkey,
       
so_hdr_tbl.ar_ship_adtxt,
       
so_hdr_tbl.so_hdr_revno        
FROM    dbo.so_hdr_tbl
GO

--(54840 row(s) affected)
--Table 'so_hdr_tbl'. Scan count 1, logical reads 3293, 
--physical reads 0, read-ahead reads 0, lob logical reads 
--0, lob physical reads 0, lob read-ahead reads 0.

--SQL Server Execution Times:
--CPU time = 87 ms,  elapsed time = 1049 ms.

 

 

 

Share this post :

Comments

Posted by DBA_Rob on 3 November 2008

Because of compression I certainly expect the logical reads to be reduced. What I find more interesting is the CPU and elapsed times. The page compression yields more than 50% reduction in CPU time, an no meaningful difference in elapsed time.  The row compression had no difference in CPU time or elapsed time.  Based on the number of rows, a much larger sample (54 million ?) would be needed to see any meanigful differences.  All other things equal though, a 70% reduction in disk space is beneficial itself.

Posted by Brad M. McGehee on 3 November 2008

My test was on my 2-core laptop, local hard drive, and Vista, hardly a fair test. Each time I ran the above tests, the CPU time and duration jumped around quite a lot, most likely in response to other activities going on on the laptop at the same time. The focus of this example was on data compression and logical reads. A real test of performance would require a much better hardware setup than what I used.

Posted by Hugo Shebbeare, SQL Server [mc]DBA , CDP Capital / on 12 December 2008

I tested for many months a similar type of vardecimal compression in TST/DRP environment on sql 2005 sp2 cu5 onwards, the db was 1.4 TB. The number of rows in the main table was over 500M rows - after the conversion just on a few columns involved, the data was half the size and queries took only 40% the regular execution time.

It's as if Financial Institutions made a specific request from MSFT to optimise their queries - pretty impressive results.

Posted by Jeff Brinker on 30 June 2009

I just began looking at page compression over the weekend, ran similar tests and my results were in line with those mentioned above.

I am left to wonder if we are all missing other hidden costs and am hesitant to right in and implement this rather broadly across our data warehouse.  Has anyone encountered sustantively different results in the past six months or run across additonal testing methodologies for page compression?

Posted by Brad M. McGehee on 30 June 2009

You might want to check out Linchi Shea's blogs on this topic at:  sqlblog.com/.../sql-server-2008-page-compression-using-multiple-processors.aspx and  sqlblog.com/.../sql-server-2008-page-compression-compression-ratios-from-real-world-databases.aspx

Microsoft also has a good article at: msdn.microsoft.com/.../dd894051.aspx

In any event, I think testing (taking performance baselines before and after compression) your own data in your own environment is the only way to know for sure if compression will work well for you.

Leave a Comment

Please register or log in to leave a comment.