I wrote a script to generate script for non-clustered index page compression in SQL Server. I’ve since made improvements and here it is.

Highlights:

• This script generates index compression script on a partition by partition basis, if the underlying index is partitioned;
• Like the previous version, it still works for non-partitioned indexes;
• Like the previous version, it compresses indexes from smallest to largest, progressively saving space as it nibbles forward. Therefore it is unlikely that it’ll grow data file(s) during rebuild;
• Unlike the previous version, I’ve set maxdop = 0, which let’s SQL decides degree of parallelism;
• There is no quick way, that I know of, to tell if an underlying index is partitioned. Hence the usage of two temp tables to differentiate the two for proper script generation.

[code language=”sql”]
IF Object_id(‘tempdb..#NonPartitionedIndex’) IS NOT NULL
DROP TABLE #nonpartitionedindex

SELECT object_id,
index_id
INTO #nonpartitionedindex
FROM sys.partitions
WHERE object_id > 255
AND data_compression IN ( 0, 1 ) — Non-compressed
AND index_id > 1
GROUP BY object_id,
index_id
HAVING Count(*) = 1

IF Object_id(‘tempdb..#PartitionedIndex’) IS NOT NULL
DROP TABLE #partitionedindex

SELECT object_id,
index_id
INTO #partitionedindex
FROM sys.partitions
WHERE object_id > 255
AND data_compression IN ( 0, 1 ) — Non-compressed
AND index_id > 1
GROUP BY object_id,
index_id
HAVING Count(*) > 1

SELECT s.NAME
AS
SchemaName,
t.NAME
AS TableName,
i.NAME
AS IndexName,
‘ALTER INDEX ‘ + i.NAME + ‘ ON ‘ + s.NAME + ‘.’
+ t.NAME
+
‘ REBUILD WITH (SORT_IN_TEMPDB = ON, MAXDOP = 0, DATA_COMPRESSION = PAGE);’ AS
AlterRebuild,
Sum(a.total_pages) * 8
AS TotalSpaceKB,
Sum(a.used_pages) * 8
AS UsedSpaceKB,
( Sum(a.total_pages) – Sum(a.used_pages) ) * 8
AS UnusedSpaceKB
FROM sys.tables t
JOIN sys.schemas s
ON s.schema_id = t.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
JOIN sys.allocation_units a
ON p.partition_id = a.container_id
JOIN #nonpartitionedindex npi
ON p.object_id = npi.object_id
AND p.index_id = npi.index_id
WHERE i.index_id > 1 — Non-clustered indexes
AND p.data_compression IN ( 0, 1 ) — Non-compressed
AND t.NAME <> ‘dtproperties’ — Ignore certain tables
AND t.is_ms_shipped = 0
AND i.object_id > 255 — Non-system objects
GROUP BY s.NAME,
t.NAME,
i.NAME,
p.partition_number
UNION ALL
SELECT s.NAME AS SchemaName,
t.NAME AS TableName,
i.NAME AS IndexName,
‘ALTER INDEX ‘ + i.NAME + ‘ ON ‘ + s.NAME + ‘.’
+ t.NAME
+
‘ REBUILD WITH (SORT_IN_TEMPDB = ON, MAXDOP = 0, DATA_COMPRESSION = PAGE ON PARTITIONS (‘
+ Cast(p.partition_number AS VARCHAR(3))
+ ‘));’ AS AlterRebuild,
Sum(a.total_pages) * 8 AS TotalSpaceKB,
Sum(a.used_pages) * 8 AS UsedSpaceKB,
( Sum(a.total_pages) – Sum(a.used_pages) ) * 8 AS UnusedSpaceKB
FROM sys.tables t
JOIN sys.schemas s
ON s.schema_id = t.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
JOIN sys.allocation_units a
ON p.partition_id = a.container_id
JOIN #partitionedindex pi
ON p.object_id = pi.object_id
AND p.index_id = pi.index_id
WHERE i.index_id > 1 — Non-clustered indexes
AND p.data_compression IN ( 0, 1 ) — Non-compressed
AND t.NAME <> ‘dtproperties’ — Ignore certain tables
AND t.is_ms_shipped = 0
AND i.object_id > 255 — Non-system objects
GROUP BY s.NAME,
t.NAME,
i.NAME,
p.partition_number
ORDER BY UsedSpaceKB
[/code]