SQL Server 2008 Data Compression Example

, 2008-10-31

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 :

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads