Konserwacja bazy danych MS SQL: Odbudowa i reorganizacja indeksów

Indeksy są kluczowym elementem poprawiającym wydajność bazy danych MS SQL Server. Administratorzy baz danych powinni regularnie konserwować indeksy, aby zapewnić optymalną wydajność.

Odbudowa i reorganizacja indeksów to dwie metody konserwacji indeksów i poprawy wydajności bazy danych. Można je przeprowadzać za pomocą złożonych zapytań SQL lub planów konserwacji SQL Server w SQL Server Management Studio.

Po utworzeniu indeksów należy zaplanować ich konserwację, ponieważ z czasem ulegają one fragmentacji, gdy dane są dodawane, aktualizowane lub usuwane z tabel.

Reorganizacja indeksu

Reorganizacja indeksu zużywa mniej zasobów niż jego odbudowa. Dlatego reorganizacja powinna być preferowaną metodą konserwacji indeksów, chyba że istnieje szczególny powód do użycia odbudowy. Reorganizacja jest zawsze operacją online, co oznacza, że nie są utrzymywane długoterminowe blokady obiektów – zapytania i aktualizacje na tabeli mogą być kontynuowane podczas reorganizacji.

Odbudowa indeksu

Odbudowa indeksu polega na jego usunięciu i ponownym utworzeniu. W zależności od typu indeksu i wersji silnika bazy danych MS SQL, operacja odbudowy może odbywać się offline lub online. Odbudowa offline trwa zwykle krócej, ale powoduje blokadę obiektu, co uniemożliwia dostęp do tabeli w czasie trwania operacji.

Odbudowa online nie wymaga blokady obiektu aż do końca operacji, gdzie blokada jest potrzebna na krótki czas. W zależności od wersji silnika MS SQL odbudowę online można uruchomić jako operację wznawialną (resumable). Taka operacja może być wstrzymana i wznowiona później bez utraty dotychczasowego postępu.

Spis treści

1. Sprawdzanie, czy należy przeprowadzić reorganizację czy odbudowę indeksów <a name="1"></a>

Przed wykonaniem jakichkolwiek operacji należy sprawdzić poziom fragmentacji indeksów. Informacje te można uzyskać, uruchamiając domyślny raport Index Physical Statistics w SQL Server Management Studio:

• Otwórz SQL Server Management Studio i wybierz odpowiednią bazę danych z aktywnościami (zwykle: <nazwa_hosta>.EkranActivityDB).

• Kliknij prawym przyciskiem myszy nazwę bazy danych > Reports > Standard Reports > Index Physical Statistics.

• Raport wyświetli wszystkie dostępne indeksy, poziom ich fragmentacji oraz rekomendowane działania.

Zalecenie:

• Fragmentacja < 30% → Reorganizacja

• Fragmentacja ≥ 30% → Odbudowa

2. Tworzenie i harmonogramowanie planu konserwacji do odbudowy indeksów <a name="2"></a>

Po ustaleniu poziomu fragmentacji bazy danych możesz utworzyć plan konserwacji:

• Otwórz Object Explorer, rozwiń folder Management, kliknij prawym przyciskiem myszy Maintenance Plans > New Maintenance Plan.

• Nadaj planowi nazwę, np. Index Rebuild Job.

• Z toolboxa przeciągnij element Rebuild Index Task i zmień jego nazwę (np. „Nocna konserwacja indeksów”).

• Kliknij prawym przyciskiem zadanie > Edit, a następnie:

  
  
  • W polu Connections wybierz Local server connection.

  • W polu Database(s) wybierz EkranActivityDB.

  • Nie zmieniaj domyślnych ustawień zadania odbudowy indeksów.

• Kliknij OK, by zapisać ustawienia.

• Kliknij ikonę kalendarza w menu, aby ustawić harmonogram (np. co 6 miesięcy o 1:00 w nocy).

• Kliknij OK, by zapisać harmonogram, a następnie zapisz plan konserwacji.

• Przetestuj plan, klikając prawym przyciskiem na Index Rebuild Job > Execute.