V minulém článku (viz. Fragmentace indexů) jsem popisoval, jak zjistit fragmentaci indexů přes skript využívající systémové tabulky. Podle doporučení Microsoftu bychom měli indexy s fragmentací mezi 5 – 30 % reorganizovat (REORGANIZE) a indexy nad 30 % rebuildovat (REBUILD). Využijeme zde skript z minulého článku a vytvoříme automatický skript. Oprava sql indexů provedená tímto způsobem je jednoduchá a rychlá.

Automatická oprava indexů pomocí skriptu v SQL Server

Skript funguje následovně:

• Založíme si přípravnou tabulku @TablesToMaintenance
• Do tabulky si připravíme indexy, které budeme opravovat, tzn. všechny indexy, které mají fragmentaci > než 5 %. Doplníme si také operaci, kterou budeme provážet (REBUILD vs REORGANIZE)
• Na konci s použitím kurzoru provedeme požadovanou operaci postupně nad každým indexem (pouštíme dynamický SQL dotaz)

DECLARE @TablesToMaintenance AS TABLE
(
  DB_Schema VARCHAR(255),
  DB_Table VARCHAR(255),
  DB_Index VARCHAR(255),
  INDEX_Fragmentation FLOAT,
  OperationToMaintenance VARCHAR(255)
)
-----------Příprava fragmentovaných tabulek----
INSERT INTO @TablesToMaintenance(
  DB_Schema,
  DB_Table,
  DB_Index,
  INDEX_Fragmentation,
  OperationToMaintenance
)
SELECT
   sch.name AS DB_Schema,
   obj.name AS DB_Table,
   ind.name AS DB_Index,
   stat.avg_fragmentation_in_percent AS INDEX_Fragmentation,
   CASE
     WHEN avg_fragmentation_in_percent between 5 and 30 THEN 'REORGANIZE'
     WHEN avg_fragmentation_in_percent >30 THEN 'REBUILD'
   END AS OperationToMaintenance
FROM
   sys.objects obj
   LEFT JOIN sys.schemas sch
     ON obj.schema_id= sch.schema_id
   LEFT JOIN sys.indexes ind
     ON obj.object_id=ind.object_id
   LEFT JOIN sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, 'LIMITED') AS stat
     ON ind.object_id=stat.object_id
     AND ind.index_id=stat.index_id
WHERE
   obj.type='U'
   AND ind.index_id > 0
   AND avg_fragmentation_in_percent>5

-----------Kurzor---------------------
DECLARE @SQL VARCHAR(255)
DECLARE @DB_Index VARCHAR(255)
DECLARE @DB_Schema VARCHAR(255)
DECLARE @DB_Table VARCHAR(255)
DECLARE @OperationToMaintenance VARCHAR(255)

DECLARE index_cursor CURSOR FOR (SELECT DB_Index,DB_Schema,DB_Table,OperationToMaintenance FROM @TablesToMaintenance)

OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @DB_Index, @DB_Schema, @DB_Table, @OperationToMaintenance
WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @SQL= 'Alter INDEX [' + @DB_Index + '] ON [' + @DB_Schema + '].[' + @DB_Table + '] ' + @OperationToMaintenance
    EXECUTE (@SQL)
    FETCH NEXT FROM index_cursor INTO @DB_Index, @DB_Schema, @DB_Table, @OperationToMaintenance
  END
CLOSE index_cursor
DEALLOCATE index_cursor

Enjoy

Edit 11.12.2018 – upraven skript uvnitř kurzoru – sestavení dynamického SQL. Je vhodné používat hranaté závorky, protože některé automaticky vytvořené sql indexy mají různé zlobivé znaky nebo mezery a execute SQL s podobnými znaky končí failem. Do budoucna si dávám předsevzetí dávat hranaté závorky všude kam patří, je to optional a profesionálnější přístup.