How to Estimate Space Saved by Data Compression in SQL Server? – Interview Question of the Week #167

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.