If you’re working with compressed indexes, SQL Server provides a system stored procedure to help test the space savings of implementing data compression:
sp_estimate_data_compression_savings. Starting in SQL Server 2019, it can even be used to estimate savings with columnstore.
I really don’t like
sp_estimate_data_compression_savings. In fact, I kind of hate it. It’s not always very accurate–and even when it is accurate, the results can be misleading. Before I get ranty about why I don’t like it, let’s look at it in action.
Andy makes good points in this, so check it out.