SQL Automatická oprava indexů v SQL Server + skript

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 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

_______________________________________________________________________________________________
Intelligent technologies - podniková řešení a školení
SQL Automatická oprava indexů v SQL Server + skript
5 (100%) 1 vote

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

This site uses Akismet to reduce spam. Learn how your comment data is processed.