Scripts for Full DB Compression at partition level

,

Although, SQL Server 2008 provides new Feature for DATA COMPRESSION -- There are not many Stored Proc. provided by MS to handle page compression at DB level (Partitioned /non-partitioned). Following PROC, can be handy to compress all objects in a Database with control of what partitions gets compressed.

 

key features:

  • Page/Row Compresses all objects in a given Database
  • If DB is partitioned and you wan to compress only RANGE of PARTITIONS - you can specifiy partition range
  • To compress specifc partition, just pass in Same partition number as Start and End partition
  • Also compresses non partitioned tables/indexes.
Drop PROC FullDB_Compression_For_Given_PartitionRange
go
CREATE PROC FullDB_Compression_For_Given_PartitionRange
(
@CompressionType VARCHAR(10) = 'Page', 
@StartAtPartition int = 153 , -- Change this to your choice
@EndAtPartition int = 153
)
/* 
 Jay Rajgor
 Sept 08, 2009
 Full DB Page Compression for all Objects in the database
 Revision 1: 11/04/2009 - Added Functionality to specify PARTITION Range.
 
 NOTE: Uncomment "EXEC @SQL " in below code to perform the compression. 
 In current Form - it will generate script for Data compression.
 
*/

 AS

 DECLARE
 @PK INT,
 @Schema varchar(150),
 @object varchar(150),
 @DAD varchar(25),
 @partNO int,
 @indexID int,
 @ixName VARCHAR(250),
 @SQL nVARCHAR(max),
 @ixType VARCHAR(50)
 
 

print ' DB Compression Started: '
print getdate()


-- Part 1 
-- set the compression on Partitioned Tables

DECLARE cCompress CURSOR FAST_FORWARD
 FOR 
 
 select distinct S.name, o.name, I.name, I.type_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' 
 and (P.partition_number < 152) and P.object_id in
(select object_id from sys.PARTITIONS group by object_id having max(partition_number) > 1 )
 
 OPEN cCompress
 FETCH cCompress INTO @Schema, @object, @ixName, @ixType -- , @CompressionType -- prime the cursor

 WHILE @@Fetch_Status = 0 
 BEGIN
 
 
 IF @ixType = 'Clustered' or @ixType='heap'
 set @SQL = 'ALTER TABLE ' + @schema + '.' + @object + ' Rebuild PARTITION=ALL with (data_compression = ' + @CompressionType + ' on PARTITIONS (' + convert(varchar(4),@StartAtPartition) + ' to ' + convert(varchar(4),@EndAtPartition) + '))'
 
 else 
 set @SQL = 'ALTER INDEX ' + @ixName + ' on ' + @schema + '.' + @object + ' Rebuild PARTITION=ALL with (data_compression = ' + @CompressionType + ' on PARTITIONS (' + convert(varchar(4),@StartAtPartition) + ' to ' + convert(varchar(4),@EndAtPartition) + '))'

 print @SQL 
 
 --EXEC sp_executesql @SQL 
 
 FETCH cCompress INTO @Schema, @object, @ixName, @ixType --, @CompressionType -- prime the cursor
 END

 CLOSE cCompress
 DEALLOCATE cCompress
 
 
 
 
-- Part 2 
-- set the compression on NON- partitioned Tables

 PRINT '' 
 PRINT ''
 PRINT ''
 
DECLARE cCompress CURSOR FAST_FORWARD
 FOR 
 
 select distinct S.name, o.name, I.name, I.type_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' 
 and P.object_id in
(select object_id from sys.PARTITIONS group by object_id having max(partition_number) =1 )
 
 OPEN cCompress
 FETCH cCompress INTO @Schema, @object, @ixName, @ixType 

 WHILE @@Fetch_Status = 0 
 BEGIN
 
 
 IF @ixType = 'Clustered' or @ixType='heap'
 
set @SQL = 'ALTER TABLE ' + @schema + '.' + @object + ' Rebuild PARTITION=ALL with (data_compression = ' + @CompressionType + ')'
 
 else 
set @SQL = 'ALTER INDEX ' + @ixName + ' on ' + @schema + '.' + @object + ' Rebuild PARTITION=ALL with (data_compression = ' + @CompressionType + ')'

 print @SQL 
 
 
 --EXEC sp_executesql @SQL 
 
 FETCH cCompress INTO @Schema, @object, @ixName, @ixType 
 END

 CLOSE cCompress
 DEALLOCATE cCompress
 
 
 
print 'DB Compression Completed: '
print getdate()
 
 RETURN

Rate

3.8 (5)

Share

Share

Rate

3.8 (5)