SQL Fragmentace indexů – ukázka + sql skripty

Indexy jsou základním pilířem optimalizace SQL dotazů z databáze. Pomocí nich můžeme výrazně zrychlovat operace typu čtení. Bohužel se nám v čase indexy kazí – fragmentují a uspořádání indexů se zhoršuje. Fragmentace indexu je dána tím, že do tabulky se postupně vkládají nebo mažou záznamy a indexy se poté už neoptimalizují. To znamená že jednotlivé pages indexu mají spoustu volného místa a pokud se dotazujeme do tabulky, tak musíme scanovat více pages, než bychom museli kdyby byla fragmentace v pořádku.

Pro údržbu indexu doporučuje Microsoft tyto pravidla pro fragmentaci v %:

  1. Pokud je  mezi 5 a 30 %, měli bychom provést reorganizaci indexu (REOGRANIZE)
  2. Pokud je větší než 30 %, tak se provádí rebuild indexu (REBUILD)

Fragmentaci můžeme zjistit 2ma způsoby:

  • Přes vlastnosti daného indexu nebo lepší možnost

Fragmentace indexů - ukázka

  • Přes skript (viz níže)

Fragmentace indexů přes skript

Níže najdete skript na vyhledání fragmentovaných indexů, tedy indexů jejichž fragmentace je větší než 5 %. Skript je potřeba pustit nad databází, kde chceme fragmentaci zjišťovat. SQL dotaz obsahuje i doporučení, co s indexem provést (REORGANIZE vs REBUILD)

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

Oprava indexů – sql syntaxe

Reorganizace indexu: ALTER INDEX <nazev_indexu> ON <nazev_tabulky> REORGANIZE;

Rebuild indexu: ALTER INDEX <nazev_indexu> ON <nazev_tabulky> REBUILD;

V dalším článku ukážu, jak lze indexy opravovat automaticky prostřednictvím SQL skriptu – článek najdete zde – Automatická oprava indexů

_______________________________________________________________________________________________
Intelligent technologies - podniková řešení a školení
SQL Fragmentace indexů – ukázka + sql skripty
Hodnocení

Napsat komentář

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

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