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.

Doporučení Microsoftu pro fragmentaci indexů

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)

Jak zjistit fragmentaci SQL indexu

Fragmentaci můžeme zjistit 2ma způsoby

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

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