patternsqlMinor
When should tables NOT be compressed
Viewed 0 times
tablesshouldcompressednotwhen
Problem
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.
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.
Solution
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
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
Context
StackExchange Database Administrators Q#43049, answer score: 6
Revisions (0)
No revisions yet.