Riprendo l’esempio del precedente Index Fragmentation per realizzare una piccola stored procedure USP_ExecReorgRebuildIndex() in grado di eseguire la deframmentazione degli indici con avg_fragmentation_in_percent maggiore del 10%. Dopo ogni ALTER INDEX viene eseguito anche un UPDATE STATISTICS per aggiornare le informazioni riguardanti la distribuzione dei Key Values nella tabella indicizzata.
IF OBJECT_ID('USP_ExecReorgRebuildIndex', 'P') IS NOT NULL
DROP PROCEDURE dbo.USP_ExecReorgRebuildIndex
GO
CREATE PROCEDURE dbo.USP_ExecReorgRebuildIndex
(@DataBaseName AS VARCHAR(128),
@Exec AS INT,
@Print AS INT)
AS BEGIN
/*
Parametri: @DataBaseName = Nome database
@Exec = Esegui REORGANIZE o REBUILD dell'indice (con @Exec = -1 esegue gli ALTER INDEX, con @Exec <> -1 non esegue ALTER INDEX)
@Print = Stampa (con @Print = -1 stampa i comandi ALTER INDEX, con @Print <> -1 non stampa i comandi ALTER INDEX)
*/
DECLARE @index_id AS INT,
@index_name SYSNAME,
@action_to_do AS VARCHAR(1024),
@avg_fragmentation_in_percent FLOAT,
@table_name AS VARCHAR(512)
DECLARE CUR CURSOR FOR
SELECT a.index_id, b.name index_name, a.avg_fragmentation_in_percent,
CASE WHEN (a.avg_fragmentation_in_percent <= 30)
THEN 'ALTER INDEX '+LTRIM(RTRIM(b.name))+' ON '+LTRIM(RTRIM(s.name))+'.'+LTRIM(RTRIM(c.name))+' REORGANIZE'
ELSE 'ALTER INDEX '+LTRIM(RTRIM(b.name))+' ON '+LTRIM(RTRIM(s.name))+'.'+LTRIM(RTRIM(c.name))+' REBUILD'
END AS action_to_do,
LTRIM(RTRIM(s.name))+'.'+LTRIM(RTRIM(c.name)) AS table_name
FROM sys.dm_db_index_physical_stats (DB_ID(@DataBaseName),NULL,NULL,NULL,NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
JOIN sys.objects AS c ON c.object_id = a.object_id
JOIN sys.schemas AS s ON s.schema_id = c.schema_id
WHERE (b.name IS NOT NULL)
AND (a.avg_fragmentation_in_percent > 10)
ORDER BY a.avg_fragmentation_in_percent DESC
OPEN CUR
FETCH NEXT FROM CUR INTO @index_id, @index_name, @avg_fragmentation_in_percent, @action_to_do, @table_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@Exec = -1)
BEGIN
EXEC(@action_to_do)
EXEC('UPDATE STATISTICS ' + @table_name + ' ' + @index_name)
END
IF (@Print = -1)
BEGIN
PRINT @action_to_do
PRINT 'UPDATE STATISTICS ' + @table_name + ' ' + @index_name
PRINT '@'
END
FETCH NEXT FROM CUR INTO @index_id, @index_name, @avg_fragmentation_in_percent, @action_to_do, @table_name
END
CLOSE CUR
DEALLOCATE CUR
END