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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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:
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.