SQL Server 2008 Compression & Fragmentation

Few weeks back  I was evaluating implementing database compression in one of our data warehouse project. The first thought that came to my mind was how does compression impacts the fragmentations, because we had a performance issue with the index due to DBCC SHRINK. If you are not aware of this, pls go thru the below article which explains how DBCC SHRINK impacts your indexes and in-turn your application’s performance.

http://www.sqlfundas.com/post/2009/09/26/Understanding-Logical-Fragmentation-e28093-Series-2.aspx

I wanted to be doubly sure that compression does not do the same with my indexes. I flipped thru the web but could not find any reference to this effect. So started to test it out by myself.

The approach was like

Create a new database called TEST_COMPRESSION.

Create a table and a non clustered index. Load 1L records. Note down the fragmentation.

Enable PAGE Compression. Check the fragmentation.

Enable ROW Compression. Check the fragmentation.

Set Compression to NONE and check the attribute.

The table script and index fragmentation before compression was as below

 

While enabling PAGE compression induced some fragmentation, ROW compression returned zero fragmentation.

Decompressing it by setting the compression to NONE, brought the index fragmentation back to the state before compression.

In the above approach I moved from NONE -> PAGE ->ROW -> NONE. In that case  ROW compression did not have any fragmentation at all.

Another path ,i.e. NONE -> ROW ->PAGE-> NONE gave bit different result for ROW compression.

In a relative note, I was curious to see what would be the impact of compressed backup option on fragmentation.  I took a compressed backup and restored it with new name and checked the fragmentation. But did not see anything amiss.

While backup compression does not cause fragmentation, seems like data compression cause some fragmentation. Since this is very negligible, we can safely ignore it.

Though I would agree that compression cannot be generic and depends on the data, I dont see any issue with compression w.r.to fragmentation.

Advertisements

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in T-SQL. Bookmark the permalink.

One Response to SQL Server 2008 Compression & Fragmentation

  1. Ganesh says:

    Nice Articlae Suresh ..Thanks

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