SQL Server Data Compression Storage Savings for all Tables
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.
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
- 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
View all my tips
Article Last Updated: 2011-06-06