sql - 使用表压缩的缺点

Question

使用表压缩是否有任何缺点，例如 Row compression和 Page compression ， 例如:

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

如果上面的命令可以利用 sql 查询的性能，为什么我们不在每次创建新表时都使用它，即使它可能不会影响数据页很少的表。

使用这个有什么缺点吗？

谢谢

概括:
检查@paulbarbin 的答案或 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?

Answer 1

压缩确实会带来开销。完成压缩需要额外的 CPU，基于压缩的限制，您可能会发现 yield 小于痛苦。但是，据我所知，大多数人在大多数情况下都受益于页面压缩，并在特定情况下使用行压缩。我会说在您的开发/测试环境中尝试它，确定您的 CPU 成本和查询节省，并在有意义时实现。