使用SSMS数据压缩向导SQL Server数据压缩

Every DBA knows that database grows over time and that the growth never stops. The more data is in the database, the more time (work) SQL Server is needed in order to deal with it. From SQL Server 2008 on, new tools are available in order to help DBAs to reduce the size of database.

每个DBA都知道数据库会随着时间的增长而增长，并且增长不会停止。 数据库中的数据越多，SQL Server处理它所需要的时间（工作）就越多。 从SQL Server 2008开始，可以使用新工具来帮助DBA减少数据库的大小。

This article will describe SQL Server data compression and the SSMS Data Compression Wizard will be explained.

本文将介绍SQL Server数据压缩，并说明SSMS数据压缩向导。

The SQL Server data compression reduces the amount of physical disk space required to store data and the amount of disk I/O is saved by performing SQL Server data compression.

SQL Server数据压缩减少了存储数据所需的物理磁盘空间量，并且通过执行SQL Server数据压缩节省了磁盘I / O量。

SQL Server data compression does not compress entire database at once, instead, it can be used on the following database objects:

SQL Server数据压缩不会一次压缩整个数据库，而是可以在以下数据库对象上使用它：

• Table that is stored as a clustered index
  存储为聚集索引的表
• Table stored as a heap
  表存储为堆
• Partitioned tables and indexes
  分区表和索引
• Non-clustered index
  非聚集索引
• Indexed view
  索引视图

There are two types of SQL Server data compressions:

SQL Server数据压缩有两种类型：

• Row level data compression
  行级数据压缩
• Page level data compression
  页面级数据压缩

行级数据压缩 (Row level data compression)

Basically, row compression is the compression of data types, which means that this type of compression will take fixed character strings and store them as variable length data types and strip the blank characters.

基本上，行压缩是数据类型的压缩，这意味着这种类型的压缩将采用固定的字符串并将其存储为可变长度的数据类型，并去除空白字符。

For example, if the Char (150) data type for column is used and, for storing, the “This is test” date, only twelve characters are needed, the Row level data compression will strip the blank characters and only 12 characters are stored.

例如，如果使用了列的Char（150）数据类型，并且要存储“ This is test”日期，则仅需要12个字符，行级数据压缩将去除空白字符，并且仅存储12个字符。

Row level data compression does not store NULL or 0 values, it reduces the quantity of metadata used to store a row.

行级数据压缩不存储NULL或0值，它减少了用于存储行的元数据的数量。

More about Row level data compression can be found on the Row Compression Implementation page.

有关行级数据压缩的更多信息，请参见“ 行压缩实施”页面。

页面级数据压缩 (Page level data compression)

This type of compression offers a higher level of SQL Server data compression then the Row level data compression but CPU usage is greater.

这种类型的压缩提供了比行级别的数据压缩更高级别SQL Server数据压缩，但是CPU使用率更高。

Page level data compression starts with Row level data compression and then uses additional two compressions: prefix and dictionary compressions.

页面级数据压缩从行级数据压缩开始，然后使用其他两种压缩：前缀和字典压缩。

Prefix compression removes repeating patterns from the beginning of the values in the column and replaces it with an appropriate reference. That information is saved in the compression information (CI) structure that immediately follows the page header.

前缀压缩从该列中的值的开头删除重复的模式，并将其替换为适当的引用。 该信息保存在紧随页面标题之后的压缩信息（CI）结构中。

Dictionary compression finds the repeated values on the whole age and places them in CI area.

字典压缩查找整个年龄段的重复值，并将其放在CI区域中。

The main difference between prefix compression and dictionary compression is that the prefix compression searches for repeated data on a column while the dictionary compression searches on repeated value on the whole a data page.

前缀压缩和字典压缩之间的主要区别在于，前缀压缩在列上搜索重复数据，而字典压缩在整个数据页上搜索重复值。

More about Page level data compression can be found on the Page Compression Implementation page.

有关页面级数据压缩的更多信息，请参见页面压缩实施页面。

数据压缩向导 (Data Compression Wizard)

The SQL Server data compression can be used via SQL Server Management Studio (SSMS) Data Compression Wizard or using T-SQL.

可以通过SQL Server Management Studio（SSMS） 数据压缩向导或使用T-SQL来使用SQL Server 数据压缩 。

To compress the data using Data Compression Wizard, go to Object Explorer, find (select) a table which want to compress, right click and, from the Storage sub-menu, choose the Manage Compression command:

要使用数据压缩向导压缩数据，请转到“ 对象资源管理器” ，找到（选择）要压缩的表，右键单击，然后从“ 存储”子菜单中选择“ 管理压缩”命令：

