Procedimento armazenado para reconstruir ou reorganizar índices de banco de dados fragmentados

Um procedimento armazenado reutilizável que você pode chamar para re-indexar qualquer banco de dados, inserindo o nome do banco de dados.

Alvo

Este é um Procedimento armazenado acessível que você pode literalmente copiar e colar em seu banco de dados Master ou Utilities, para re-indexar todas as tabelas dentro de um banco de dados específico.

A parte mais útil disso é que você insira o nome do banco de dados quando você chama o procedimento, o que significa que você pode percorrer todos os seus bancos de dados de uma só vez, chamando o mesmo procedimento da seguinte maneira, se você tiver vinculado servidores, você também pode chamá-lo de Outro servidor quando chamado corretamente.

Multiple call SQL

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

Alvo

Embora seja possível executar isso durante as horas de trabalho, é sempre mais sensato executar isso quando há muito pouco trabalho no servidor.

SQL

USE [utilities]
GO
CREATE PROC [maint].DatabaseReIndex(@Database VARCHAR(100)) AS BEGIN
DECLARE @DbID SMALLINT=DB_ID(@Database)--Get Database ID
IF EXISTS(SELECT * FROM tempdb.sys.objects WHERE name='Indexes') BEGIN--Delete Temp Table if exists, then create
DROP TABLE TempDb.dbo.Indexes
END
CREATE TABLE TempDb.dbo.Indexes(IndexTempID INT IDENTITY(1,1),SchemaName NVARCHAR(128),TableName NVARCHAR(128),IndexName NVARCHAR(128),IndexFrag FLOAT)
EXEC ('USE '+@Database+';
INSERT INTO TempDb.dbo.Indexes(TableName,SchemaName,IndexName,IndexFrag)
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,sch.name,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),@IndexNameNVARCHAR(128),@IndexFrag FLOAT
SELECT * FROM TempDb.dbo.Indexes--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=IndexFragFROM TempDb.dbo.Indexes 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 TempDb.dbo.IndexesWHERE IndexTempID>@IndexTempID)
END
END
DROP TABLE TempDb.dbo.Indexes
GO