Przeorganizowanie indeksu

Przeorganizowanie indeksu to lżejsza alternatywa dla przebudowy, defragmentująca strony bez blokowania tabeli.

Na czym polega przeorganizowanie indeksu

Przeorganizowanie indeksu w Microsoft SQL Server jest operacją wykonywaną na istniejącym indeksie, której celem jest uporządkowanie jego stron na poziomie liści. W czasie normalnych operacji zapisu strony indeksu mogą się rozdzielać, a logiczna kolejność stron może przestać odpowiadać ich kolejności fizycznej. Taki stan określa się jako fragmentację indeksu. Przeorganizowanie porządkuje zawartość stron i ogranicza fragmentację bez tworzenia indeksu od początku.

Operację uruchamia się poleceniem ALTER INDEX ... REORGANIZE. Przykładowa składnia dla konkretnego indeksu w SQL Server wygląda następująco: ALTER INDEX IX_Zamowienia_Data ON dbo.Zamowienia REORGANIZE; Możliwe jest także przeorganizowanie wszystkich indeksów tabeli za pomocą wartości ALL. Polecenie działa przy istniejącej strukturze indeksu, dlatego nie zmienia jego definicji, kluczy, kolumn uwzględnionych ani warunku filtrowania.

Fragmentacja stron i wpływ na odczyt

Fragmentacja ma znaczenie przede wszystkim dla indeksów, których dane są odczytywane zakresami, na przykład według daty, numeru dokumentu albo identyfikatora. Jeżeli strony liści indeksu są ułożone w kolejności logicznej innej niż kolejność fizyczna, SQL Server może wykonywać więcej operacji wejścia-wyjścia podczas skanowania zakresu. Na dysku talerzowym może to oznaczać dodatkowe ruchy głowicy, natomiast na dysku SSD problem zwykle wiąże się z większą liczbą odczytów i gorszym wykorzystaniem pamięci podręcznej.

Poziom fragmentacji można sprawdzić za pomocą funkcji dynamicznego zarządzania sys.dm_db_index_physical_stats. Zwraca ona między innymi wartość avg_fragmentation_in_percent, czyli średni poziom fragmentacji, oraz page_count, określający liczbę stron indeksu. Sama wartość procentowa nie wystarcza do podjęcia decyzji. Niewielki indeks może mieć wysoki procent fragmentacji, ale jego uporządkowanie nie przyniesie zauważalnej korzyści. Należy uwzględnić również rozmiar indeksu, rodzaj zapytań i czas trwania operacji.

Przebieg operacji bez przebudowy struktury

Przeorganizowanie indeksu jest operacją online. SQL Server może wykonywać ją przy jednoczesnej pracy użytkowników, ponieważ nie wymaga pełnego, długotrwałego zablokowania całej tabeli na czas porządkowania. Nie oznacza to jednak braku blokad. Operacja może korzystać z blokad krótkotrwałych, zajmować zasoby procesora i generować obciążenie wejścia-wyjścia. Przy dużej aktywności zapisu przeorganizowanie może trwać dłużej, a jego efekt może być częściowo zmniejszany przez kolejne zmiany danych.

W odróżnieniu od przebudowy indeksu polecenie REORGANIZE nie pozwala na zmianę współczynnika wypełnienia stron przez opcję FILLFACTOR. Przeorganizowanie nie aktualizuje też statystyk kolumnowych w takim zakresie, jak pełna przebudowa indeksu. Po uporządkowaniu stron optymalizator zapytań może nadal korzystać ze starych lub nieaktualnych statystyk, dlatego diagnostyka planów wykonania powinna obejmować również stan statystyk. Operacja może być przerwana, a wykonane dotąd zmiany pozostają zachowane; nie jest to klasyczna transakcja wymagająca rozpoczęcia całego procesu od początku.

Dobór działania po pomiarze fragmentacji

Decyzję o przeorganizowaniu należy podejmować na podstawie pomiaru konkretnego indeksu, a nie samego faktu, że tabela często się zmienia. Warto sprawdzić, czy zapytania wykonują skanowania zakresowe, czy indeks jest wystarczająco duży, aby fragmentacja miała praktyczne znaczenie, oraz czy czas operacji mieści się w dostępnych zasobach. Dla indeksów małych albo używanych głównie do wyszukiwania pojedynczych wierszy poprawa może być pomijalna. Po zmianie należy porównać czas zapytań, liczbę odczytów logicznych i plan wykonania.

Przeorganizowanie nie naprawia problemów wynikających z nieprawidłowego indeksowania, błędnych warunków zapytań, niedopasowanych typów danych ani zbyt małej ilości pamięci. Nie zastępuje również analizy blokad i oczekiwań, ponieważ wolne zapytanie może wynikać z konkurencyjnych modyfikacji, a nie z fragmentacji. W środowisku produkcyjnym operację powinno się poprzedzić kopią konfiguracji, identyfikacją właściwego indeksu i obserwacją obciążenia. Przy bardzo dużej fragmentacji lub potrzebie zmiany parametrów indeksu właściwym działaniem może być przebudowa, ale jest to odrębna operacja o innym profilu zasobów i blokowania.