SQl Defragmantasyon Otomatik Onarım Scripti

--Defragmantasyon oranı sorgulama ve otomatik düzeltme
Use TEST_DB
GO


DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90

 DECLARE cr_Kayitlar CURSOR FOR

 SELECT OBJECT_NAME (ps.object_id) as ObjectName --,ps.avg_fragmentation_in_percent
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') ps
  INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id
  WHERE avg_fragmentation_in_percent > 5 AND ps.index_id > 0
  ORDER BY avg_fragmentation_in_percent desc
 OPEN cr_Kayitlar;
 FETCH NEXT FROM cr_Kayitlar INTO @Table;
 WHILE @@FETCH_STATUS=0
  BEGIN
   --ALTER INDEX ALL ON (@Table) REBUILD
   SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)

   --ALTER INDEX ALL ON @TabloAdi REORGANIZE --WITH (ONLINE = ON)
  FETCH NEXT FROM cr_Kayitlar INTO @Table;
 END
CLOSE cr_Kayitlar
DEALLOCATE cr_Kayitlar

Detaylar