Estimate compression for Columnstore Indexes

The stored procedure sp_estimate_data_compression_savings estimates the object size for the requested compression state. Furthermore, you can evaluate potential compression savings for whole tables or parts of tables; we will discuss the available options shortly. Prior to SQL Server 2019, you were unable to use sp_estimate_data_compression_savings for columnstore indexes and, thus, we were unable to estimate compression for columnstore or columnstore_archive.

We have extended the capability for sp_estimate_data_compression_savings to include support for COLUMNSTORE and COLUMNSTORE_ARCHIVE.

sp_estimate_data_compression_savings Syntax

Look at the following T-SQL 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'   
[;]

The following argument descriptions are provided by docs.microsoft.com: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql?view=sql-server-2017.

Table 1.26: Description of the arguments

There are currently eight available outputs; you will primarily focus on the four outputs related to size.

Output:

object_name
schema_name
index_id
partition_number
size_with_current_compression_setting (KB)
size_with_requested_compression_setting (KB)
sample_size_with_current_compression_setting (KB)
sample_size_with_current_requested_setting (KB)

The following is an example of the procedure in action, followed by a comparison of the space savings for page and columnstore compression:

EXEC sp_estimate_data_compression_savings 
    @schema_name = 'dbo', 
    @object_name = 'MySourceTable', 
    @index_id = NULL, 
    @partition_number = NULL, 
    @data_compression = 'PAGE'

Example with PAGE Compression:

Figure 1.27: PAGE Compression

EXEC sp_estimate_data_compression_savings 
    @schema_name = 'dbo', 
    @object_name = 'MySourceTable', 
    @index_id = NULL, 
    @partition_number = NULL, 
    @data_compression = 'COLUMNSTORE'

Example with COLUMNSTORE compression:

Figure 1.28: COLUMNSTORE compression

In this example, page compression has estimated space savings of roughly 45%, and columnstore compression has estimated space savings of 68%.