SQL 2019:- sp_estimate_data_compression_savings

This procedure allows us to estimate the potential space savings on a table before we apply a compression algorithm. It’s not exactly a new feature of SQL 2019 because it was also available in SQL 2017 however it now provides information on Columnstore indexes as well. The reason why this is important is because we often apply compression to very large tables that are often partitioned. This is done to improve the disk utilization as well as ensure that historical data doesn’t consume excessive space. However it’s difficult to estimate how much space would be saved when we utilize the massive compression afforded by COLUMNSTORE Indexes. While there are other way to get an estimate this procedure provides a fairly simple and easy way to do the job and as a result make database administrators lives easier when capacity planning.

Here are all the different ways you can call this procedure, its follows the typical Schema, object, partition, index, type format

sp_estimate_data_compression_savings 'dbo'
    ,'CaseDurations'
    ,NULL
    ,NULL
    ,'NONE'
GO

sp_estimate_data_compression_savings 'dbo'
    ,'CaseDurations'
    ,NULL
    ,NULL
    ,'ROW'
GO

sp_estimate_data_compression_savings 'dbo'
    ,'CaseDurations'
    ,NULL
    ,NULL
    ,'PAGE'
GO

sp_estimate_data_compression_savings 'dbo'
    ,'CaseDurations'
    ,NULL
    ,NULL
    ,'COLUMNSTORE'
GO

sp_estimate_data_compression_savings 'dbo'
    ,'CaseDurations'
    ,NULL
    ,NULL
    ,'COLUMNSTORE_ARCHIVE'

The below screenshot shows the results but further down I have also included an excel calculation on the potential saving as well. One thing I would have liked to see in the output was the row count so that I provides an easier way to figure out per row savings too.

As can be seen below Columnstore indexes do provide the most compression by as much as 85%.

Please Consider Subscribing

Subscribe