Generate a SQL Script to run page-compression on all SQL Tables that exceed a certain saving rate threshold

This Script is reusing a script from Eli Leiba published here: https://www.mssqltips.com/sqlservertip/2381/sql-server-data-compression-storage-savings-for-all-tables/  and extends it to also generate the compression-script where the saving-rate exceeds a certain threshold (in the script below the threshold is 20%)

–DISCLAIMER:

–This code is not supported under any Microsoft standard support program or service.

–This code and information are provided “AS IS” without warranty of any kind, either expressed or implied.

–The entire risk arising out of the use or performance of the script and documentation remains with you.

–Furthermore, Microsoft or the author shall not be liable for any damages you may sustain by using this information, whether direct,

–indirect, special, incidental or consequential, including, without limitation, damages for loss of business profits, business interruption, loss of business information

–or other pecuniary loss even if it has been advised of the possibility of such damages.

–Read all the implementation and usage notes thoroughly.

CREATE Table CompressionResult (schemaname varchar(255),tablename varchar(255),avg_size_with_current_compression bigint, avg_size_with_requested_compression_setting bigint, avg_size_saving bigint)
GO
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

Insert Into CompressionResult
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

EXEC usp_tables_compress_report @compress_method = ‘PAGE’

Select * from CompressionResult

  Select Distinct ‘ALTER TABLE [‘+ schemaname +’].[‘+tablename+’] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)’
   from CompressionResult
   where avg_size_saving *1.0/(avg_size_with_current_compression+0.000001) > 0.20

Add a Comment

Your email address will not be published. Required fields are marked *