Technical Article

Compress All Objects

,

This script creates compression statements for all objects in all databases on the instance where the script is run. As a deafult PAGE compression is used. If row compression is desired, it be changed by setting @comp = 'ROW'.

The Script does not run the actual compression commands, this is left up to the user.

The output of the script is a table with five columns:

  • Rows - the number of rows in the object to be compressed.
  • db_name - the name of the database.
  • CompressStatement - the actual statement to be executed if the object should be compressed. The statement tries to performn the operation as an online operation. If this is not possible, the operation is automatically done as an offline operation instead.
  • create_date - the create for the object in question.
  • modify_date - the modify for the object in question.

When the generated compression statements are executed, they print what compression operation was actually executed on what object.

Example:

ONLINE:  [MyDatabase].[dbo].[Clusters];

OFFLINE: [MyDatabase].[dbo].[Nodes];

ONLINE:  [MyDatabase].[dbo].[Instances];

ONLINE:  [MyDatabase].[dbo].[Disks];

Notes:

Compression is a SQL Server 2008+ Enterprise Edition feature.

The script performs compression as online operations, which requires a large amount of free space in the database. In order to minimize the impact of this, the compression statements are generated with the objects with the fewest rows first.

I would recommend running the results of this script with care, and testing it well before production use.

SET NOCOUNT ON
DECLARE @db_name VARCHAR(50);
DECLARE @sql VARCHAR(max);
DECLARE @comp CHAR(4);
SET @comp = 'PAGE'; -- Desired Compression Type

-- Create Cursor for all non-system databases, running in compatibility level above 90, online and not read-only.
DECLARE cur_dbs CURSOR FOR 
SELECT name
FROM sys.databases 
WHERE database_id > 4
AND compatibility_level > 90
AND is_read_only = 0
AND state = 0
-- AND name NOT IN ('exclude_my_database1','exclude_my_database2')
ORDER BY database_id

IF  EXISTS (
SELECT * FROM tempdb.sys.objects WHERE type = 'U' AND object_id = OBJECT_ID(N'tempdb..#TempCompressTable')
)
DROP TABLE #TempCompressTable;

CREATE TABLE #TempCompressTable (
Rows BIGINT
,db_name NVARCHAR(255)
,CompressStatement NVARCHAR(2000)
,create_date DATETIME
,modify_date DATETIME
);

OPEN cur_dbs

FETCH NEXT FROM cur_dbs INTO @db_name;
WHILE @@FETCH_STATUS = 0
   BEGIN
     SET @sql = 'USE ['+@db_name+'];' + CHAR(13) + CHAR(10)
 SET @sql = @sql + 'INSERT INTO #TempCompressTable' + CHAR(13) + CHAR(10)
 SET @sql = @sql + 'SELECT ' + CHAR(13) + CHAR(10)
 SET @sql = @sql + 'ROWS' + CHAR(13) + CHAR(10)
 SET @sql = @sql + ',db_name() AS db_name ' + CHAR(13) + CHAR(10)
 SET @sql = @sql + ',[--CompressStatement]  ' + CHAR(13) + CHAR(10)
 SET @sql = @sql + ',create_date' + CHAR(13) + CHAR(10)
 SET @sql = @sql + ',modify_date' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'FROM (' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'SELECT p.rows AS rows, ''BEGIN TRY ALTER TABLE ['' + db_name() + ''].['' + SCHEMA_NAME(schema_id) + ''].['' + name + ''] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+@comp+', ONLINE = ON); PRINT ''''ONLINE:  ['' + db_name() + ''].['' + SCHEMA_NAME(schema_id) + ''].['' + name + ''];'''' END TRY BEGIN CATCH ALTER TABLE ['' + db_name() + ''].['' + SCHEMA_NAME(schema_id) + ''].['' + name + ''] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+@comp+', ONLINE = OFF); PRINT ''''OFFLINE: ['' + db_name() + ''].['' + SCHEMA_NAME(schema_id) + ''].['' + name + '']''''; END CATCH;'' AS [--CompressStatement], create_date, modify_date ' + CHAR(13) + CHAR(10)
     SET @sql = @sql + '--SELECT *' + CHAR(13) + CHAR(10)
 SET @sql = @sql + 'FROM' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'sys.objects o' + CHAR(13) + CHAR(10)
     SET @sql = @sql + ',sys.partitions p' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'WHERE ' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'p.object_id = o.object_id' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND type = ''U'' ' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND p.index_id <= 1' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND o.name <> ''sysdiagrams''' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND p.partition_number = 1' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND o.is_ms_shipped = 0 ' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND data_compression = 0 -- all uncompressed user tables' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'UNION' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'SELECT p.rows AS rows, ''BEGIN TRY ALTER INDEX ['' + i.name + ''] ON ['' + db_name() + ''].['' + SCHEMA_NAME(o.schema_id) + ''].['' + OBJECT_NAME(i.object_id) + ''] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+@comp+', ONLINE = ON); PRINT ''''ONLINE:  ['' + db_name() + ''].['' + i.name + ''] ON ['' + OBJECT_NAME(i.object_id) + ''];'''' END TRY BEGIN CATCH ALTER INDEX ['' + i.name + ''] ON ['' + db_name() + ''].['' + SCHEMA_NAME(o.schema_id) + ''].['' + OBJECT_NAME(i.object_id) + ''] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+@comp+', ONLINE = OFF); PRINT ''''OFFLINE: ['' + db_name() + ''].['' + i.name + ''] ON ['' + OBJECT_NAME(i.object_id) + '']''''; END CATCH;'' AS [--CompressStatement], create_date, modify_date ' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'FROM ' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'sys.indexes i' + CHAR(13) + CHAR(10)
     SET @sql = @sql + ',sys.objects o' + CHAR(13) + CHAR(10)
     SET @sql = @sql + ',sys.partitions p' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'WHERE' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'o.object_id = i.object_id' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND o.object_id = p.object_id' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND i.type = 2 -- nonclustered index' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND i.name <> ''UK_principal_name'' -- nonclustered index' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND p.partition_number = 1' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND p.index_id > 1 -- nonclustered index' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND o.is_ms_shipped = 0 ' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND NOT o.type in (''TF'',''FN'')' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND data_compression = 0 -- all uncompressed user tables' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'AND o.schema_id NOT IN (3,4)  ' + CHAR(13) + CHAR(10)
     SET @sql = @sql + ') x' + CHAR(13) + CHAR(10)
     SET @sql = @sql + 'ORDER BY ROWS DESC, [--CompressStatement] DESC' + CHAR(13) + CHAR(10)
     EXEC (@sql)
     FETCH NEXT FROM cur_dbs INTO @db_name;
   END;
DEALLOCATE cur_dbs;

-- Retrieve the compression statements. If everything needs to be compressed, select the content of the column "CompressStatement" and run it manually.
SELECT * 
FROM #TempCompressTable
ORDER BY db_name, Rows ASC

Rate

4 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (2)

You rated this post out of 5. Change rating