SQL Server パーティション構成したDBで断片化の状態によってインデックスを再構成または再構築する方法（再構築はオンラインで実施）

#既存の断片化対応から改善
SQL ServerをEnterprise Editionに変えたのでオンラインオプションをつけた。
パーティション構成にすると指定オプションが増える。

#作業

• 「メンタナンス プラン」でスケジュール構成
  • T-SQLステートメントの実行タスク

USE target-db
EXEC up_UpdateIndex

• up_UpdateIndex

ALTER PROCEDURE [dbo].[up_UpdateIndex]
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @cnt int, @i int, @sql nvarchar(2000), @isOnline tinyint, @tar varchar(2000)
	DECLARE @SchemaName nvarchar(128), @ObjectName nvarchar(128), @IndexName nvarchar(128)
		, @avg_fragmentation_in_percent float

	--パーティション構成テーブルはパーティション番号を指定するため
	SET @tar = '[table_1],[table_2],[table_3],......'

	DECLARE @tbl table(
		ID int identity(1,1)
		, SchemaName nvarchar(128)
		, ObjectName nvarchar(128)
		, IndexName nvarchar(128)
		, avg_fragmentation_in_percent float
	);

	INSERT INTO @tbl(SchemaName, ObjectName, IndexName, avg_fragmentation_in_percent)
	SELECT QUOTENAME(S.name) AS SchemaName, QUOTENAME(O.name) AS ObjectName,
		QUOTENAME(I.name) AS IndexName, PS.avg_fragmentation_in_percent
	FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS PS
		INNER JOIN sys.objects AS O ON PS.object_id = O.object_id
		INNER JOIN sys.schemas AS S ON O.schema_id = S.schema_id
		INNER JOIN sys.indexes AS I ON PS.object_id = I.object_id AND PS.index_id =  I.index_id
	WHERE avg_fragmentation_in_percent >= 1.0 AND PS.index_id > 0;

	SELECT @cnt = Count(*) FROM @tbl;

	SET @i = 1;

	WHILE @i <= @cnt
	BEGIN
		SET @isOnline = 0

		SELECT @SchemaName = SchemaName, @ObjectName = ObjectName
			, @IndexName = IndexName, @avg_fragmentation_in_percent =  avg_fragmentation_in_percent
		FROM @tbl
		WHERE ID = @i;

		IF @avg_fragmentation_in_percent < 30.0
		BEGIN
			SET @sql = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' +  @ObjectName + N' REORGANIZE';
		END
		ELSE
		BEGIN
			--断片化が30%以上だと再構築をオンラインで実施
			SET @sql = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' +  @ObjectName + N' REBUILD';

			set @isOnline = 1
		END

		--この部分はパーティション構成ではなければ無視
		IF (SELECT CHARINDEX(@ObjectName, @tar)) > 0
		BEGIN
			SET @Sql += N' PARTITION = 1'
		END

		IF @isOnline = 1
		BEGIN
			SET @Sql += N' WITH(ONLINE = ON)'
		END

		EXEC (@Sql);
		SET @i = @i + 1;
	END
END

#参考
https://sql55.com/query/reorganize-rebuild-index.php