Question: How to Estimate Space Saved by Data Compression in SQL Server?
Answer: It has been long since I have heard this question. I remember I used to answer this question pretty much every day when I was drivers were costly. Nowadays when the storage is very cheap (economical), this question is not something I hear every day. Anyway, let us quickly see the answer to this question.
There is already inbuilt stored procedure sp_estimate_data_compression_savings in SQL Server. We can pass either of three parameters to this Stored Procedure – PAGE, ROW or NONE.
Before you continue reading this blog post, I suggest you read following blog posts:
- SQL SERVER – Data and Page Compressions – Data Storage and IO Improvement
- SQL SERVER – 2008 – Introduction to Row Compression
Now let us see the syntax of how we can see the estimated compression for a table. The following example estimates the size of the Production.WorkOrderRouting table if it is compressed by using ROW compression.
USE WideWorldImporters; GO EXEC sp_estimate_data_compression_savings 'Sales', 'InvoiceLines', NULL, NULL, 'ROW' ; GO
You can replace the parameter ROW to PAGE to see Page Level compression.
USE WideWorldImporters; GO EXEC sp_estimate_data_compression_savings 'Sales', 'InvoiceLines', NULL, NULL, 'PAGE' ; GO
Let me know if you have any further questions. Here is the link to a script which can you can download to estimate the compression for the entire database.
Reference: Pinal Dave (https://blog.sqlauthority.com)
