Clustered columnstore index rebuild script–updated
Recently I published a series of blog posts that provided scripts for rebuilding clustered columnstore indexes.
Niko Neugebauer sent me a suggested update to the latest version of those scripts and it makes sense. The change will help to filter out row groups that are not compressed and Niko noted that as Microsoft have started to include more objects (like Tombstone values) into sys.column_store_row_groups in Azure SQLDatabase, he believes that will help to prevent future SQL versions having a problem with the scripts.
Sounds good so the latest version is shown below. Thanks Niko !
-———————————————————————
-- Rebuild clustered columnstore indexes when necessary
-- Dr Greg Low v2.1
-———————————————————————
-- rebuild when more than supplied % of rows have been deleted
-- rebuild when any segments contain more than supplied % deleted rows
-- rebuild if more than supplied number of segments are empty
-- rebuild when segments are on average less than the supplied % of
-- maximum capacity
DECLARE @DeletedTotalRowPercentage int = 10;
DECLARE @DeletedSegmentsRowPercentage int = 20;
DECLARE @EmptySegmentsAllowed int = 0;
DECLARE @SegmentPotentialFullnessPercentage int = 90;
-———————————————————————
DECLARE @IndexesToRebuild TABLE (SchemaName sysname,
TableName sysname,
IndexName sysname);
WITH ClusteredColumnstoreIndexes
AS
( SELECT t.object_id AS ObjectID,
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
i.name AS IndexName
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
ON i.object_id = t.object_id
WHERE i.type = 5
),
RowGroups
AS
( SELECT csrg.object_id AS ObjectID,
csrg.total_rows AS TotalRows,
csrg.deleted_rows AS DeletedRows,
csrg.deleted_rows * 100.0 / csrg.total_rows AS DeletedPercentage,
CASE WHEN csrg.total_rows = csrg.deleted_rows
THEN 1 ELSE 0
END AS IsEmptySegment
FROM sys.column_store_row_groups AS csrg
WHERE csrg.state = 3 – Compressed (Ignoring: 0 - Hidden, 1 - Open, 2 - Closed, 4 - Tombstone)
),
IndexStats
AS
( SELECT cci.ObjectID,
cci.SchemaName,
cci.TableName,
cci.IndexName,
SUM(CAST(rg.TotalRows AS decimal(18,0))) AS TotalRows,
SUM(CAST(rg.DeletedRows AS decimal(18,0))) AS DeletedRows,
SUM(CAST(rg.DeletedRows AS decimal(18,0))) * 100.0
/ SUM(CAST(rg.TotalRows AS decimal(18,0)))
AS DeletedPercentage,
SUM(rg.IsEmptySegment) aS EmptySegments,
COUNT(rg.TotalRows) AS TotalSegments
FROM ClusteredColumnstoreIndexes AS cci
INNER JOIN RowGroups AS rg
ON cci.ObjectID = rg.ObjectID
GROUP BY cci.ObjectID, cci.SchemaName, cci.TableName, cci.IndexName
)
INSERT @IndexesToRebuild (SchemaName, TableName, IndexName)
SELECT s.SchemaName, s.TableName, s.IndexName
FROM IndexStats AS s
WHERE s.DeletedPercentage > @DeletedTotalRowPercentage
OR s.EmptySegments > @EmptySegmentsAllowed
OR EXISTS(SELECT 1 FROM RowGroups AS rg
WHERE rg.ObjectID = s.ObjectID
AND rg.DeletedPercentage > @DeletedSegmentsRowPercentage)
OR (s.TotalRows * 100 / (s.TotalSegments * 1048576.0))
< @SegmentPotentialFullnessPercentage;
DECLARE @SchemaName sysname;
DECLARE @TableName sysname;
DECLARE @IndexName sysname;
DECLARE @SqlCommand nvarchar(max);
DECLARE IndexList CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT SchemaName, TableName, IndexName
FROM @IndexesToRebuild
ORDER BY SchemaName, TableName, IndexName;
OPEN IndexList;
FETCH NEXT FROM IndexList INTO @SchemaName, @TableName, @IndexName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlCommand = N’ALTER INDEX ’ + QUOTENAME(@IndexName)
+ N’ ON ’ + QUOTENAME(@SchemaName)
+ N’.’ + QUOTENAME(@TableName)
+ N’ REBUILD PARTITION = ALL;';
PRINT @SqlCommand;
EXEC(@SqlCommand);
FETCH NEXT FROM IndexList INTO @SchemaName, @TableName, @IndexName;
END;
CLOSE IndexList;
DEALLOCATE IndexList;
2015-01-31