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 :