We could use data compression in SQL Server to save both storage space and memory required for data cache. It also has a drawback of consuming high CPU usage to compress and decompress data. It is possible to apply data compression on tables and indexed views. There are two types of compression Row-level compression and Page-level compression.
Row-Level compression reduces storage space by converting fixed-length data types by removing the unused space as if it is a variable level length, by not storing NULL and reduce the metadata to store the data. Although it doesn’t deliver as much compression as Page-level compression it requires less amount of CPU.
I have a stored procedure to determine the percentage of compression delivered by each compression type.
--==================================================================================-- -- Author: Biz Nigatu -- Description: calculate row and page level compression percentage gain -- Sample Proc call: exec proc_calculate_compresson_precentage N'dbo',N'FactInternetSales' --==================================================================================-- create proc proc_calculate_compresson_precentage ( @schema_name NVarchar(256), @object_name NVarchar(256)) as DECLARE @data_compression NVarchar(120); DECLARE @rowCompression TABLE ( [object_name] NVarchar(256), [schema_name] NVarchar (256), [index_id] Int, [partition_number] Int, [size_with_current_setting(KB)] Int, [size_with_request_compression_setting(KB)] Int, [sample_size_with_current_compression_setting(KB)] Int, [sample_size_with_requested_compression_setting(KB)] Int ); DECLARE @pageCompression TABLE ( [object_name] NVarchar(256), [schema_name] NVarchar (256), [index_id] Int, [partition_number] Int, [size_with_current_setting(KB)] Int, [size_with_request_compression_setting(KB)] Int, [sample_size_with_current_compression_setting(KB)] Int, [sample_size_with_requested_compression_setting(KB)] Int ); --check for row level compression SET @data_compression = N'ROW'; --create temp table to hold the estimated values insert into @rowCompression exec sp_estimate_data_compression_savings @schema_name, @object_name, NULL, NULL, @data_compression; --check for page level compression SET @data_compression = N'PAGE'; --create temp table to hold the estimated values insert into @pageCompression exec sp_estimate_data_compression_savings @schema_name, @object_name, NULL, NULL, @data_compression; --summarize the result of the estimation select 'ROW' AS compression_type, [object_name], [schema_name], sum([size_with_current_setting(KB)]) AS [size_with_current_setting(KB)], sum([size_with_request_compression_setting(KB)]) AS [size_with_request_compression_setting(KB)], 100 -(cast(sum([size_with_request_compression_setting(KB)] ) as numeric(10,2)) * 100/ sum([size_with_current_setting(KB)])) as percentage_compression from @rowCompression group by [object_name], [schema_name]; select 'PAGE' AS compression_type, [object_name], [schema_name], sum([size_with_current_setting(KB)]) AS [size_with_current_setting(KB)], sum([size_with_request_compression_setting(KB)]) AS [size_with_request_compression_setting(KB)], 100 -(cast(sum([size_with_request_compression_setting(KB)] ) as numeric(10,2)) * 100/ sum([size_with_current_setting(KB)])) as percentage_compression from @pageCompression group by [object_name], [schema_name]; go |
Let us execute the stored procedure on AdventureWorksDW for FactInternetSales table.
exec proc_calculate_compresson_precentage N'dbo',N'FactInternetSales |
We get 46% compresson using ROW and 70% compression using PAGE compression.
Once we decided which compression type to use we could apply compression as
ALTER TABLE <shemaName>.<tableName> REBUILD WITH (DATA_COMPRESSION=<compressionType>); |
You may also use the following stored procedure
--===================================================================================-- -- Author: Biz Nigatu -- Description: Apply compression to a table -- Sample Proc call: -- exec proc_apply_compresson_precentage N'dbo',N'FactInternetSales','PAGE' --===================================================================================-- create proc proc_apply_compresson_precentage ( @schema_name NVARCHAR(256), @object_name NVARCHAR(256), @compression_type NVARCHAR(20)='ROW') as DECLARE @sql NVARCHAR(2000), @parameterDefinition Nvarchar(max); SET @sql = 'ALTER TABLE ' + LTRIM(RTRIM(@schema_name)) + '.' + LTRIM(RTRIM(@object_name)) + ' REBUILD WITH (DATA_COMPRESSION=' + @compression_type +')'; EXECUTE sp_executesql @sql; go |
Reference used
Rankins, Ray, Paul Bertucci, Chris Gallelli, Alex Silverstein, and Hilary,. Cotter.Microsoft SQL Server 2012 Unleashed. N.p.: Pearson Eduction, 2014.
http://msdn.microsoft.com/en-us/library/cc280576(v=sql.110).aspx
http://msdn.microsoft.com/en-us/library/cc280449(v=sql.110).aspx