Compression estimates
The script estimate compression benefits per each object in the database for which the page compression has not been implemented.
2018-02-15 (first published: 2018-01-15)
1,885 reads
USE [YourDatabaseNameHere] GO /****** Object: StoredProcedure [dbo].[spCompression] Script Date: 10/9/2015 11:30:55 AM ******/SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[spCompression] (@Execute INT =1 ) /*----------------------------------------------------------------------------------------- Date:2015.03.17 Developer:Eric S Blake Description:System Util to keep compression to page. Version:1.00 ------------------------------------------------------------------------------------------- Modifications ------------------------------------------------------------------------------------------- Date:yyyy/mm/dd Developer:your name Description:description of mod Version:Version## -------------------------------------------------------------------------------------------*/as begin DECLARE @TablesCompressed INT=0 ,@IndexesCompressed INT=0 ,@ExecuteNow INT = 1 -- 0-Test, 1=Execute now. Set NOCOUNT ON SET @ExecuteNow=@Execute BEGIN TRY DROP TABLE [#TableCompress] END TRY BEGIN CATCH -- END CATCH BEGIN TRY DROP TABLE [#IndexCompress] END TRY BEGIN CATCH -- END CATCH BEGIN TRY DROP TABLE [#CompressCommands] -- Depreciated.... END TRY BEGIN CATCH -- END CATCH CREATE TABLE [#TableCompress]( [RID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED, [SchemaName] [varchar](255) NOT NULL, [TableName] [varchar](255) NOT NULL, ) CREATE TABLE [#IndexCompress]( [RID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED, [SchemaName] [varchar](255) NOT NULL, [TableName] [varchar](255) NOT NULL, [IndexName] [varchar](255) NOT NULL, [IsPrimaryKey] bit not null, ) INSERT INTO [#TableCompress] ([SchemaName],[TableName] ) select s.name [SchemaName], t.name [TableName] from sys.tables t join sys.schemas s on t.schema_id = s.schema_id join sys.indexes i on t.object_id = i.object_id join sys.partitions p on (i.object_id = p.object_id and i.index_id = p.index_id) where t.type = 'U' and i.name is null -- Base - Tables and p.data_compression_desc='None' and t.name not like 'sys%' order by 1, 2 INSERT INTO [#IndexCompress] ([SchemaName],[TableName],[IndexName],[IsPrimaryKey] ) select s.name [SchemaName], t.name [TableName], i.name [IndexName] ,isnull([i].[is_primary_key],0)AS [IsPrimaryKey] from sys.tables t join sys.schemas s on t.schema_id = s.schema_id join sys.indexes i on t.object_id = i.object_id join sys.partitions p on (i.object_id = p.object_id and i.index_id = p.index_id) where t.type = 'U' and i.name is not null and p.data_compression_desc='None' and t.name not like 'sys%' order by 1, 2, 3 -- TEMP tables loaded.... -- EXECUTE Commands.... DECLARE @RowCountint ,@OnRowint =0 ,@CMDnvarchar(MAX) ,@Statusvarchar(max) ,@SchemanNamevarchar(255) ,@TableNamevarchar(255) ,@IndexNamevarchar(255) ,@IsPrimaryKeyBIT SET @Status='Starting Table/Index Compression...' + CHAR(10) RAISERROR (@Status, 0, 1) WITH NOWAIT SELECT @RowCount=COUNT(*) FROM [#TableCompress] SET @Status='Table COMPRESSION There are ' + CAST(@RowCount as varchar(10)) + ' Tables to Compress' RAISERROR (@Status, 0, 1) WITH NOWAIT IF(@RowCount>0) SELECT CHAR(9) + [SchemaName] + '.' +[TableName]FROM [#TableCompress] SET @OnRow=1 WHILE (@OnRow<=@RowCount) BEGIN Select @SchemanName =[SchemaName],@TableName=[TableName]FROM [#TableCompress] where [RID]=@OnRow SET @Status='Compressing Table ' + QUOTENAME(@SchemanName)+'.'+QUOTENAME(@TableName); SET @Status+=' ' + CAST(@OnRow as varchar(10)) +' of ' + cast(@RowCount as varchar(10)) RAISERROR (@Status, 0, 1) WITH NOWAIT SET @CMD='ALTER TABLE [@S].[@T] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION=PAGE)' SET @CMD=REPLACE(@CMD,'@S',@SchemanName) SET @CMD=REPLACE(@CMD,'@T',@TableName) SET @Status=CHAR(9)+@CMD RAISERROR (@Status, 0, 1) WITH NOWAIT IF(@ExecuteNow=1) EXEC (@CMD) SET @TablesCompressed +=1; SET @OnRow+=1; END SET @Status='Table Processing Complete...' + CHAR(10) RAISERROR (@Status, 0, 1) WITH NOWAIT SELECT @RowCount=COUNT(*) FROM [#IndexCompress] SET @Status='Index COMPRESSION There are ' + CAST(@RowCount as varchar(10)) + ' Indexes to Compress' RAISERROR (@Status, 0, 1) WITH NOWAIT IF(@RowCount>0) SELECT CHAR(9) + [SchemaName] + '.' +[TableName]+'.'+[IndexName]FROM [#IndexCompress] SET @OnRow=1 WHILE (@OnRow<=@RowCount) BEGIN Select @SchemanName =[SchemaName] ,@TableName=[TableName] ,@IndexName=[IndexName] ,@IsPrimaryKey=[IsPrimaryKey]FROM [#IndexCompress] where [RID]=@OnRow SET @Status='Compressing Index ' + QUOTENAME(@SchemanName)+'.'+QUOTENAME(@TableName) +'.'+QUOTENAME(@IndexName); SET @Status+=' ' + CAST(@OnRow as varchar(10)) +' of ' + cast(@RowCount as varchar(10)) RAISERROR (@Status, 0, 1) WITH NOWAIT SET @CMD='ALTER INDEX [@I] ON [@S].[@T] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE)' SET @CMD=REPLACE(@CMD,'@S',@SchemanName) SET @CMD=REPLACE(@CMD,'@T',@TableName) SET @CMD=REPLACE(@CMD,'@I',@IndexName) SET @Status=CHAR(9)+@CMD RAISERROR (@Status, 0, 1) WITH NOWAIT IF(@ExecuteNow=1) EXEC (@CMD) SET @TablesCompressed +=1; SET @OnRow+=1; END SET @Status='Index Processing Complete...' + CHAR(10) RAISERROR (@Status, 0, 1) WITH NOWAIT BEGIN TRY DROP TABLE [#TableCompress] END TRY BEGIN CATCH -- END CATCH BEGIN TRY DROP TABLE [#IndexCompress] END TRY BEGIN CATCH -- END CATCH END