SQL Server: Use sp_estimate_data_compression_savings for checking estimate object size and saving space

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing the use of sp_estimate_data_compression_savings system stored procedure which we can use to check the object size + object expected saving space in SQL Server.

Many times, I have found that people are using CHAR data type for fix length store, but it is also true that people are not storing a correct length of data in CHAR column.

I just gave one example of CHAR column; there might be many reasons where we have to check the table actual size and the size of saving space.

Once we got the actual size and expected saving space, we can apply proper compression algorithm on objects. If your table has multiple indexes, you can compare space of all indexes and can take the decision to which index requires to compress.

We can use sp_estimate_data_compression_savings for the table of a clustered index, non-clustered index and heap table. You can also evaluate object for ROW size and PAGE SIZE.

Check the below small demonstration on this:

Create a sample table:

Insert dummy data:

Check the inserted records:

Check space for ROW:

Result: Check the difference between size_with_current_compression_setting and size_with_requested_compression_setting

After data compression, we can make the object size upto size_with_requested_compression_setting.

Check space for PAGE:

Result: Check the difference between size_with_current_compression_setting and size_with_requested_compression_setting

After data compression, we can make the object size upto size_with_requested_compression_setting.