SQL Server Data Compression – Uncompressable Data

Data compression is a tool to improve query performance by reducing physical IO reads and writes. It can only reduce IO reads and writes when it saves space and it normally does so. I say normally because just like other performance tools it can hurt performance in some cases. As I mentioned earlier, SQL Server provides 2 types of data compression – page compression and row compression. Both of these two types of compression will consume additional space when they are implemented, normally this is called metadata overhead. The final space saving is the difference between saved space from compressed data and consumed metadata overhead space. Therefore, when metadata overhead space is greater than saved space, implementing data compression will expand actual storage space.

 

To demonstrate the growth of data space after implementing data compression, I engineered the following scenario.

 

Let’s first create a table. This table will have only one char column. Normally row compression will be able to save data space by eliminating used blank spaces, however, in this case, I intentionally left no space for row compression.

create database DCTest;

go

use DCTest;

create table table1 (msg char(36));

go

Once the table is created, I populate it with 1 million rows of fixed length random data:

–insert 1 million random records in table1

insert into table1 select NEWID()

go 1000000

Run the estimate stored procedure with both page compression and row compression and notice that the estimates show space growth:

–Get estimate on both row compression and page compression. Estimate shows table1 will grow after compresssion

exec sp_estimate_data_compression_savings ‘dbo’,’table1′,null,null,’row’

exec sp_estimate_data_compression_savings ‘dbo’,’table1′,null,null,’page’

For demonstration purposes, let’s ignore the estimates and continue implementing data compression:

–Get a baseline:

exec sp_spaceused table1

–Test row compression

alter table table1 rebuild with (data_compression = row)

exec sp_spaceused table1

–Test page compression

alter table table1 rebuild with (data_compression = page)

exec sp_spaceused table1

After executing this script, table1 now consumes more space than it did before.

 

Below is a screenshot I took from my test environment:


 

One last step before we close this topic.

–Clean up

drop table table1;

use master;

drop database DCTest;

Advertisements

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