Data Compression
This document helps us to perform DataCompression @ PAGE and ROW level. We can Modify the scripts if we want to perform only @ PAGE or both.
Data compression uses the following 3 main procedures. These procedures need to be created on the user database on which we are going to implement the compression.
1. sp_estimate_data_compression_savings [will be existing - just verification- MS inbuilt Procedure]
2. SP_CompressDatabase_OnlyPage [This has both ROW and PAGE- Modified for Page level Compression; this will call procedure 1 and 3 internally]
3. db_compression_estimate [Procedure estimates ROW and PAGE Compression, Created by Paul Nielsen ]
Step By Step Procedure:-
Create the following procedure in the database on which we are going to implement data compression.
create PROC [dbo].[SP_CompressDatabase_OnlyPage]
(
@minCompression FLOAT = .25 /* e.g. .25 for minimum of 25% compression */
,@MaxRunTimeInMinutes INT = 60
,@ExecuteCompressCommand BIT = 1 /* 1 to execute command */
,@DBToScan SYSNAME /* database to compress */
)
AS
SET nocount ON;
IF ISNULL(@DBToScan, '') NOT IN (SELECT [name]
FROM sys.databases)
BEGIN
SELECT 'Database ' + ISNULL(@DBToScan, 'NULL')
+ ' not found on server.'
RETURN
END
DECLARE @CompressedCount INT;
SET @CompressedCount = 0;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @SQL1 NVARCHAR(MAX);
DECLARE @SQL2 NVARCHAR(MAX);
DECLARE @SQL3 NVARCHAR(MAX);
DECLARE @SQL4 NVARCHAR(MAX);
DECLARE @ParmDefinition NVARCHAR(100);
DECLARE @TestResult NVARCHAR(20);
DECLARE @CheckString NVARCHAR(1000);
DECLARE @StartTime DATETIME2;
SET @StartTime = CURRENT_TIMESTAMP;
DECLARE @CurrentDatabase SYSNAME;
SET @CurrentDatabase = DB_NAME()
/* Added this to Orginal Procedure : To get the data file sizes before compression */
PRINT '############################ CURRENT DATABASE FILE SIZE DETAILS BEFORE COMPRESSION ##########################'
SET @SQL1 = 'USE [' + @DBToScan + '];
select
name
, filename
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a'
EXEC (@SQL1)
print CHAR(13) + CHAR(10);
PRINT '############################ CURRENT TABLE SIZE DETAILS ##########################'
SET @SQL2 = 'USE [' + @DBToScan + '];
CREATE TABLE ##temp(
rec_id int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2),
Rundate date,
COMMENTS varchar(200) )
-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into ##temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace ''?''",
@command2="update ##temp set table_name = ''?'' where rec_id = (select max(rec_id) from ##temp)"
-- Set the total_size and total database size fields
UPDATE ##temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM ##temp)
-- Set Comments and rundate
UPDATE ##temp
SET Rundate = GETDATE(), comments=''BeforeCompression''
-- Set the percent of the total database size
-- Get the data
SELECT rec_id,table_name,nbr_of_rows,data_space,index_space,total_size
FROM tempdb..##temp
ORDER BY total_size DESC
--Inserting Data Into Table
IF NOT EXISTS ( SELECT 1
FROM sys.objects
WHERE object_id = OBJECT_ID(N''[dbo].[CompressionReport]'')
AND type IN (N''U'') )
BEGIN
CREATE TABLE [dbo].[CompressionReport](
[rec_id] [int] NULL,
[table_name] [varchar](128) NULL,
[nbr_of_rows] [int] NULL,
[data_space] [decimal](15, 2) NULL,
[index_space] [decimal](15, 2) NULL,
[total_size] [decimal](15, 2) NULL,
Rundate date,
Comments varchar(100)
)
END
Insert into CompressionReport
SELECT rec_id,table_name,nbr_of_rows,data_space,index_space,total_size, rundate,comments FROM tempdb..##temp ORDER BY total_size DESC '
EXEC (@SQL2)
print '###################################################### DATA COMPRESSION PROCESS STARTED #########################'+ CHAR(13) + CHAR(10);
IF OBJECT_ID('tempdb..##ObjEst', 'U') IS NOT NULL
DROP TABLE ##ObjEst
CREATE TABLE ##ObjEst
(
PK INT IDENTITY
NOT NULL
PRIMARY KEY
,object_name VARCHAR(250)
,schema_name VARCHAR(250)
,index_id INT
,partition_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_compresison_setting BIGINT
);
-- select * from dbo.dbCompressionEstimates
IF NOT EXISTS ( SELECT 1
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[dbCompressionEstimates]')
AND type IN (N'U') )
BEGIN
CREATE TABLE dbo.dbCompressionEstimates
(
PK INT IDENTITY
NOT NULL
PRIMARY KEY
,DatabaseName SYSNAME
,schema_name VARCHAR(250)
,object_name VARCHAR(250)
,index_id INT
,ixName VARCHAR(255)
,ixType VARCHAR(50)
,partition_number INT
,data_compression_desc VARCHAR(50)
,None_Size INT
,Row_Size INT
,Page_Size INT
,AlreadyProcessed BIT
,StatusText VARCHAR(75)
);
END
/*
If all objects have been processed, rescan and start fresh. Useful for catching
added objects since last scan. But beware - this is I/O intensive and can take a while.
*/
IF NOT EXISTS ( SELECT 1
FROM dbo.dbCompressionEstimates
WHERE AlreadyProcessed = 0
AND DatabaseName = @DBToScan )
BEGIN
DELETE FROM dbo.dbCompressionEstimates
WHERE DatabaseName = @DBToScan;
SET @SQL = 'USE [' + @DBToScan + '];
INSERT INTO [' + @CurrentDatabase
+ '].dbo.dbCompressionEstimates
(DatabaseName
,schema_name
,object_name
,index_id
,ixName
,ixType
,partition_number
,data_compression_desc
,AlreadyProcessed
,StatusText)
SELECT ''' + @DBToScan + '''
,S.name
,o.name
,I.index_id
,I.name
,I.type_desc
,P.partition_number
,P.data_compression_desc
,0 AS AlreadyProcessed
,''Initial load'' AS StatusText
FROM [' + @DBToScan + '].sys.schemas AS S
JOIN [' + @DBToScan
+ '].sys.objects AS O ON S.schema_id = O.schema_id
JOIN [' + @DBToScan
+ '].sys.indexes AS I ON o.object_id = I.object_id
JOIN [' + @DBToScan
+ '].sys.partitions AS P ON I.object_id = P.object_id
AND I.index_id = p.index_id
WHERE O.TYPE = ''U'';'
--AND P.data_compression_desc = ''NONE'';'
-- AND P.rows > 0;' -- only look at objects with data
EXEC (@SQL)
END
-- Determine Compression Estimates
DECLARE @PK INT
,@DatabaseName SYSNAME
,@Schema VARCHAR(150)
,@object VARCHAR(150)
,@DAD VARCHAR(25)
,@partNO VARCHAR(3)
,@indexID VARCHAR(3)
,@ixName VARCHAR(250)
,@ixType VARCHAR(50)
,@Recommended_Compression VARCHAR(10);
DECLARE cCompress CURSOR FAST_FORWARD
FOR
SELECT schema_name
,[object_name]
,index_id
,partition_number
,data_compression_desc
,ixName
FROM dbo.dbCompressionEstimates
WHERE (None_size IS NULL
OR Row_Size IS NULL
OR Page_size IS NULL)
AND DatabaseName = @DBToScan;
OPEN cCompress;
FETCH cCompress INTO @Schema, @object, @indexID, @partNO, @DAD, @ixName;
WHILE @@Fetch_Status = 0
BEGIN
/* evaluate objects with no compression */
IF DATEDIFF(mi, @StartTime, CURRENT_TIMESTAMP) < @MaxRunTimeInMinutes
BEGIN
/* First, make sure the table / index still exists (in case this
process is run over multiple days */
IF @indexID = 0
BEGIN /* heaps */
SET @CheckString = 'IF object_ID('''
+ @DBToScan + '.' + '['+ @Schema + ']'+'.'+ '['+@object+']'
+ ''') IS NULL
BEGIN
SELECT @TestResultOUT = ''Does Not Exist''
END
ELSE
BEGIN
SELECT @TestResultOUT = ''Exists''
END';
END
ELSE
BEGIN /* indexes */
SET @CheckString = 'IF NOT EXISTS (SELECT 1 FROM ['
+ @DBToScan
+ '].[sys].[indexes] WHERE [name] ='''
+ @ixName + ''' AND OBJECT_ID('''
+ '[' + @DBToScan + ']' + '.['
+ @Schema + '].[' + @object + ']'''
+ ') = [object_id])
BEGIN
SELECT @TestResultOUT = ''Does Not Exist''
END
ELSE
BEGIN
SELECT @TestResultOUT = ''Exists''
END';
END
SET @ParmDefinition = '@TestResultOUT varchar(20) OUTPUT';
EXECUTE sp_executesql
@CheckString
,@ParmDefinition
,@TestResultOUT = @TestResult OUTPUT;
IF @TestResult = 'Exists'
BEGIN
IF @DAD = 'none'
BEGIN
/* estimate Page compression */
SET @SQL = 'USE [' + @DBToScan
+ '];
INSERT ##ObjEst
(object_name
,schema_name
,index_id
,partition_number
,size_with_current_compression_setting
,size_with_requested_compression_setting
,sample_size_with_current_compression_setting
,sample_size_with_requested_compresison_setting)
EXEC sp_estimate_data_compression_savings
@Schema_name = ' + '['+ @Schema + ']'
+ '
,@object_name = '+'[' + @object +']'
+ '
,@index_id = ' + @indexID
+ '
,@partition_number = '
+ @partNO
+ '
,@data_compression = ''page'';'
EXEC (@SQL)
UPDATE dbo.dbCompressionEstimates
SET none_size = O.size_with_current_compression_setting
,page_size = O.size_with_requested_compression_setting
,StatusText = 'Compression estimate 50% complete'
FROM dbo.dbCompressionEstimates D
JOIN ##ObjEst O ON D.Schema_name = O.Schema_Name
AND D.Object_name = O.object_name
AND D.index_id = O.index_id
AND D.partition_number = O.partition_number;
DELETE ##ObjEst;
-- estimate Row compression
SET @SQL = 'USE [' + @DBToScan
+ '];
INSERT ##ObjEst
(object_name
,schema_name
,index_id
,partition_number
,size_with_current_compression_setting
,size_with_requested_compression_setting
,sample_size_with_current_compression_setting
,sample_size_with_requested_compresison_setting)
EXEC sp_estimate_data_compression_savings
@Schema_name = ' + '['+ @Schema + ']'
+ '
,@object_name = '+'[' + @object +']'
+ '
,@index_id = ' + @indexID
+ '
,@partition_number = '
+ @partNO
+ '
,@data_compression = ''ROW'';'
EXEC (@SQL)
UPDATE dbo.dbCompressionEstimates
SET row_size = O.size_with_requested_compression_setting
,StatusText = 'Compression estimate 100% complete'
FROM dbo.dbCompressionEstimates D
JOIN ##ObjEst O ON D.Schema_name = O.Schema_Name
AND D.Object_name = O.object_name
AND D.index_id = O.index_id
AND D.partition_number = O.partition_number;
DELETE ##ObjEst;
END /* end evaluating objects with no compression */
END
ELSE /* table or index no longer exists */
BEGIN
UPDATE dbo.dbCompressionEstimates
SET AlreadyProcessed = 1
,StatusText = 'Object no longer exists at compression estimate stage'
WHERE schema_name = @Schema
AND object_name = @object
AND index_id = @indexID
AND partition_number = @partNO
AND data_compression_desc = @DAD
AND DatabaseName = @DBToScan;
END
FETCH NEXT FROM cCompress INTO @Schema, @object,
@indexID, @partNO, @DAD, @ixName
END -- end time check block
ELSE
BEGIN
PRINT 'Max runtime reached. No compression performed. Compression estimate still processing. Exiting...';
CLOSE cCompress
DEALLOCATE cCompress
DROP TABLE ##ObjEst
RETURN
END
END -- end while loop
CLOSE cCompress
DEALLOCATE cCompress
PRINT 'Initial scan complete.'
--END
/* End evaluating compression savings. Now do the actual compressing. */
PRINT 'Beginning compression at ' + CONVERT(VARCHAR, GETDATE(), 101)+' ' + CONVERT(VARCHAR, GETDATE(), 108);
/* Do not process objects that do not meet our compression criteria */
/*Custom add : Added abs function to convert -ve values to +ve */
UPDATE dbo.dbCompressionEstimates
SET AlreadyProcessed = 1
,StatusText = 'Best compression method less than minCompression threshold'
WHERE abs((1 - (CAST(Row_Size AS float) / None_Size))) < @minCompression
AND (Row_Size <= Page_Size)
AND None_Size > 0
AND AlreadyProcessed = 0;
--1.3
UPDATE dbo.dbCompressionEstimates
SET AlreadyProcessed = 1
,StatusText = 'Best compression method less than minCompression threshold'
WHERE abs((1 - (CAST(Page_Size AS float) / None_Size))) < @minCompression
AND (Page_Size <= Row_Size)
AND None_Size > 0
AND AlreadyProcessed = 0;
/* Do not set compression on empty objects */
UPDATE dbo.dbCompressionEstimates
SET AlreadyProcessed = 1
,StatusText = 'No data in table to compress'
WHERE None_size = 0
AND AlreadyProcessed = 0;
-- set the compression
DECLARE cCompress CURSOR FAST_FORWARD
FOR
SELECT DatabaseName
,schema_name
,object_name
,partition_number
,ixName
,ixType
,CASE
/* Removing row compression
WHEN abs((1 - (CAST(Row_Size AS FLOAT) / none_Size))) >= @minCompression
AND (Row_Size <= Page_Size) THEN 'Row'
*/
WHEN abs((1 - (CAST(page_Size AS FLOAT) / none_Size))) >= @minCompression
AND (Page_Size <= row_Size) THEN 'Page'
ELSE 'None'
END AS Recommended_Compression
,PK
FROM dbo.dbCompressionEstimates
WHERE None_Size <> 0
AND (CASE
/* Removing row compression
WHEN abs((1 - (CAST(Row_Size AS FLOAT)
/ none_Size))) >= @minCompression
AND (Row_Size <= Page_Size) THEN 'Row'
*/
WHEN abs((1 - (CAST(page_Size AS FLOAT)
/ none_Size))) >= @minCompression
AND (Page_Size <= row_Size) THEN 'Page'
ELSE 'None'
END <> data_compression_desc)
AND AlreadyProcessed = 0
ORDER BY None_Size ASC; /* start with smallest tables first */
OPEN cCompress
FETCH cCompress INTO @DatabaseName, @Schema, @object, @partNO, @ixName,
@ixType, @Recommended_Compression, @PK -- prime the cursor;
WHILE @@Fetch_Status = 0
BEGIN
IF @ixType = 'Clustered'
OR @ixType = 'heap'
BEGIN
SET @SQL = 'USE [' + @DatabaseName + '];
ALTER TABLE [' + @Schema + '].[' + @object
+ '] Rebuild with (data_compression = '
+ @Recommended_Compression
+ ', SORT_IN_TEMPDB=ON)';
SET @CheckString = 'IF object_ID('''
+ @DatabaseName + '.' + @Schema + '.' + @object
+ ''') IS NULL
BEGIN
SELECT @TestResultOUT = ''Does Not Exist''
END
ELSE
BEGIN
SELECT @TestResultOUT = ''Exists''
END';
END
ELSE /* non-clustered index */
BEGIN
SET @SQL = 'USE [' + @DatabaseName + '];
ALTER INDEX [' + @ixName + '] on [' + @Schema
+ '].[' + @object
+ '] Rebuild with (data_compression = '
+ @Recommended_Compression
+ ',SORT_IN_TEMPDB=ON)';
SET @CheckString = 'IF NOT EXISTS (SELECT 1 FROM ['
+ @DBToScan
+ '].[sys].[indexes] WHERE [name] ='''
+ @ixName + ''' AND OBJECT_ID(''' + '['
+ @DBToScan + ']' + '.[' + @Schema + '].['
+ @object + ']''' + ') = [object_id])
BEGIN
SELECT @TestResultOUT = ''Does Not Exist''
END
ELSE
BEGIN
SELECT @TestResultOUT = ''Exists''
END';
END
IF DATEDIFF(mi, @StartTime, CURRENT_TIMESTAMP) < @MaxRunTimeInMinutes
BEGIN
IF @ExecuteCompressCommand = 1
BEGIN
/* verify that table / index still exists before doing anything */
SET @ParmDefinition = '@TestResultOUT varchar(20) OUTPUT';
EXECUTE sp_executesql
@CheckString
,@ParmDefinition
,@TestResultOUT = @TestResult OUTPUT;
IF @TestResult = 'Exists'
BEGIN
UPDATE dbo.dbCompressionEstimates
SET StatusText = 'Compressing data...'
WHERE PK = @PK;
/*added Timelines to the each compressed table*/
PRINT 'Compressing Started for Object: '+ @Schema + '.' + @object +' ' + 'at' + ' '+ CONVERT(VARCHAR, GETDATE(), 101) +' ' + CONVERT(VARCHAR, GETDATE(), 108);
PRINT 'Compressing table/index with'+' ' + @Recommended_Compression +' ' + 'Compression: '
+ @Schema + '.' + @object
+ '('+ @ixName +')' +' in database '
+ @DatabaseName;
EXEC sp_executesql
@SQL;
UPDATE dbo.dbCompressionEstimates
SET AlreadyProcessed = 1
,StatusText = 'Compression complete'
WHERE PK = @PK;
PRINT 'Compressing End for Object: '+ @Schema + '.' + @object +' '+ 'at' + ' '+ CONVERT(VARCHAR, GETDATE(), 101) +' ' + CONVERT(VARCHAR, GETDATE(), 108)+ CHAR(13) + CHAR(10);
-- PRINT CHAR(13) + CHAR(10)
SET @CompressedCount = @CompressedCount
+ 1;
END
ELSE
BEGIN
UPDATE dbo.dbCompressionEstimates
SET AlreadyProcessed = 1
,StatusText = 'Object no longer exists at compression stage'
WHERE PK = @PK;
END
END
ELSE
BEGIN
PRINT 'Command execution not enabled. Command is:'
+ @SQL;
END
END
ELSE
BEGIN
PRINT 'Max runtime reached. Some compression performed. Exiting...';
CLOSE cCompress
DEALLOCATE cCompress
DROP TABLE ##ObjEst
BREAK
END
FETCH cCompress INTO @DatabaseName, @Schema, @object,
@partNO, @ixName, @ixType, @Recommended_Compression,
@PK;
END
CLOSE cCompress;
DEALLOCATE cCompress;
PRINT 'Objects compressed: ' + CONVERT(VARCHAR(10), @CompressedCount);
PRINT 'compression for all tables completed at ' + CONVERT(VARCHAR, GETDATE(), 101)+' ' + CONVERT(VARCHAR, GETDATE(), 108);
DROP TABLE ##ObjEst
PRINT '############################ CURRENT TABLE SIZE DETAILS AFTER COMPRESSION ##########################'
SET @SQL3 = 'USE [' + @DBToScan + '];
CREATE TABLE #temp1(
rec_id int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2),
Rundate date,
COMMENTS varchar(200))
-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp1(nbr_of_rows, data_space, index_space) exec sp_mstablespace ''?''",
@command2="update #temp1 set table_name = ''?'' where rec_id = (select max(rec_id) from #temp1)"
-- Set the total_size and total database size fields
UPDATE #temp1
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp1)
-- Set the percent of the total database size
UPDATE #temp1
SET percent_of_db = (total_size/db_size) * 100
-- Set Comments and rundate
UPDATE #temp1
SET Rundate = GETDATE(), comments=''AfterCompression''
Select t1.rec_id, t1.table_name, t1.total_size as Current_Tale_Size, (t1.total_size - t2.total_size) TotalSavings_AfterCompression
From ##temp t1 inner join #temp1 t2
on t1.rec_id = t2.rec_id
--Inserting Data Into Table
Insert into CompressionReport
SELECT rec_id,table_name,nbr_of_rows,data_space,index_space,total_size, rundate,comments FROM tempdb..#temp1 ORDER BY total_size DESC
-- Comment out the following line if you want to do further querying
DROP TABLE ##temp
DROP TABLE #temp1
'
EXEC (@SQL3)
print CHAR(13) + CHAR(10);
/* Added this to Orginal Procedure : To get the data file sizes After compression */
PRINT '############################ CURRENT DATABASE FILE SIZE DETAILS AFTER COMPRESSION ##########################'
SET @SQL4 = 'USE [' + @DBToScan + ']; select
name
, filename
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a'
EXEC (@SQL4)
print CHAR(13) + CHAR(10);
RETURN
Now create Data Compression estimate Procedure
CREATE PROC [dbo].[db_compression_estimate]
AS
SET nocount ON
-- estimates the row and page compression gain for every object and index in the database
-- Paul Nielsen
-- www.SQLServerBible.com
-- March 13, 2008
-- to do:
-- [ ] transaction error from insert...Exec sp_estimate_data_compression_savings
-- [ ] filter objects to only those eligible for compression
CREATE TABLE #ObjEst
(
PK INT IDENTITY
NOT NULL
PRIMARY KEY
,object_name VARCHAR(250)
,schema_name VARCHAR(250)
,index_id INT
,partition_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_compresison_setting BIGINT
)
CREATE TABLE #dbEstimate
(
PK INT IDENTITY
NOT NULL
PRIMARY KEY
,schema_name VARCHAR(250)
,object_name VARCHAR(250)
,index_id INT
,ixName VARCHAR(255)
,ixType VARCHAR(50)
,partition_number INT
,data_compression_desc VARCHAR(50)
,None_Size INT
,Row_Size INT
,Page_Size INT
)
INSERT INTO #dbEstimate
(schema_name
,object_name
,index_id
,ixName
,ixType
,partition_number
,data_compression_desc)
SELECT S.name
,o.name
,I.index_id
,I.name
,I.type_desc
,P.partition_number
,P.data_compression_desc
FROM sys.schemas AS S
JOIN sys.objects AS O ON S.schema_id = O.schema_id
JOIN sys.indexes AS I ON o.object_id = I.object_id
JOIN sys.partitions AS P ON I.object_id = P.object_id
AND I.index_id = p.index_id
WHERE O.TYPE = 'U'
-- Determine Compression Estimates
DECLARE @PK INT
,@Schema VARCHAR(150)
,@object VARCHAR(150)
,@DAD VARCHAR(25)
,@partNO INT
,@indexID INT
,@SQL NVARCHAR(MAX)
DECLARE cCompress CURSOR FAST_FORWARD
FOR
SELECT schema_name
,object_name
,index_id
,partition_number
,data_compression_desc
FROM #dbEstimate
OPEN cCompress
FETCH cCompress INTO @Schema, @object, @indexID, @partNO, @DAD -- prime the cursor
WHILE @@Fetch_Status = 0
BEGIN
IF @DAD = 'none'
BEGIN
-- estimate Page compression
INSERT #ObjEst
(object_name
,schema_name
,index_id
,partition_number
,size_with_current_compression_setting
,size_with_requested_compression_setting
,sample_size_with_current_compression_setting
,sample_size_with_requested_compresison_setting)
EXEC sp_estimate_data_compression_savings
@Schema_name = @Schema
,@object_name = @object
,@index_id = @indexID
,@partition_number = @partNO
,@data_compression = 'page'
UPDATE #dbEstimate
SET none_size = O.size_with_current_compression_setting
,page_size = O.size_with_requested_compression_setting
FROM #dbEstimate D
JOIN #ObjEst O ON D.Schema_name = O.Schema_Name
AND D.Object_name = O.object_name
AND D.index_id = O.index_id
AND D.partition_number = O.partition_number
DELETE #ObjEst
-- estimate Row compression
INSERT #ObjEst
(object_name
,schema_name
,index_id
,partition_number
,size_with_current_compression_setting
,size_with_requested_compression_setting
,sample_size_with_current_compression_setting
,sample_size_with_requested_compresison_setting)
EXEC sp_estimate_data_compression_savings
@Schema_name = @Schema
,@object_name = @object
,@index_id = @indexID
,@partition_number = @partNO
,@data_compression = 'row'
UPDATE #dbEstimate
SET row_size = O.size_with_requested_compression_setting
FROM #dbEstimate D
JOIN #ObjEst O ON D.Schema_name = O.Schema_Name
AND D.Object_name = O.object_name
AND D.index_id = O.index_id
AND D.partition_number = O.partition_number
DELETE #ObjEst
END -- none compression estimate
IF @DAD = 'row'
BEGIN
-- estimate Page compression
INSERT #ObjEst
(object_name
,schema_name
,index_id
,partition_number
,size_with_current_compression_setting
,size_with_requested_compression_setting
,sample_size_with_current_compression_setting
,sample_size_with_requested_compresison_setting)
EXEC sp_estimate_data_compression_savings
@Schema_name = @Schema
,@object_name = @object
,@index_id = @indexID
,@partition_number = @partNO
,@data_compression = 'page'
UPDATE #dbEstimate
SET row_size = O.size_with_current_compression_setting
,page_size = O.size_with_requested_compression_setting
FROM #dbEstimate D
JOIN #ObjEst O ON D.Schema_name = O.Schema_Name
AND D.Object_name = O.object_name
AND D.index_id = O.index_id
AND D.partition_number = O.partition_number
DELETE #ObjEst
-- estimate None compression
INSERT #ObjEst
(object_name
,schema_name
,index_id
,partition_number
,size_with_current_compression_setting
,size_with_requested_compression_setting
,sample_size_with_current_compression_setting
,sample_size_with_requested_compresison_setting)
EXEC sp_estimate_data_compression_savings
@Schema_name = @Schema
,@object_name = @object
,@index_id = @indexID
,@partition_number = @partNO
,@data_compression = 'none'
UPDATE #dbEstimate
SET none_size = O.size_with_requested_compression_setting
FROM #dbEstimate D
JOIN #ObjEst O ON D.Schema_name = O.Schema_Name
AND D.Object_name = O.object_name
AND D.index_id = O.index_id
AND D.partition_number = O.partition_number
DELETE #ObjEst
END -- row compression estimate
IF @DAD = 'page'
BEGIN
-- estimate Row compression
INSERT #ObjEst
(object_name
,schema_name
,index_id
,partition_number
,size_with_current_compression_setting
,size_with_requested_compression_setting
,sample_size_with_current_compression_setting
,sample_size_with_requested_compresison_setting)
EXEC sp_estimate_data_compression_savings
@Schema_name = @Schema
,@object_name = @object
,@index_id = @indexID
,@partition_number = @partNO
,@data_compression = 'row'
UPDATE #dbEstimate
SET page_size = O.size_with_current_compression_setting
,row_size = O.size_with_requested_compression_setting
FROM #dbEstimate D
JOIN #ObjEst O ON D.Schema_name = O.Schema_Name
AND D.Object_name = O.object_name
AND D.index_id = O.index_id
AND D.partition_number = O.partition_number
DELETE #ObjEst
-- estimate None compression
INSERT #ObjEst
(object_name
,schema_name
,index_id
,partition_number
,size_with_current_compression_setting
,size_with_requested_compression_setting
,sample_size_with_current_compression_setting
,sample_size_with_requested_compresison_setting)
EXEC sp_estimate_data_compression_savings
@Schema_name = @Schema
,@object_name = @object
,@index_id = @indexID
,@partition_number = @partNO
,@data_compression = 'none'
UPDATE #dbEstimate
SET none_size = O.size_with_requested_compression_setting
FROM #dbEstimate D
JOIN #ObjEst O ON D.Schema_name = O.Schema_Name
AND D.Object_name = O.object_name
AND D.index_id = O.index_id
AND D.partition_number = O.partition_number
DELETE #ObjEst
END -- page compression estimate
FETCH cCompress INTO @Schema, @object, @indexID, @partNO,
@DAD
END
CLOSE cCompress
DEALLOCATE cCompress
-- report findings
SELECT schema_name + '.' + object_name AS [Object]
,index_id
,ixName
,ixType
,partition_number
,data_compression_desc AS Current_Compression
,CAST((1 - (CAST(Row_Size AS FLOAT) / none_Size)) * 100 AS INT) AS RowGain
,CAST((1 - (CAST(page_Size AS FLOAT) / none_Size)) * 100 AS INT) AS PageGain
,CASE WHEN (1 - (CAST(Row_Size AS FLOAT) / none_Size)) >= .25
AND (Row_Size <= Page_Size) THEN 'Row'
WHEN (1 - (CAST(page_Size AS FLOAT) / none_Size)) >= .25
AND (Page_Size <= row_Size) THEN 'Page'
ELSE 'None'
END AS Recommended_Compression
FROM #dbEstimate
WHERE None_Size <> 0
ORDER BY [Object]
Verify the compression status before starting the compression, usually this will result 0 rows.
SELECT
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[rows]
,[data_compression_desc]
,[index_id] as [IndexID_on_Table]
FROM sys.partitions
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
ORDER BY SchemaName, ObjectName
**
Once the above steps are done, execute the below command to implement the compression. Please start perfmon to capture intial sizes of tempdb and user database sizes and continue to capture till complete.**
** **
master.dbo.xp_cmdshell 'SQLCMD -S SERVERNAME -d DBNAME -E -m 1 -Q "exec [dbo].[SP_CompressDatabase_OnlyPage] @minCompression = 0.0, @MaxRunTimeInMinutes = 60,@ExecuteCompressCommand = 1, @DBToScan = ''DBNAME''" -o "C:\compression_output1.txt"
Output file can be verified in the destination mentioned above. Using query we can verify the tables/objects which are completed.
select * from dbo.dbCompressionEstimates
Above query will give the estimation on all objects
select * from dbo.CompressionReport
Using above report we can find how much size saved by comparing before and after compression.
Below query will results the total space saved
select table_name,total_size as SizeBeforeCompression into #t1 from CompressionReport where comments='Beforecompression'
go
select table_name,total_size as SizeAfterCompression into #t2 from CompressionReport where comments='Aftercompression'
go
select t.table_name, (t.SizeBeforeCompression-tt.SizeAfterCompression) as SpaceSavedMB, ((t.SizeBeforeCompression-tt.SizeAfterCompression)/1024) as SpaceSavedSIZEinGB from #t1 t join #t2 tt on t.table_name=tt.table_name order by 2 desc
go
drop table #t1
go
drop table #t2
======================================= END=======================================