CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
LONG
LONG

Reputation: 4620

Disadvantages of using table compression

Are there any disadvantages of using table compression such as Row compression and Page compression, for example:

ALTER TABLE A
REBUILD WITH (DATA_COMPRESSION = PAGE)  --or ROW

If the above command could leverage the performance of the sql query, why don't we use that every time we create a new table even though it may not effect a table with few data pages.

Any disadvantages of using this?

Thanks

Summary: check either @paulbarbin's answer or check the conclusion part of this post here

As we can see, row- and page-level compression can be powerful tools to help you reduce space taken by your data and improve the execution speed, but at the expense of CPU time. This is because each access of a row or page requires a step to undo the compression (or calculate and match hashes) and this translates directly into compute time. So, when deploying row- or page-level compression, conduct some similar testing (you are welcome to use my framework!) and see how it plays out in your test environment. Your results should inform your decision - if you're already CPU-bound, can you afford to deploy this? If your storage is on fire, can you afford NOT to?

Upvotes: 5

Views: 8375

Answers (2)

Shiwangini
Shiwangini

Reputation: 836

when a page level compression gets applied to a table,row level compression get also applied.The benefits of page compression depend on the type of data compressed. Data that involves many repeating values will be more compressed than data populated by more unique values.One more thing, Data compression change the query plan because the data is compressed in different number pages and rows.Additional CPU requires to retrieve compressed data exist. I suggest, go with the compression only when you have a big warehouse table that contain millions of records and you/your application don't need to query the table frequently. You can also use partition level compression when it's partitioned table.

Upvotes: 1

paulbarbin
paulbarbin

Reputation: 382

Compression does come with an overhead. There is additional CPU required to complete the compression and based on the limitations of compression, you might find that the gain is less than the pain. However, it's my understanding that most people benefit from page compression for most scenarios and use row compression in specific circumstances. I'd say try it in your dev/test environment, determine your cost on CPU and savings in queries and implement if it makes sense.

Upvotes: 4

Related Questions