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

2 Answers 2

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

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

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.
{"cc_re__cc_re__cc_by_sa_3_0": ["sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com?utm_source=stackoverflow.com&amp;utm_medium=site-ui&amp;utm_campaign=footerlink\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2014 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a>                   with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2014.11.25.2047\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Database Administrators Stack Exchange works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" clas"], "copyright__copyright__copyright": ["erts, 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.\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}