通过

sp_estimate_data_compression_savings (Transact-SQL)

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例

返回所请求对象的当前大小并估算对象在所请求的压缩状态下的大小。 可对所有表或部分表评估压缩。 这包括堆、聚集索引、非聚集索引、列存储索引、索引视图以及表和索引分区。 可以使用行、页面、列存储或列存储存档压缩来压缩对象。 如果表、索引或分区已压缩，则可以使用此过程来估算表、索引或分区的大小（如果重新压缩或存储而不压缩）。

系统sys.sp_estimate_data_compression_savings存储过程在Azure SQL 数据库和Azure SQL 托管实例中可用。

从 SQL Server 2022（16.x 开始），可以使用 xml 数据类型压缩列中的行外 XML 数据，从而减少存储和内存需求。 有关详细信息，请参阅 CREATE TABLE 和 CREATE INDEX。 sp_estimate_data_compression_savings 支持 XML 压缩估计。

注意

压缩， sp_estimate_data_compression_savings 在 SQL Server 的每个版本中都不可用。 有关 SQL Server 各个版次支持的功能列表，请参阅 SQL Server 2022 的各个版次及其支持的功能。

若要估计对象的大小（如果使用的是请求的压缩设置），此存储过程将采样源对象，并将此数据加载到在其中创建的等效表和索引中 tempdb。 然后，创建的 tempdb 表或索引将压缩到请求的设置，并计算估计的压缩节省。

若要更改表、索引或分区的压缩状态，请使用 ALTER TABLE 或 ALTER INDEX 语句。 有关压缩的一般信息，请参阅 数据压缩。

注意

如果现有的数据含有碎片，则可以在不使用压缩的情况下通过重新生成索引来减小数据的大小。 对于索引，在索引重新生成的过程中将应用填充因子。 这可能会增加索引的大小。

Transact-SQL 语法约定

语法

sp_estimate_data_compression_savings
    [ @schema_name = ] N'schema_name'
    , [ @object_name = ] N'object_name'
    , [ @index_id = ] index_id
    , [ @partition_number = ] partition_number
    , [ @data_compression = ] N'data_compression'
    [ , [ @xml_compression = ] xml_compression ]
[ ; ]

参数

[ @schema_name = ] N'schema_name'

包含表或索引视图的数据库架构的名称。 @schema_name 为 sysname，无默认值。 如果@schema_nameNULL，则使用当前用户的默认架构。

[ @object_name = ] N'object_name'

索引位于的表或索引视图的名称。 @object_name为 sysname，无默认值。

[ @index_id = ] index_id

索引的 ID。 @index_id为 int，可以是以下值之一：

  • 索引的 ID 号
  • NULL
  • 0如果object_id是堆，则为

若要返回基表或视图的所有索引的信息，请指定 NULL。 如果指定NULL，还必须指定NULL@partition_number。

[ @partition_number = ] partition_number

对象中的分区号。 @partition_number为 int，可以是以下值之一：

  • 索引或堆的分区号
  • NULL
  • 1 用于非分区索引或堆

若要指定分区，还可以指定 $PARTITION 函数。 若要返回拥有对象的所有分区的信息，请指定 NULL。

[ @data_compression = ] N'data_compression'

指定要计算的压缩类型。 @data_compression 为 nvarchar（60），可以是以下值之一：

  • NONE
  • ROW
  • PAGE
  • COLUMNSTORE
  • COLUMNSTORE_ARCHIVE

对于 SQL Server 2022（16.x）及更高版本， NULL 也是一个可能的值。 如果@xml_compression，则@data_compression不能。NULLNULL

[ @xml_compression = ] xml_compression

适用于：SQL Server 2022（16.x）及更高版本、Azure SQL 数据库和Azure SQL 托管实例

指定是否计算 XML 压缩的节省。 @xml_compression位，可以是以下值之一：

  • NULL（默认值）
  • 0
  • 1

如果@data_compression，则@xml_compression不能NULL。NULL

返回代码值

0（成功）或 1（失败）。

结果集

将返回以下结果集，以提供表、索引或分区的当前大小和估计大小。

列名称 数据类型 描述
object_name sysname 表或索引视图的名称。
schema_name sysname 表或索引视图的架构。
index_id int 索引的索引 ID：

0 = 堆
1 = 聚集索引
>1 = 非聚集索引
partition_number int 分区号。 返回 1 非分区表或索引。
size_with_current_compression_setting (KB) bigint 当前存在的所请求的表、索引或分区的大小。
size_with_requested_compression_setting (KB) bigint 使用请求压缩设置的表、索引或分区的估计大小;如果适用，则为现有填充因子，并且假设没有碎片。
sample_size_with_current_compression_setting (KB) bigint 使用当前压缩设置时的示例大小。 此大小包括任何碎片。
sample_size_with_requested_compression_setting (KB) bigint 使用请求的压缩设置及现有填充因子（如果适用）创建的且没有碎片的样本的大小。

