sp_estimate_data_compression_savings (Transact-SQL)

  • 项目

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

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

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

注意

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

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

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

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

注意

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

Transact-SQL 语法约定

语法

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

参数

[ @schema_name = ] 'schema_name'

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

[ @object_name = ] 'object_name'

索引位于的表或索引视图的名称。 object_name 为 sysname。

[ @index_id = ] index_id

索引的 ID。 index_id为 int，可以是以下值之一：如果object_id是堆，则为索引、NULL 或 0 的 ID 号。 若要返回基表或视图的所有索引的信息，请指定 NULL。 如果指定 NULL，则还必须为 partition_number指定 NULL。

[ @partition_number = ] partition_number

对象中的分区号。 partition_number为 int，可以是以下值之一：索引或堆的分区号、NULL 或非分区索引或堆的 1。

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

[ @data_compression = ] 'data_compression'

要计算的压缩类型。 data_compression可以是以下值之一：NONE、ROW、PAGE、COLUMNSTORE 或 COLUMNSTORE_ARCHIVE。

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

[ @xml_compression = ] xml_compression

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

指定是否计算 XML 压缩的节省。 xml_compression为位，可以是 NULL、0 或 1。 默认值为 NULL。

如果data_compression为 NULL，则xml_compression不能为 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 2019（15.x）之前，此过程不适用于列存储索引，因此不接受数据压缩参数 COLUMNSTORE 和COLUMNSTORE_ARCHIVE。 从 SQL Server 2019（15.x）开始，在Azure SQL 数据库和Azure SQL 托管实例中，列存储索引既可用作估算的源对象，也可以用作请求的压缩类型。

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

SQL Server 2022 （16.x） 候选版本 （RC） 0 不会估计 XML 索引的节省。

列存储索引的注意事项

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

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

注意

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

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

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

注意

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

示例

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

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

USE AdventureWorks2022;
GO
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值。

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

相关内容