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 
