I wrote ascript 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.
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
由 udpwork.com 聚合
|
评论: 0
|
要! 要! 即刻! Now!