DefragFragmentedIndexes-GUZEL OTOMATIK ONARIR VE MESAJ OLARAK YAZAR
Definition Description
-- Defragment indexes in descending order of -- the fragmentation grade. DECLARE @TableName sysname, @IndexID SMALLINT, @IndexName sysname; DECLARE @MinFragPer FLOAT, @FragScale AS VARCHAR ( 5 ); -- Filter for fragmentation grade in percent.
SET @MinFragPer = 10.0; -- Declaration of a cursor with all concered indexes -- exceed the defined min fragmentation grade. DECLARE Fragment CURSOR LOCAL FOR SELECT SCH.NAME + '.' + OBJ.NAME AS TableName, IDX.index_id AS IndexID, IDX.NAME AS IndexName, CONVERT ( VARCHAR ( 5 ), ROUND( FRG.avg_fragmentation_in_percent, 2 )) AS FragScale FROM sys.dm_db_index_physical_stats ( DB_ID (), NULL, NULL, NULL, NULL ) AS FRG INNER JOIN sys.objects AS OBJ ON FRG.object_id = OBJ.object_id INNER JOIN sys.schemas AS SCH ON OBJ.schema_id = SCH.schema_id INNER JOIN sys.indexes AS IDX ON FRG.index_id = IDX.index_id AND FRG.object_id = IDX.object_id WHERE IDX.index_id > 0 -- no Heaps AND FRG.avg_fragment_size_in_pages > 1 -- at least one page AND FRG.avg_fragmentation_in_percent > @MinFragPer ORDER BY FRG.fragment_count DESC, FRG.page_count DESC, FRG.avg_fragment_size_in_pages DESC;-- Open cursor with indexes & table names. OPEN Fragment; FETCH NEXT FROM Fragment INTO @TableName, @IndexID, @IndexName, @FragScale; WHILE @@FETCH_STATUS = 0 BEGIN-- Info print out. PRINT @Tablename + ' - ' + @IndexName + '(' + CONVERT ( VARCHAR ( 10 ), @IndexID ) + ') = ' + @FragScale + ' %'; DBCC INDEXDEFRAG ( 0, @TableName, @IndexID ) WITH NO_INFOMSGS; FETCH NEXT FROM Fragment INTO @TableName, @IndexID, @IndexName, @FragScale;
END;-- Close and dealloc cursor. CLOSE Fragment; DEALLOCATE Fragment;
