Podejść do tematu zarządzania indeksami w bazach danych jest kilka. Jak kiedyś planowałem sposób w jaki będą one utrzymywane w porządku, najpopularniejszym rozwiązaniem było wykonywanie Reorganize dla indeksów z fragmentacją między 10 a 30 procent, oraz Rebuild gdy fragmentacja przekraczała 30%.
Dysk SSD/NVME zmieniły postrzeganie fragmentacji
Przy dyskach mechanicznych istotne było układanie danych tak, by wykonywanie skanowania indeksu w miarę możliwości wykonywało się sekwencyjnie. Dyski półprzewodnikowe mają dużo lepsze czasy losowego dostępu, może więc rzeczywiście nie ma co się przejmować uporządkowaniem danych?
Trzeba mieć jeszcze na uwadze że dane powinniśmy chcieć trzymać w pamięci RAM. Kiedyś to było trudniejsze, w obecnych czasach to się zmieniło i nie jest już tak trudne by serwery miały ilość pamięci umożliwiających przechowywanie całej baz (a przynajmniej części w bieżącym użytkowaniu).
Tutaj wrzucę co mówią mądrzejsi:
Stop Worrying About SQL Server Fragmentation by Brent Ozar.
W większości środowisk zmieniłem podejście na trochę inne.
Przeliczanie statystyk
Codziennie przeliczam statystyki na indeksach, które mają powyżej 5 mln rekordów. Ustawiam również limit na 2 godziny, próbkowanie 100% i odpalam to poza godzinami biznesowymi. Tu mam zrobiony automat przy instalacji SQLi, że na każdym wykonuje się to o losowej godzinie w okienku serwisowym. Przy kilka instancjach nie ma to znaczenia, przy kilkuset istotne jest by nie dobić wszystkich blach w jednym momencie.
Ta ilość rekordów została przeze mnie wyestymowana empirycznie na podstawie baz którymi się opiekuję. Gdyby okazało się że 2 godziny to za mało, to mogę albo wydłużyć ten timeout, albo zmniejszyć górny limit ilości wierszy.
Ponadto raz w tygodniu (w soboty) przeliczam statystyki dla indeksów posiadających ponad 5 mln rekordów, tu już z limitem czasu 6 godzin.
Przebudowa indeksów
Nie robię w ogóle reorganizacji indeksów (Index reorganize), ale puszczam przebudowę indeksów co sobotę.
Warunkami dla tej przebudowy jest fragmentacja na poziomie co najmniej 10%, ale liczona na podstawie fragmentacji wew., a nie tego co domyślnie podaje SQL.
Dla przebudowy indeksów ustawiam limit 6 godzin, utrzymuję wzorcowy poziom Fill Factora (część tabel ma 70%).
Z jakich skryptów SQL korzystam?
Korzystam ze skryptów tworzonych przez Ola Hallengrena – SQL Server Maintenance Solution
Ktoś uważny może zauważyć że nie ma tam części funkcjonalności na które się powołuję (liczenie internal fragmentation, przeliczanie statystyk bazując na ilości rekordów i zachowywanie Fill Factora przy przebudowie indeksów). Te zmiany zostały przeze mnie dodane jako Pull requesty i oczekują na włączenie do jego rozwiązania.
