Chạy REBUILD/REORGANIZE tự động theo mức fragmentation

DECLARE @TableName NVARCHAR(128);
DECLARE @IndexName NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);
DECLARE @FragmentationPercent FLOAT;
DECLARE @ObjectId INT;
DECLARE @IndexId INT;

IF OBJECT_ID('tempdb..#FragmentedIndexes') IS NOT NULL DROP TABLE #FragmentedIndexes;

SELECT
OBJECT_ID AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragmentationPercent
INTO #FragmentedIndexes
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE index_id > 0;

DECLARE cur CURSOR FOR
SELECT ObjectId, IndexId, FragmentationPercent
FROM #FragmentedIndexes;

OPEN cur;
FETCH NEXT FROM cur INTO @ObjectId, @IndexId, @FragmentationPercent;

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@TableName = QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectId)) + '.' + QUOTENAME(OBJECT_NAME(@ObjectId)),
@IndexName = QUOTENAME(i.name)
FROM sys.indexes i
WHERE i.object_id = @ObjectId AND i.index_id = @IndexId;

IF @FragmentationPercent >= 30
SET @sql = 'ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD;';
ELSE
SET @sql = 'ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REORGANIZE;';

EXEC (@sql);

FETCH NEXT FROM cur INTO @ObjectId, @IndexId, @FragmentationPercent;
END

CLOSE cur;
DEALLOCATE cur;

DROP TABLE #FragmentedIndexes;

 

• 2) Sau đó chạy:

EXEC sp_updatestats;