Requirements: In SQL Server database, there is a table (e.g., log table, request log table) that takes up a very large disk space, maybe hundreds of GB, how to reduce the table space usage? Data compression (space-saving) can be enabled for highly repetitive text (e.g., HTML, JSON, logs).
ROW compression
Compression will change the physical storage of the data, but not the logical structure of the data. The process of line compression:
Identify the data type of each column and convert it to a variable length
Finally, the amount of storage space requests is reduced to actual demand
After compression, the amount of stored data on each page is increased
After compression, reduce the amount of metadata
For example, if the data type of a column is smallint, the system allocates 2 bytes by default
But in reality, only 1 byte of space is used, and if compression is enabled on the table, it can be allocated on demand
PAGE compression
Page compression performs additional operations to enhance row compression
Main operations: line compression, prefix compression, dictionary compression
CPU consumption: PAGE compression > row compression
Test
Create a new Users1 and Users2 tables, both insert 100,000 pieces of data, and the sql script is as follows:
To view the table space occupancy, all of which are 3.27 MB, the command is as follows:
As shown below:
To view the compression status of the Users2 table, the command is as follows:
As shown below:
sp_estimate_data_compression_savingsEstimate the savings that can occur when you enable tables or partitions for rows, pages, column stores, column store archives, or XML compression. The command is as follows:
The output is as follows:
object_name schema_name index_id partition_number size_with_current_compression_setting(KB) size_with_requested_compression_setting(KB) sample_size_with_current_compression_setting(KB) sample_size_with_requested_compression_setting(KB)
Users2 dbo 1 1 3368 1296 3448 1328
size_with_current_compression_setting (KB) 3368 KB The index occupies ≈ 3.29 MB with the current compression settings
size_with_requested_compression_setting (KB) 1296 KB Estimated footprint ≈ 1.27 MB if you enable the new compression method you request, such as PAGE or ROW
Comparison of effects:The Users2 table is 3.27 MB before compression and 1.23 MB after compression, as shown in the figure below:
The visualization is as follows:
Reference:
The hyperlink login is visible.
The hyperlink login is visible.
The hyperlink login is visible. |
Posted on 1/9/2026 11:20:06 AM
|
|
|
|
