Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have two servers. One we just compressed, but I took over this little project and this guy says some tables are not good candidates. As with many others, performance is -critical-. Is it accurate to say that some tables shouldn't be compressed? If so, what is a general threshold to look for? I ran his script to check for each objects reads/writes, but since this looks like a history, not what's occurred during a time frame, this might not be an accurate way to gauge that.

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

You can also take a look at this whitepaper written by the SQL CAT team. Take note of who all reviewed that document too, it is very well written.

The whitepaper will explain that since we are talking about data compression there is some data that will compress better than other. I believe the section on Application Workload has information for some of your questions, is goes over the performance implications found when using data compression. My advice is going to be to test it. That is really the only way you will find out for sure if it will benefit or hurt your application/system.

A snippet from the whitepaper points out some data that does not benefit from data compression:

  • Columns with numeric or fixed-length character data types where most values require all the bytes allocated for the specific data type

  • Not much repeating data

  • Repeating data with non-repeating prefixes

  • Data stored out of the row

  • FILESTREAM data

share|improve this answer
    
Awesome link Shawn, I think this is exactly what I needed to read –  ini May 25 '13 at 14:23
    
Had to research the same thing at a previous employer...came in very handy –  Shawn Melton May 25 '13 at 14:24
    
Hm, I must say - I doubt the first item. I get 75% compression ratio sometimes on tables which have only fixed length data types (numeric - financial transaction data). There is not a single variable length data type in those. –  TomTom May 26 '13 at 12:02
    
@TomTom I don't think the intent of the list in the whitepaper is to point out data that "cannot be compressed" but more towards what you do not usually get any benefit in compressing. However, every environment is different and the main reason I mentioned to test it. –  Shawn Melton May 27 '13 at 21:56
add comment

I'd like to add to Shawn's answer, that tables with frequent writes (updates, inserts, deletes) are bad candidates as well, because it takes time to apply compression for every transaction. Eg. staging tables before accepting new data should not be compressed. It is faster to load data and compress it afterwards, rather than load data into compressed table.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.