28/01/2015

Jak znaleźć brakujące indeksy w bazie danych?

Home

Optymalizacja bazy danych i zapytań to temat rozległy i szeroki jak morze i nie jedną książka napisano na ten temat. Ja dzisiaj napiszę o dosyć prostej technice pozwalającej znaleźć brakujące indeksy w bazie danych MSSQL. Zapewne każdy korzystający z MSSQL Management Studio wie, że można poprosić o wyświetlenie planu wykonania zapytania (opcje Dispaly Actual Execution Plan oraz Include Actual Execution Plan). Dodatkowo po wykonaniu zapytania MSSQL zasugeruje nam jakich indeksów brakuje.

Fajnie, ale co w sytuacji kiedy widzimy, że nasza aplikacja działa wolno. Mamy podejrzenie, że problem dotyczy bazy danych, ale przecież nie będziemy uruchamiali każdego możliwego zapytania w SSMS. W takiej sytuacji możemy de facto użyć tej samej funkcjonalności co w przypadku uruchamiania zapytania z SSMS. Mam tutaj na myśli Missing Indexes Feature, która jest cechą MSSQL, a nie środowiska SSMS. Informacje o brakujących indeksach silnik bazy danych odkłada mianowicie w kilku widokach systemowych z rodziny sys.dm_db_missing_index_*. Wystarczy więc uruchomić aplikację i zobaczyć jakie indeksy sugeruje nam MSSQL. Ja w tym celu używam zapytania, które znalazłem na blogu SQL Authority.

Przykład z życia. Ostatnio musiałem zoptymalizować pewne obliczenia i postąpiłem dokładnie jak napisałem wyżej. Uruchomiłem w aplikację, zmierzyłem czas obliczeń, zapisałem czas ich uruchomienia i zakończenia, a następnie wyświetliłem listę sugerowanych indeksów do utworzenia. Było ich 6. Na początek odrzuciłem te o niskiej wartości w kolumnie Avg_Esitmated_Impact. Z pozostałych indeksów 2 różniły się tym, że jeden miał klauzulę INCLUDE, a drugi nie. Stwierdziłem, że w pierwszym podejściu skupię się na jednym.

W dalszej kolejności wykonałem testy aby zobaczyć jaki uzysk daje założenie każdego z tych 3 indeksów, a także 2 z nich czy wszystkich 3. Okazało się, że zastosowanie jednego z nich skrócił czas obliczeń o ponad 30%, a pozostałe dwa o małe kilka. Dla rzetelności testy powtórzyłem, a wyniki uśredniłem. Na koniec dokładnie przeanalizowałem proponowany indeks i porównałem go do indeksów już utworzonych na tabeli. Okazało się, że istniał już bardzo podobny indeks. Konkretnie, MSSQL zaproponował coś takiego:
CREATE INDEX IX_Test ON dbo.Table(Col_1, Col_2) INCLUDE (Col_4);
A istniejący indeks wyglądał tak:
CREATE INDEX IX_Test ON dbo.Table(Col_1, Col_2, Col_3);
Wystarczyło, wieć go zmodyfikować w następujący sposób:
CREATE INDEX IX_Test ON dbo.Table(Col_1, Col_2, Col_3) INCLUDE (Col_4);
Na koniec sprawdziłem jak taka modyfikacja wpływa na operacje wstawiania/aktualizacji danych do/w docelowej tabeli. W tym celu napisałem zapytania wstawiające setki tysięcy rekordów do tej tabeli, a także takie, które modyfikuje kolumnę Col_4.. Wyniki pokazały niewielkie spadek wydajności. Był on znacznie mniejszy niż zysk przy odczycie danych, a po drugie wiedziałem, że w praktyce omawiana tabela jest częściej czytana niż modyfikowana.

Przy pracy z Missing Indexes Feature warto wiedzieć o kilku dodatkowych rzeczach. MSSQL może nam zasugerować wiele brakujących indeksów i nie koniecznie wszystkie muszą dotyczyć zapytać wykonanych przez nas. Aby wyeliminować ten problem sugeruję wykonywanie takich ćwiczeń na dedykowanej bazie danych. Przydatne będą też kolumny last_user_seek oraz last_user_scan z widoku sys.dm_db_missing_index_group_stats. Zawierają one informacje o tym kiedy dany brakujący indeks był potrzebny. Po pierwsze podany czas możemy porównać z czasem uruchomienia/zakończenia obliczeń i odrzucić te indeksy, które nie mieszczą się w tym zakresie. Po drugie te czasy mogą zgrubnie wskazać, w którym momencie działania aplikacji występuje problem. Napisałem, że przy wyborze indeksów do dalszej analizy bazowałem na kolumnie Avg_Esitmated_Impact. Trzeba na to jednak uważać. Ta wartość to tylko pewne przybliżenie i może nas wyprowadzić na manowce. Z 3 indeksów jakie wybrałem do dalszej analizy największy zysk miał ten o najmniej wartości w tej kolumnie.

Końcowa uwaga jest taka, że Missing Indexes Feature to pomocna rzecz, ale nie jest to magiczna formuła, która rozwiąże wszystkie problemy za nas. Ma też swoje ograniczenia, o których należy wiedzieć.

Podsumowując:
  • MSSQL sugeruje brakujące indeksy.
  • Brakujące indeksy można odczytać z bazy danych.
  • Testy wydajności należy powtórzyć kilka razy.
  • Testy wydajności dobrze wykonywać w dedykowanym do tych celu środowisku.
  • Missing Indexes Feature to nie magiczna formuła i ma swoje ograniczenia.
  • Proponowane brakujące indeksy należy zawsze poddać analizie i porównać do istniejących indeksów.
  • Należy pamiętać, że indeksy spowalniają operacje aktualizacji i wstawiania danych.
  • Wartość w kolumnie Avg_Esitmated_Impact należy traktować ostrożnie.

4 comments:

Michał said...

Dzięki za wytłumaczenie ale nie do końca wszystko rozumiem i nie wiem czy tylko ja mam z tym problem? Dlaczego testy wydajności należy powtarzać, ile razy? Czy wyniki mogą się zmieniać?

Michał Komorowski said...

Wyniki mogą się zmieniać na przykład w zależności od obciążenia maszyny, na której wykonujemy testy. Nawet jeśli mamy pewność, że w danym momencie jesteśmy jedynym użytkownikiem maszyny to na wyniki testu może też wpłynąć aktywność systemu operacyjnego. W związku z tym proponuję powtórzyć testy i uśrednić wyniki. Ile razy? Sądzę, że 3 razy to rozsądne minimum. Przy czym jeśli zauważymy, że z testu na test wyniki znacznie się od siebie różnią to zwiększyłbym liczbę testów do 5, a nawet więcej.

bmm.com.pl/ said...

Też kiedyś miałem podobny problem ze znalezieniem indeksów. Długo szukałem rozwiązania, a okazało się, że jest to dziecinnie proste. Czasem coś jest trudne, zanim stanie się łatwe ;) Fajnie, że ktoś dodaje takie publikacje, dzięki temu w internecie łatwo znaleźć potrzebne informacje

Karol said...

Dzięki za informacje. Szkoda, że zmarnowałem tyle czasu na poszukiwaniu rozwiązania, skoro było na wyciągnięcie ręki :)

Post a comment