Azure SQL Database にて指定したスキーマと断片化率に応じたインデックスを再構築するコード

コード

Azure SQL Database にて指定した断片化率以上のインデックスを再構築するコードを共有します。Github Copilot にて生成した SQL を変更しました。

DECLARE @TargetFragmentation FLOAT = 30
DECLARE @TargetSchemaName NVARCHAR(255) = 'dbo'
DECLARE @TableName NVARCHAR(255)
DECLARE @IndexName NVARCHAR(255)

DECLARE FragmentedIndexes CURSOR
FOR
SELECT TableName = t.name
	,IndexName = i.name
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
INNER JOIN sys.indexes i ON s.object_id = i.object_id
	AND s.index_id = i.index_id
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s2 ON t.schema_id = s2.schema_id
WHERE s.avg_fragmentation_in_percent > @TargetFragmentation
	AND s2.name = @TargetSchemaName

OPEN FragmentedIndexes

FETCH NEXT
FROM FragmentedIndexes
INTO @TableName
	,@IndexName

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Rebuilding index ' + @indexName + ' on table ' + @tableName + ' with fragmentation ' + CONVERT(NVARCHAR(10), @avgFragmentation) + '%';

	EXEC ('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD WITH (ONLINE = ON)')

	FETCH NEXT
	FROM FragmentedIndexes
	INTO @TableName
		,@IndexName
END

CLOSE FragmentedIndexes

DEALLOCATE FragmentedIndexes

Github Copilot の質問内容と回答

Azure SQL Database にて、指定したスキーマのインデックスの 断片化率が30パーセント以上の場合に再構築する T-SQL を作成してください。

DECLARE @schemaName NVARCHAR(128) = 'YourSchemaName';
DECLARE @tableName NVARCHAR(128);
DECLARE @indexName NVARCHAR(128);
DECLARE @avgFragmentation FLOAT;

DECLARE tableCursor CURSOR FOR
SELECT t.name, i.name, s.avg_fragmentation_in_percent
FROM sys.indexes i
INNER JOIN sys.objects t ON i.object_id = t.object_id
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE t.schema_id = SCHEMA_ID(@schemaName) AND s.avg_fragmentation_in_percent > 30;

OPEN tableCursor;

FETCH NEXT FROM tableCursor INTO @tableName, @indexName, @avgFragmentation;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Rebuilding index ' + @indexName + ' on table ' + @tableName + ' with fragmentation ' + CONVERT(NVARCHAR(10), @avgFragmentation) + '%';
    EXEC ('ALTER INDEX ' + @indexName + ' ON ' + @schemaName + '.' + @tableName + ' REBUILD;');
    FETCH NEXT FROM tableCursor INTO @tableName, @indexName, @avgFragmentation;
END

CLOSE tableCursor;
DEALLOCATE tableCursor;