Procédure stockée pour reconstruire ou réorganiser les index de base de données fragmentés

Une procédure stockée réutilisable que vous pouvez appeler pour réindexer n'importe quelle base de données en entrant le nom de la base de données.

Avoir pour but

Il s'agit d'une procédure stockée pratique que vous pouvez littéralement copier et coller dans votre base de données principale ou utilitaires, pour réindexer toutes les tables d'une base de données particulière.

La partie la plus utile de ceci est que vous entrez le nom de la base de données lorsque vous appelez la procédure, ce qui signifie que vous pouvez parcourir toutes vos bases de données en une seule fois en appelant la même procédure comme suit, si vous avez des serveurs liés, vous pouvez également l'appeler à partir de un autre serveur lorsqu'il est appelé correctement.

Multiple call SQL

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

Avoir pour but

Bien qu'il soit possible de l'exécuter pendant les heures de travail, il est toujours plus judicieux de l'exécuter lorsqu'il y a très peu de travail en cours sur le serveur.

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