Stored procedure per ricostruire o riorganizzare gli indici di database frammentati
Una stored procedure riutilizzabile che è possibile chiamare per reindicizzare qualsiasi database inserendo il nome del database.
Scopo
Questa è una comoda procedura memorizzata che puoi letteralmente copiare e incollare nel tuo database principale o di utilità, per reindicizzare tutte le tabelle all'interno di un database particolare.
La parte più utile di questo è che inserisci il nome del database quando chiami la procedura, il che significa che puoi scorrere tutti i tuoi database in una volta sola chiamando la stessa procedura come segue, se hai server collegati puoi anche chiamarla da un altro server se chiamato correttamente.
Multiple call SQL
EXEC [utilities].[maint].DatabaseReIndex 'YourDatabaseName'
EXEC [utilities].[maint].DatabaseReIndex 'YourDatabaseName2'Scopo
Sebbene sia possibile eseguirlo durante l'orario di lavoro, è sempre più sensato eseguirlo quando c'è pochissimo lavoro da fare sul server.
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