SQL Server Data Compression Storage Savings for all Tables


By:   |   Updated: 2011-06-06   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | More > 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

data compression in sql server 2008
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.


Last Updated: 2011-06-06


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, March 10, 2015 - 8:47:27 AM - dan Back To Top

HI Eli,

I ran the SP against Master and tried to see compression of Adventure works it's not working!!! 


Friday, July 25, 2014 - 7:20:03 AM - BRIJ Back To Top

Hello Eli,

Do you have any blog created for Index saving?

 

SQL Server Data Compression Storage Savings for all Indexes ? If yes please let me know

 


Tuesday, May 06, 2014 - 8:39:17 AM - dab Back To Top

can you do order by ave_size_with_current_compression_setting desc?


Friday, March 23, 2012 - 12:28:53 PM - Fred Pizarro Back To Top

Very good sample ... thanks !!!



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools