DEFRAG all Indexes in a Database

Gerade bei größeren Datenbanken ist es sehr mühsam alle Indexe manuell zu warten. Folgendes Script ermittelt alle Indexe mit einer Fragmentierung >= 5 % und reorganisiert diese oder erzeugt diese neu.

DROP TABLE IF EXISTS #defragind
SELECT   * ,
         cast(NULL AS datetime) AS [Startdate],
         cast(NULL AS datetime) AS [Enddate],
         CASE
                  WHEN avg_fragmentation_in_percent >= 30 THEN 'Rebuild'
                  ELSE
                           CASE
                                    WHEN avg_fragmentation_in_percent > = 5 THEN 'Reorganize'
                                    ELSE 'ignore'
                           END
         END AS [Action],
         CASE
                  WHEN avg_fragmentation_in_percent >= 30 THEN 'ALTER INDEX [' + [Index] + '] on [' + 
                       [schema] + '].[' + [table] + '] REBUILD'
                  ELSE
                           CASE
                                    WHEN avg_fragmentation_in_percent > = 5 THEN 'ALTER INDEX [' + 
                                         [Index] + '] on [' + [schema] + '].[' + [table] + '] REORGANIZE'
                                    ELSE NULL
                           END
         END AS [Command]
INTO     #defragind
FROM     (
                    SELECT     row_number() OVER (ORDER BY t.object_id) AS [ID],
                               s.NAME                                   AS 'Schema',
                               t.NAME                                   AS 'Table',
                               i.NAME                                   AS 'Index',
                               ddips.avg_fragmentation_in_percent,
                               ddips.page_count
                    FROM       sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ddips
                    INNER JOIN sys.tables t
                    ON         t.object_id = ddips.object_id
                    INNER JOIN sys.schemas s
                    ON         t.schema_id = s.schema_id
                    INNER JOIN sys.indexes i
                    ON         i.object_id = ddips.object_id
                    AND        ddips.index_id = i.index_id
                    WHERE      ddips.database_id = db_id()
                    AND        i.NAME IS NOT NULL
                    AND        ddips.avg_fragmentation_in_percent >= 5 ) AS q
ORDER BY avg_fragmentation_in_percent 



DECLARE @max AS INT = 
(
       SELECT Max(id)
       FROM   #defragind)


DECLARE @i AS   INT = 1
DECLARE @sql AS NVARCHAR(max) = NULL

WHILE @i < @max
BEGIN
	print @i

  UPDATE #defragind
  SET    [startdate] = Getdate()
  WHERE  id = @i

  SET    @sql =
         (
                SELECT command
                FROM   #defragind
                WHERE  id = @i)
  print @SQL;
  EXEC(@SQL);

  UPDATE #defragind
  SET    [enddate] = Getdate()
  WHERE  id = @i

  SET    @i = @i + 1
END

SELECT *
FROM   #defragind