[dba-SQLServer] Data compression

jwcolby jwcolby at colbyconsulting.com
Sat Jun 19 21:34:58 CDT 2010


I tested building a table with page compression.  It is is a duplicate of an existing table that I 
have never used compression with.  It has four indexes, which I also created with compression.

The space usage is as follows:

Row Count: 67,564,677

With Compression:
Index space: 10,082.422 MB
Data Space: 7,010.180 MB

Without Compression:
Index space: 18,431.859 MB
Data Space: 13,785.570 MB

That is significant.  The common wisdom is that compressed data takes less disk I/O at the expense 
of more CPU usage.  Likewise less memory is used because the data is kept in memory compressed.

This is one of my central tables.  When used, this table is always joined on another table (or view 
into a table), sometimes on a PKID, sometimes on a SHA hash field.

-- 
John W. Colby
www.ColbyConsulting.com



More information about the dba-SQLServer mailing list