After clicking on the Manage Compression command, the Welcome page of the Data Compression Wizard will appear:

单击“ 管理压缩”命令后，将显示“ 数据压缩向导”的“ 欢迎”页面：

Showing this page on the start of the compression process is optional. To skip this page, next time, when launch the Data Compression Wizard, check the “Do not show this starting page again” check box.

在压缩过程开始时显示此页面是可选的。 要跳过此页面，下一次在启动数据压缩向导时，选中“不再显示此起始页面”复选框。

To proceed, press the Next button. The next page that appears in the compression process is the Select Compression Type page:

要继续，请按下一步按钮。 压缩过程中显示的下一页是“ 选择压缩类型” 页：

On this page, the compression type needs to be chosen. There are three types: Row, Page, None.

在此页面上，需要选择压缩类型。 共有三种类型：行，页面，无。

If the None type is selected, then no compression will be performed.

如果选择“ 无”类型，则将不执行压缩。

If an object (table, index) is partitioned, then, to enable the compression type on all partitions, the “Use the same compression type for all partitions”check box needs to be checked. This will enable drop down box and disable the Compression type box:

如果对象（表，索引）已分区 ，则要在所有分区上启用压缩类型，需要选中“对所有分区使用相同的压缩类型”复选框。 这将启用下拉框并禁用压缩类型框：

The Partition no. box will list all partitions that exist in a table/index. This field cannot be modified.

分区号 框将列出表/索引中存在的所有分区。 此字段无法修改。

The Compression type column is for choosing a compression type for all partitions. If the “Use the same compression type for all partitions” check box is active, then the Compression type column is disabled.

压缩类型列用于为所有分区选择压缩类型。 如果“对所有分区使用相同的压缩类型”复选框处于活动状态，则“ 压缩类型”列将被禁用。

The Boundary column shows the partition boundary. The Boundary column is the read-only column; which mean that cannot be modified.

边界列显示分区边界。 边界列是只读列； 这意味着无法修改。

The Row count column shows the total number of rows in partition. The Row count column cannot be modified (read-only).

行数列显示分区中的总行数。 行数列不能修改（只读）。

The Current space column shows the size in megabytes (MB) which partition occupies. The data in this column cannot be modified (read-only).

当前空间列显示分区占用的大小（以兆字节（MB）为单位）。 此列中的数据无法修改（只读）。

The Requested compressed space column calculates the estimation size of the partition which will take after compression by using the type in the Compression type column. This value is visible after pressing the Calculate button and cannot be modified:

“ 请求的压缩空间”列将使用“压缩类型”列中的类型来计算分区在压缩后将采用的估计大小。 按下“ 计算”按钮后，该值可见，并且无法修改：

When click on the Calculate button, it calculates (estimates) the size of partitions. Depending on the value set in the Compression type column, the calculated (estimated) size will be displayed in the Requested compressed space column.

当单击“ 计算”按钮时，它将计算（估计）分区的大小。 根据“ 压缩类型”列中设置的值，计算出的（估计的）大小将显示在“ 请求的压缩空间”列中。

After the size estimation on the Select Compression Type is finished, press the Next button to continue. The next page is the Select an Output Option page:

在完成“ 选择压缩类型”的大小估计后，按“ 下一步”按钮继续。 下一页是“ 选择输出选项”页面：

On this page, it can be set how the SQL Server data compression changes will be performed by:

在此页面上，可以通过以下方式设置如何执行SQL Server数据压缩更改：

• Creating script 创建脚本
• Run immediate
  立即运行
• Schedule 时间表

After choosing how the SQL Server data compression will be performed, in this example, the Run immediate action type is selected, click the Next button to proceed.

选择执行SQL Server数据压缩的方式之后，在本示例中，选择了“立即运行”操作类型，然后单击“ 下一步”按钮继续。

The next page that appears in the Data Compression Wizard is Summary page:

数据压缩向导中显示的下一页是“ 摘要”页面：

This page shows all action the Data Compression Wizard will take after pressing the Finish button.

该页面显示了按下“ 完成”按钮后数据压缩向导将执行的所有操作。

The last page is the Compression Wizard Progress page which shows the progress information about the actions that are performed:

最后一页是“ 压缩向导进度”页面，其中显示有关所执行操作的进度信息：

Under the Report button, a couple of options exist for creating a report that contains the results of the Data Compression Wizard:

在“ 报告”按钮下，有几个选项可用于创建包含数据压缩向导结果的报告：

翻译自: https://www.sqlshack.com/sql-server-data-compression-using-the-ssms-data-compression-wizard/