Enable Page Level Compression on a Table

Page level compression has the benefit of generating high levels of data compression on tables with oodles of data and lots of repetitive values. I like it http://technet.microsoft.com/en-us/library/cc280464.aspx because, amongst other things, both storage and performance work well for reporting applications. The compression activity searches columns for values that can be used to reduce storage space, so it can be ideal for fact tables. Read performance can also be quite beneficial for reporting applications http://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx.

To estimate the space savings benefit of enabling page level compression use the following stored procedure.

EXEC sp_estimate_data_compression_savings 'dbo', 'SomeTable', NULL, NULL, 'PAGE' ;

To enable page compression on an existing table :

ALTER TABLE dbo.SomeTable 
REBUILD WITH (DATA_COMPRESSION = PAGE);

Remove compression on the table as follows :

ALTER TABLE dbo.SomeTable 
REBUILD WITH (DATA_COMPRESSION = NONE);

If page compression is enabled AFTER the table is fully loaded as per the above REBUILD operation, then the table is fully rebuilt with page level compression in one swoop. If the table is created with page level compression BEFORE the load, then no page compression is initiated until the load process fills the first page. Until then, only row compression occurs. When the first page is full, then page compression is initiated and additional rows compressed into that page until the next page is reached.

Read about it here http://technet.microsoft.com/en-us/library/cc280449.aspx

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

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