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.
Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.
- MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019