sp_estimate_data_compression_savings (Transact-SQL)sp_estimate_data_compression_savings (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

返回所请求对象的当前大小并估算对象在所请求的压缩状态下的大小。Returns the current size of the requested object and estimates the object size for the requested compression state. 可对所有表或部分表评估压缩。Compression can be evaluated for whole tables or parts of tables. 这包括堆、聚集索引、非聚集索引、列存储索引、索引视图以及表和索引分区。This includes heaps, clustered indexes, nonclustered indexes, columnstore indexes, indexed views, and table and index partitions. 可以使用 row、page、列存储或列存储存档压缩来压缩这些对象。The objects can be compressed by using row, page, columnstore or columnstore archive compression. 如果表、索引或分区已经过压缩,则可使用该过程来估计在重新压缩的情况下该表、索引或分区的大小。If the table, index, or partition is already compressed, you can use this procedure to estimate the size of the table, index, or partition if it is recompressed.

备注

并非在 MicrosoftMicrosoftSQL ServerSQL Server每个版本中都提供压缩和 sp_estimate_data_compression_savings。Compression and sp_estimate_data_compression_savings are not available in every edition of MicrosoftMicrosoftSQL ServerSQL Server. 有关 SQL ServerSQL Server各版本支持的功能列表,请参阅 SQL Server 2016 各个版本支持的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016.

若要对使用请求的压缩设置的对象进行大小估算,该存储过程将对源对象进行采样并且将此数据加载到在 tempdb 中创建的等效表和索引中。To estimate the size of the object if it were to use the requested compression setting, this stored procedure samples the source object and loads this data into an equivalent table and index created in tempdb. 然后,将按照所请求的设置压缩在 tempdb 中创建的表和索引,并计算出估计的压缩节省量。The table or index create in tempdb is then compressed to the requested setting and the estimated compression savings is computed.

若要更改表、索引或分区的压缩状态, 请使用ALTER tablealter index语句。To change the compression state of a table, index, or partition, use the ALTER TABLE or ALTER INDEX statements. 有关压缩的一般信息, 请参阅数据压缩For general information about compression, see Data Compression.

备注

如果现有的数据含有碎片,则可以在不使用压缩的情况下通过重新生成索引来减小数据的大小。If the existing data is fragmented, you might be able to reduce its size without using compression by rebuilding the index. 对于索引,在索引重新生成的过程中将应用填充因子。For indexes, the fill factor will be applied during an index rebuild. 这可能会增加索引的大小。This could increase the size of the index.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
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'   
[;]  

参数Arguments

[ @schema_name=] "schema_name"[ @schema_name= ] 'schema_name'
包含表或索引视图的数据库架构的名称。Is the name of the database schema that contains the table or indexed view. schema_namesysnameschema_name is sysname. 如果schema_name为 NULL, 则使用当前用户的默认架构。If schema_name is NULL, the default schema of the current user is used.

[ @object_name=] "object_name"[ @object_name= ] 'object_name'
索引所属的表或索引视图的名称。Is the name of the table or indexed view that the index is on. object_name 为 sysname。object_name is sysname.

[ @index_id=] "index_id"[ @index_id= ] 'index_id'
索引的 ID。Is the ID of the index. index_id的数据值为int, 可以是下列值之一: 索引的 id 号、NULL 或 0 (如果object_id是堆)。index_id is int, and can be one of the following values: the ID number of an index, NULL, or 0 if object_id is a heap. 若要返回基表或视图的所有索引的信息,请指定 NULL。To return information for all indexes for a base table or view, specify NULL. 如果指定 NULL, 则还必须为partition_number指定 null。If you specify NULL, you must also specify NULL for partition_number.

[ @partition_number=] "partition_number"[ @partition_number= ] 'partition_number'
对象中的分区号。Is the partition number in the object. partition_number的数据值为int, 可以是下列值之一: 索引或堆的分区号、NULL 或 1 (对于未分区索引或堆)。partition_number is int, and can be one of the following values: the partition number of an index or heap, NULL or 1 for a nonpartitioned index or heap.

若要指定分区, 还可以指定$partition函数。To specify the partition, you can also specify the $partition function. 若要返回所属对象的所有分区的信息,请指定 NULL。To return information for all partitions of the owning object, specify NULL.

[ @data_compression=] "data_compression"[ @data_compression= ] 'data_compression'
要评估的压缩的类型。Is the type of compression to be evaluated. data_compression可以是下列值之一:NONE、ROW、PAGE、列存储或 COLUMNSTORE_ARCHIVE。data_compression can be one of the following values: NONE, ROW, PAGE, COLUMNSTORE, or COLUMNSTORE_ARCHIVE.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

结果集Result Sets

将返回以下结果集,以提供表、索引或分区的当前大小和估计大小。The following result set is returned to provide current and estimated size for the table, index, or partition.

列名Column name 数据类型Data type 描述Description
object_nameobject_name sysnamesysname 表或索引视图的名称。Name of the table or the indexed view.
schema_nameschema_name sysnamesysname 表或索引视图的架构。Schema of the table or indexed view.
index_idindex_id intint 索引的索引 ID:Index ID of an index:

0 = 堆0 = Heap

1 = 聚集索引1 = Clustered index

> 1 = 非聚集索引> 1 = Nonclustered index
partition_numberpartition_number intint 分区号。Partition number. 对于未分区的表或索引,返回 1。Returns 1 for a nonpartitioned table or index.
size_with_current_compression_setting (KB)size_with_current_compression_setting (KB) bigintbigint 当前存在的所请求的表、索引或分区的大小。Size of the requested table, index, or partition as it currently exists.
size_with_requested_compression_setting (KB)size_with_requested_compression_setting (KB) bigintbigint 使用请求的压缩设置及现有填充因子(如果适用)且假定不存在碎片时的表、索引或分区的估计大小。Estimated size of the table, index, or partition that uses the requested compression setting; and, if applicable, the existing fill factor, and assuming there is no fragmentation.
sample_size_with_current_compression_setting (KB)sample_size_with_current_compression_setting (KB) bigintbigint 使用当前压缩设置时的示例大小。Size of the sample with the current compression setting. 这包括任何碎片。This includes any fragmentation.
sample_size_with_requested_compression_setting (KB)sample_size_with_requested_compression_setting (KB) bigintbigint 使用请求的压缩设置及现有填充因子(如果适用)创建的且没有碎片的样本的大小。Size of the sample that is created by using the requested compression setting; and, if applicable, the existing fill factor and no fragmentation.

备注Remarks

使用 sp_estimate_data_compression_savings 估计为行、页、列存储或列存储存档压缩启用表或分区时可能会发生的节省。Use sp_estimate_data_compression_savings to estimate the savings that can occur when you enable a table or partition for row, page, columnstore or columnstore archive compression. 例如,如果行的平均大小可以减少 40%,则可能可以将对象大小减少 40%。For instance if the average size of the row can be reduced by 40 percent, you can potentially reduce the size of the object by 40 percent. 您可能无法节省空间,因为这取决于填充因子和行大小。You might not receive a space savings because this depends on the fill factor and the size of the row. 例如,如果某行长度为 8000 字节并且您将该行的大小减少 40%,则数据页上仍只能容纳一行。For example, if you have a row that is 8000 bytes long and you reduce its size by 40 percent, you can still fit only one row on a data page. 因此不会节省空间。There is no savings.

如果运行 sp_estimate_data_compression_savings 的结果指示表的大小将增长,则表示表中的许多行使用的几乎是数据类型的完全精度,因而为满足压缩格式的需要而增加的少量开销大于该压缩所带来的节省量。If the results of running sp_estimate_data_compression_savings indicate that the table will grow, this means that many rows in the table use almost the whole precision of the data types, and the addition of the small overhead needed for the compressed format is more than the savings from compression. 在这种极个别的情况下,请不要启用压缩。In this rare case, do not enable compression.

如果对表启用压缩,请使用 use sp_estimate_data_compression_savings 估算在未压缩该表的情况下该行的平均大小。If a table is enabled for compression, use sp_estimate_data_compression_savings to estimate the average size of the row if the table is uncompressed.

在此操作期间将获取该表的 (IS) 锁。An (IS) lock is acquired on the table during this operation. 如果不能获取 (IS) 锁,则该过程将被阻止。If an (IS) lock cannot be obtained, the procedure will be blocked. 该表将在已提交读隔离级别下进行扫描。The table is scanned under the read committed isolation level.

如果请求的压缩设置与当前的压缩设置相同,则该存储过程将返回在没有数据碎片且使用现有填充因子时的估计大小。If the requested compression setting is same as the current compression setting, the stored procedure will return the estimated size with no data fragmentation and using the existing fill factor.

如果索引或分区 ID 不存在,将不返回任何结果。If the index or partition ID does not exist, no results are returned.

权限Permissions

要求对该表具有 SELECT 权限。Requires SELECT permission on the table.

限制和局限Limitations and Restrictions

在 SQL Server 2019 之前, 此过程不适用于列存储索引, 因此不接受数据压缩参数列存储和 COLUMNSTORE_ARCHIVE。Prior to SQL Server 2019, this procedure did not apply to columnstore indexes, and therefore did not accept the data compression parameters COLUMNSTORE and COLUMNSTORE_ARCHIVE. 从 SQL Server 2019 开始, 列存储索引可用作估算源对象, 并可用作请求的压缩类型。Starting with SQL Server 2019, columnstore indexes can be used both as a source object for estimation, and as a requested compression type.

列存储索引的注意事项Considerations for Columnstore Indexes

从 SQL Server 2019 开始, sp_estimate_compression_savings 支持估计列存储和列存储存档压缩。Starting with SQL Server 2019, sp_estimate_compression_savings supports estimating both columnstore and columnstore archive compression. 与页和行压缩不同, 将列存储压缩应用于对象需要创建新的列存储索引。Unlike page and row compression, applying columnstore compression to an object requires creating a new columnstore index. 出于此原因, 使用此过程的列存储和 COLUMNSTORE_ARCHIVE 选项时, 提供给过程的源对象的类型决定了用于压缩的大小估算的列存储索引的类型。For this reason, when using the COLUMNSTORE and COLUMNSTORE_ARCHIVE options of this procedure, the type of the source object provided to the procedure determines the type of columnstore index used for the compressed size estimate. 下表说明了在将@data_compression参数设置为列存储或 COLUMNSTORE_ARCHIVE 时, 用于估计每个源对象类型的压缩节省量的引用对象。The following table illustrates the reference objects used to estimate compression savings for each source object type when the @data_compression parameter is set to either COLUMNSTORE or COLUMNSTORE_ARCHIVE.

源对象Source Object Reference 对象Reference Object
堆栈Heap 聚集列存储索引Clustered columnstore index
聚集索引Clustered index 聚集列存储索引Clustered columnstore index
非聚集索引Nonclustered index 非聚集列存储索引 (包括所提供的非聚集索引的键列和包含的所有列, 以及表的分区列, 如果有的话)Nonclustered columnstore index (including the key columns and any included columns of the provided nonclustered index, as well as the partition column of the table, if any)
非聚集列存储索引Nonclustered columnstore index 非聚集列存储索引 (包括与所提供的非聚集列存储索引相同的列)Nonclustered columnstore index (including the same columns as the provided nonclustered columnstore index)
聚集列存储索引Clustered columnstore index 聚集列存储索引Clustered columnstore index

备注

从行存储的源对象 (聚集索引、非聚集索引或堆) 估算列存储压缩时, 如果源对象中的任何列的数据类型在列存储索引中不受支持, 则为 sp_estimate_compression_savings将失败并出现错误。When estimating columnstore compression from a rowstore source object (clustered index, nonclustered index or heap), if there are any columns in the source object that have a data type that is not supported in a columnstore index, sp_estimate_compression_savings will fail with an error.

同样, 当@data_compression参数设置为 NONE、ROW 或 PAGE 并且源对象是列存储索引时, 下表概述了所使用的引用对象。Similarly, when the @data_compression parameter is set to NONE, ROW, or PAGE and the source object is a columnstore index, the following table outlines the reference objects used.

源对象Source Object Reference 对象Reference Object
聚集列存储索引Clustered columnstore index 堆栈Heap
非聚集列存储索引Nonclustered columnstore index 非聚集索引 (包括作为键列的非聚集列存储索引中包含的列) 以及表的分区列 (如果有) 作为包含列)Nonclustered index (including the columns contained in the nonclustered columnstore index as key columns, and the partition column of the table, if any, as an included column)

备注

估算列存储源对象的行存储压缩 (无、行或页) 时, 请确保源索引不包含超过32列, 因为这是行存储 (非聚集) 索引中支持的限制。When estimating rowstore compression (NONE, ROW or PAGE) from a columnstore source object, be sure that the source index does not contain more than 32 columns as this is the limit supported in a rowstore (nonclustered) index.

示例Examples

下面的示例估计 Production.WorkOrderRouting 表在使用 ROW 压缩进行压缩后的大小。The following example estimates the size of the Production.WorkOrderRouting table if it is compressed by using ROW compression.

USE AdventureWorks2012;  
GO  
EXEC sp_estimate_data_compression_savings 'Production', 'WorkOrderRouting', NULL, NULL, 'ROW' ;  
GO  

请参阅See Also

CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
sys.partitions (Transact-SQL) sys.partitions (Transact-SQL)
数据库引擎存储过程(transact-sql) Database Engine Stored Procedures (Transact-SQL)
Unicode 压缩的实现Unicode Compression Implementation