注解

用于 sp_estimate_data_compression_savings 估算为行、页面、列存储、列存储存档或 XML 压缩启用表或分区时可能发生的节省。 例如，如果行的平均大小可以减少 40%，则可以将对象的大小减小 40%。 您可能无法节省空间，因为这取决于填充因子和行大小。 例如，如果你有一个长度为 8,000 字节的行，并且将其大小减少 40%，则仍然可以在数据页上仅容纳一行。 没有节省。

如果在未压缩的表或索引上运行的结果 sp_estimate_data_compression_savings 指示大小将增加，这意味着许多行几乎使用数据类型的整个精度，并且添加压缩格式所需的小开销大于压缩节省。 在这种情况下，不要启用压缩。

如果已启用表进行压缩，则可以使用该 sp_estimate_data_compression_savings 表来估算该表未压缩时行的平均大小。

在此操作期间，在表上获取意向共享锁（IS） 锁。 如果无法获取 IS 锁，则会阻止该过程。 表在默认读取提交的隔离级别下扫描。

如果请求的压缩设置与当前压缩设置相同，则存储过程将使用源对象上索引的现有填充因子返回没有数据碎片的估计大小。

如果索引或分区 ID 不存在，则不返回任何结果。

权限

SELECT需要对表VIEW DATABASE STATE以及VIEW DEFINITION包含表和 on tempdb的数据库具有权限。

限制

在 SQL Server 2017（14.x）和早期版本中，此过程不适用于列存储索引，因此不接受数据压缩参数 COLUMNSTORE 和 COLUMNSTORE_ARCHIVE。 在 SQL Server 2019（15.x）及更高版本中，在Azure SQL 数据库和Azure SQL 托管实例中，列存储索引既可用作估算的源对象，也可以用作请求的压缩类型。

启用内存优化 TempDB 元数据后，不支持在临时表上创建列存储索引。 由于此限制， sp_estimate_data_compression_savingsCOLUMNSTORECOLUMNSTORE_ARCHIVE 启用内存优化 TempDB 元数据时不支持数据压缩参数。

列存储索引的注意事项

从 SQL Server 2019（15.x）开始，Azure SQL 数据库和Azure SQL 托管实例支持sp_estimate_compression_savings估算列存储和列存储存档压缩。 与页面和行压缩不同，将列存储压缩应用于对象需要创建新的列存储索引。 因此，使用此过程的 COLUMNSTORE 和 COLUMNSTORE_ARCHIVE 选项时，提供给过程的源对象的类型决定了用于压缩大小估计的列存储索引的类型。 下表说明了当@data_compression参数设置为任一或两COLUMNSTORE_ARCHIVE者COLUMNSTORE时，用于估计每个源对象类型的压缩节省的引用对象。

源对象 Reference 对象
**堆 聚集列存储索引
聚集索引 聚集列存储索引
非聚集索引 非聚集列存储索引（包括提供的非聚集索引的键列和包含的任何列，以及表的分区列（如果有）
非聚集列存储索引 非聚集列存储索引（包括与提供的非聚集列存储索引相同的列）
聚集列存储索引 聚集列存储索引

注意

从行存储源对象（聚集索引、非聚集索引或堆）估算列存储压缩时，如果源对象中有任何列具有列存储索引中不支持的数据类型， sp_estimate_compression_savings 将失败并显示错误。

同样，当@data_compression参数设置为NONE列ROW存储索引或PAGE源对象是列存储索引时，下表概述了使用的引用对象。

源对象 Reference 对象
聚集列存储索引 堆
非聚集列存储索引 非聚集索引（包括非聚集列存储索引中包含的列作为键列，以及表的分区列（如果有）作为包含列）

注意

从列存储源对象估算行存储压缩（NONE、ROW 或 PAGE）时，请确保源索引不包含超过 32 个键列，因为这是行存储（非聚集）索引中支持的限制。

示例

本文中的 Transact-SQL 代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可从 Microsoft SQL Server 示例和社区项目主页下载它。

A. 使用 ROW 压缩估计节省成本

以下示例通过使用压缩ROW来估计表的大小Production.WorkOrderRouting。

EXEC sys.sp_estimate_data_compression_savings
     'Production', 'WorkOrderRouting', NULL, NULL, 'ROW';
GO

B. 使用 PAGE 和 XML 压缩估计节省成本

适用于：SQL Server 2022 (16.x) 及更高版本

以下示例使用压缩PAGE来估计表的大小Production.ProductModel，并且启用了@xml_compression值。

EXEC sys.sp_estimate_data_compression_savings
     'Production', 'ProductModel', NULL, NULL, 'PAGE', 1;
GO

相关内容