SQL Server Data Compression Storage Savings for all Tables

By: Eli Leiba   |   Updated: 2011-06-06   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | > Compression

Problem

In SQL Server 2008, we were introduced to Data Compression which can reduce data storage needs and potentially improve overall data access in the Enterprise and Developer editions. I have heard there are two types of compression available. Can you please explain them to me? However, my bigger question is I have databaeses that were originally developed in SQL Server 7.0, 2000 and 2005, so which tables should I compress? Do you have any scripts I can use to figure out the best candidates for compression? Check out this tip to learn more.

Solution

Those are great questions. To answer your first one related to the types of data compression, there are two methods:

• Row-level Data Compression: a compression method that turns fixed length data types into variable length data types, freeing up empty space. It also ignores zero and null values, saving additional space. In turn, more rows can fit into a single data page.
• Page-level Data Compression: a compression method that starts with row-level data compression, then adds a prefix and a dictionary compression for data pages.

To address your second question, I have coded a stored procedure called usp_tables_compress_report that loops over all of the objects in your database and executes the sp_estimate_data_compression_savings stored procedure. My stored procedure expects two parameters either ROW or PAGE. The valuable data to answer your second question is in the last column (avg_size_saving) of the stored procedure's result set. Let's jump into the code and review a sample result set.

CREATE PROCEDURE usp_tables_compress_report (@compress_method char(4))
AS 
SET NOCOUNT ON
BEGIN
DECLARE @schema_name sysname, @table_name sysname
CREATE TABLE #compress_report_tb 
(ObjName sysname,
schemaName sysname,
indx_ID int,
partit_number int,
size_with_current_compression_setting bigint,
size_with_requested_compression_setting bigint,
sample_size_with_current_compression_setting bigint,
sample_size_with_requested_compression_setting bigint)
DECLARE c_sch_tb_crs cursor for 
SELECT TABLE_SCHEMA,TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE LIKE 'BASE%' 
AND TABLE_CATALOG = upper(db_name())
OPEN c_sch_tb_crs
FETCH NEXT FROM c_sch_tb_crs INTO @schema_name, @table_name
WHILE @@Fetch_Status = 0 
BEGIN
INSERT INTO #compress_report_tb
EXEC sp_estimate_data_compression_savings
@schema_name = @schema_name,
@object_name = @table_name,
@index_id = NULL,
@partition_number = NULL,
@data_compression = @compress_method 
FETCH NEXT FROM c_sch_tb_crs INTO @schema_name, @table_name
END
CLOSE c_sch_tb_crs 
DEALLOCATE c_sch_tb_crs
SELECT schemaName AS [schema_name]
, ObjName AS [table_name]
, avg(size_with_current_compression_setting) as avg_size_with_current_compression_setting
, avg(size_with_requested_compression_setting) as avg_size_with_requested_compression_setting
, avg(size_with_current_compression_setting - size_with_requested_compression_setting) AS avg_size_saving
FROM #compress_report_tb
GROUP BY schemaName,ObjName
ORDER BY schemaName ASC, avg_size_saving DESC 
DROP TABLE #compress_report_tb
END
SET NOCOUNT OFF
GO

Here is a sample exeuction and result set:

USE Northwind 
GO
EXEC usp_tables_compress_report @compress_method = 'PAGE'
GO

Next Steps

• Review the stored procedure from this tip and check out the compression improvements possible with some of your databases that are running on SQL Server 2008 Enterprise or Developer edition.
• Check out the remainder of the SQL Server Data Compression tips.

About the author

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips