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.
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.
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.