登录查看更多内容

What are the pros and cons of row-level vs. page-level compression in SQL Server?

由人工智能和领英社区提供技术支持

Database compression is a technique that reduces the storage space and improves the performance of SQL Server databases. However, there are different types of compression methods that have different impacts on the data and the queries. In this article, you will learn about the pros and cons of row-level vs. page-level compression in SQL Server, and how to choose the best option for your scenario.

此文章中的业界达人

由社区从 4 条内容中精选。了解更多

David J. Clark

Oracle DBA/PeopleSoft Administrator
Ricardo José Leal dos Santos

Mestrado | Universidade do Estado do Rio de Janeiro

1 Row-level compression

Row-level compression is a method that reduces the size of each row by eliminating fixed-length data types, removing null and zero values, and using variable-length storage formats. This can save space for tables that have many columns, sparse data, or large fixed-length values. Row-level compression also reduces the I/O and memory usage for queries that scan or read many rows. However, row-level compression can increase the CPU overhead for queries that insert, update, or delete data, as the compression and decompression operations consume more processing power. Row-level compression also does not compress repeating values or patterns within a column or a page.

添加您的观点

2 Page-level compression

Page-level compression is a method that applies row-level compression first, and then compresses the data further by removing duplicate values within a page and using dictionary-based encoding. A page is a unit of storage that contains 8 KB of data. Page-level compression can save more space than row-level compression for tables that have many repeating values, such as lookup tables, dimension tables, or historical data. Page-level compression also reduces the I/O and memory usage for queries that scan or read many pages. However, page-level compression can increase the CPU overhead more than row-level compression, as the compression and decompression operations are more complex and intensive. Page-level compression also requires more space for storing the compression metadata and the dictionaries.

添加您的观点

3 Compression benefits

Compression benefits are the advantages that you can gain from applying either row-level or page-level compression to your SQL Server databases. These benefits include reduced storage space, improved performance, and enhanced backup and restore processes. Compression can lower storage costs and free up more space, while also improving query performance by reducing I/O and memory usage. It can also reduce the size and time of backups, while enabling compressed backup and restore options.

添加您的观点

Ricardo José Leal dos Santos

Mestrado | Universidade do Estado do Rio de Janeiro
举报内容
CPU is not a resource that the database manager usually has little and it is really worth giving up a little of what is usually left over to improve the performance of the database. In fact, compression should be standard when creating any table because of the benefits.

已翻译

赞
ANUP S.

Staff Database Performance Engineer at Wayfair
举报内容
In my experience compression is another topic that DBA has misused instead of fixing the basics. Yes it is true that if you compress the data it saves disk space and costs less not just for disk space but less expensive IO. But it is not really free. The cost has moved to the CPU. In general CPU cost is higher than disk/IO because of SQL license cost associated with it and hence I still prefer to save on CPU than disk. if (your data is not blob and will have at most 1-2 time read requests after being written to disk) or (you have business need to keep last x years of data readily available for some odd compliance reasons which will result in TB of disk space with uncompressed data) then yes using compression is useful.

已翻译

赞

4 Compression trade-offs

Compression trade-offs are the disadvantages or challenges that you can face from applying either row-level or page-level compression to your SQL Server databases. Increased CPU overhead, altered data access, and varied compression results are common trade-offs. Compression can require more processing power for compression and decompression operations, which can affect the concurrency and scalability of your database operations. It can also alter the way you access your data by changing the data types, formats, and structures, which can affect the compatibility and functionality of your applications and tools. Its effectiveness and efficiency may vary depending on the characteristics of your data, such as the data type, distribution, cardinality, and frequency of changes. Testing and tuning may be necessary to achieve optimal results.

添加您的观点

David J. Clark

Oracle DBA/PeopleSoft Administrator
(已编辑)
举报内容
The slowest part of any computing system(besides the user) is the I/O. CPU is the fastest. My experience in tuning for performance is to 1) Give the system enough free memory to allow the CPU to do compress/de-compress without causing system paging. 2) Have enough database buffer memory to allow shareable or frequently accessed data to be accessed without constant copy to/from disk, so that it stays in memory. 3) Reduce I/O waits as much as possible by spreading I/O across multiple disks.

已翻译

赞

5 Compression best practices

Compression best practices are guidelines and recommendations that you can follow to apply either row-level or page-level compression to your SQL Server databases in a proper and efficient way. To begin, evaluate your data before applying compression with tools such as the sp_estimate_data_compression_savings stored procedure, the Data Compression Wizard, or the Database Experimentation Assistant. Then, choose your compression method based on data characteristics, workload patterns, and performance goals. Finally, monitor your compression performance after applying compression with tools such as the Performance Monitor, the Dynamic Management Views, or the Query Store to measure I/O, memory, and CPU usage. This will help you identify and resolve any performance issues or bottlenecks caused by compression.

添加您的观点

Ricardo José Leal dos Santos

Mestrado | Universidade do Estado do Rio de Janeiro
举报内容
Tables that have object type data eg varchar(max), varbinary(max), xml etc are not eligible for compression and must be excluded from the script that will perform this maintenance compression.

已翻译

赞

Database Administration

+ 关注

给文章评分

我们借助人工智能创建了此文章。您认为这篇文章怎么样？

很棒不太好

举报此文章

查看全部

What are the pros and cons of row-level vs. page-level compression in SQL Server?

1

2

3

4

5

1 Row-level compression

2 Page-level compression

3 Compression benefits

4 Compression trade-offs

5 Compression best practices

Database Administration

给文章评分

感谢您的反馈

更多Database Administration相关文章

更多相关阅读内容

What are the pros and cons of row-level vs. page-level compression in SQL Server?

1

2

3

4

5

1 Row-level compression

2 Page-level compression

3 Compression benefits

4 Compression trade-offs

5 Compression best practices

Database Administration

给文章评分

感谢您的反馈

查看其他技能