Evaluate the use of compression for tables and indexes
In this we will learn how to evaluate the use of compression for tables and indexes.
sp_estimate_data_compression_savings (Transact-SQL)
Returns the current size of the requested object and estimates the object size for the requested compression state. However, 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. The objects can be compressed by using row, page, columnstore or columnstore archive compression. And, 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.
Further, 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. The table or index create in tempdb is then compressed to the requested setting and the estimated compression savings is computed.
Syntax
Arguments
- Firstly, [ @schema_name= ] ‘schema_name’
Is the name of the database schema that contains the table or indexed view. schema_name is sysname. If schema_name is NULL, the default schema of the current user is used. - Secondly, [ @object_name= ] ‘object_name’
Is the name of the table or indexed view that the index is on. object_name is sysname. - Thirdly, [ @index_id= ] index_id
Is the ID of the index. 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. Further, to return information for all indexes for a base table or view, specify NULL. If you specify NULL, you must also specify NULL for partition_number. - Next, [ @partition_number= ] partition_number
Is the partition number in the object. 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. - Lastly, [ @data_compression= ] ‘data_compression’
Is the type of compression to be evaluated. data_compression can be one of the following values: NONE, ROW, PAGE, COLUMNSTORE, or COLUMNSTORE_ARCHIVE.
Return Code Values
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.
Limitations and Restrictions
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. However, 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
Starting with SQL Server 2019 (15.x), 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. 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. However, 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.
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.
Examples
The following example estimates the size of the Production.WorkOrderRouting table if it is compressed by using ROW compression.
SQL
USE AdventureWorks2016;
GO
EXEC sp_estimate_data_compression_savings ‘Production’, ‘WorkOrderRouting’, NULL, NULL, ‘ROW’ ;
GO
Reference: Microsoft Documentation