Possible to unCompress or deCompress compressed table and index in SQL 2008 R2?

Question

Possible to unCompress or deCompress compressed table and index in SQL 2008 R2?

Express12

SSCoach

Points: 18514
More actions
October 9, 2014 at 8:57 am

#291462

New EMC architecture offering De-Dup requires avoidance of SQL Server data compression.
We've compressed dozens of tables and hundreds of indexes in our SQL 2008 R2 prod environment across many servers and DB's.
What is the process to unCompress or deCompress these objects?
BT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

ALTER INDEX or ALTER TABLE with the rebuild clause.

Dedupe on the data files? That's got to be a violation of the IO requirements SQL has (once written data is not changed). I can understand dedupe for backups, but for the data files?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Express12 SSCoach Points: 18514 More actions · Answer 2

thx for the feedback. btw, my initial post indicated EMC. That was incorrect. This is for PureStorage.com (Flash storage) -- another storage outfit which utilizes FLASH storage.

PureStorage can accommodate/house SQL compressed objects but mentioned that's a waste of SQL Server CPU. Their technology will perform both compression and de-duping as the data is written to their flash.

BT

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

But you still lose the advantage of compressed pages in memory.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Discontinued Account 99 SSC Journeyman Points: 89 More actions · Answer 4

We have Pure Storage arrays in our environment, and we actually have a lot of (big) indexes that are PAGE compressed. Even though Pure dedupes and compresses the data, we still see a performance increase from table compression.

For our particular workload, which is NOT CPU intensive, we see buffer pool usage benefits, and also queries have sped up as a result of leveraging it.

I will not make a blanket statement to go ahead and enable compression on all of your indexes -- because all workloads are different -- but I would certainly encourage people using Pure arrays to give compression a test drive and see if it's good for them.

I will also not make a blanket statement to turn off compression just because your backend is doing it for you - like I said, YMMV. Test, test, test. It's most definitely still an option on flash and deduped arrays.

Hope my experience helps you here.

Argenis Fernandez

SQL Server MVP and MCM

@DBArgenis

http://www.sqlblog.com/blogs/argenis_fernandez/

ps: It had been a while since I had logged on to SSC...