断片化されたデータベースインデックスを再構築または再編成するためのストアドプロシージャ

データベースの名前を入力することにより、任意のデータベースのインデックスを再作成するために呼び出すことができる、再利用可能なストアドプロシージャ。

目的

これは、マスターデータベースまたはユーティリティデータベースに文字通りコピーして貼り付け、特定のデータベース内のすべてのテーブルのインデックスを再作成できる便利なストアドプロシージャです。

これの最も便利な部分は、プロシージャを呼び出すときにデータベース名を入力することです。つまり、次のように同じプロシージャを呼び出すことで、すべてのデータベースを一度にループできます。リンクされたサーバーがある場合は、そこから呼び出すこともできます。正しく呼び出された場合は別のサーバー。

Multiple call SQL

EXEC [utilities].[maint].DatabaseReIndex 'YourDatabaseName'
EXEC [utilities].[maint].DatabaseReIndex 'YourDatabaseName2'

目的

勤務時間中にこれを実行することは可能ですが、サーバー上で実行されている作業がほとんどないときにこれを実行する方が常に賢明です。

SQL

USE [utilities]
GO
CREATE PROC [maint].DatabaseReIndex(@Database VARCHAR(100)) AS BEGIN
DECLARE @DbID SMALLINT=DB_ID(@Database)--Get Database ID
DECLARE @I TABLE (IndexTempID INT IDENTITY(1,1),SchemaName NVARCHAR(128),TableName NVARCHAR(128),IndexName NVARCHAR(128),IndexFrag FLOAT)

INSERT INTO @I
EXEC ('USE '+@Database+';
SELECT sch.name,OBJECT_NAME(ind.OBJECT_ID) AS TableName,ind.name IndexName,indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats('+@DbID+', NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
INNER JOIN sys.objects obj on obj.object_id=indexstats.object_id
INNER JOIN sys.schemas as sch ON sch.schema_id = obj.schema_id
WHERE indexstats.avg_fragmentation_in_percent > 10 AND indexstats.index_type_desc<>''HEAP''
ORDER BY indexstats.avg_fragmentation_in_percent DESC')--Get index data and fragmentation, set the percentage as high or low as you need
DECLARE @IndexTempID BIGINT=0,@SchemaName NVARCHAR(128),@TableName NVARCHAR(128),@IndexName NVARCHAR(128),@IndexFrag FLOAT
SELECT * FROM @I--View your results, comment out if not needed...
-- Loop through the indexes
WHILE @IndexTempID IS NOT NULL BEGIN
    SELECT @SchemaName=SchemaName,@TableName=TableName,@IndexName=IndexName,@IndexFrag=IndexFrag FROM @I WHERE IndexTempID=@IndexTempID
    IF @IndexName IS NOT NULL AND @SchemaName IS NOT NULL AND @TableName IS NOT NULL BEGIN
    IF @IndexFrag<30. BEGIN--Low fragmentation can use re-organise, set at 30 as per most articles
    PRINT 'USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName + N' REORGANIZE'
    EXEC('USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName + N' REORGANIZE')
    END
    ELSE BEGIN--High fragmentation needs re-build
    PRINT 'USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName + N' REBUILD'
    EXEC('USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName + N' REBUILD')
    END
    END
    SET @IndexTempID=(SELECT MIN(IndexTempID) FROM @I WHERE IndexTempID>@IndexTempID)
END
GO