Query Store a indeksy

Query Store a indeksy — analiza historii planów pomaga ocenić, czy zmiana indeksu poprawiła wydajność zapytania.

Jak Query Store pokazuje wpływ indeksu na plan zapytania

Query Store w Microsoft SQL Server zapisuje historię wykonywania zapytań, planów wykonania oraz zbiorczych statystyk wydajności. Dzięki temu analiza nie ogranicza się do obserwacji bieżącego planu w chwili wystąpienia problemu. Można porównać zachowanie tego samego zapytania przed utworzeniem indeksu, po jego utworzeniu, a także po zmianie lub usunięciu indeksu. W szczególności przydatne są informacje o czasie trwania, liczbie wykonań, zużyciu procesora, odczytach logicznych i zapisie, o ile dana statystyka jest gromadzona przez konfigurację Query Store i wersję używanego SQL Server.

Indeks nie poprawia automatycznie każdego zapytania. Optymalizator może wybrać indeks wtedy, gdy jego koszt szacowany jest niższy od kosztu skanowania tabeli lub innego indeksu. Na wybór wpływają między innymi predykaty w klauzuli WHERE, kolumny używane w JOIN, sortowanie, grupowanie, rozkład wartości oraz aktualność statystyk. Query Store pozwala sprawdzić, czy po zmianie rzeczywiście pojawił się nowy plan, czy plan został tylko ponownie skompilowany, a także czy poprawa dotyczy typowego wykonania, czy wyłącznie pojedynczego przypadku.

Porównanie planów przed i po zmianie indeksu

Analizę należy rozpocząć od identyfikacji konkretnego zapytania w Query Store, a nie od samej nazwy indeksu. To samo zapytanie może mieć wiele planów, ponieważ SQL Server dobiera plan zależnie od parametrów, statystyk, ustawień bazy danych i dostępnych indeksów. W historii można zestawić plan używany przed zmianą z planem zarejestrowanym po zmianie. Należy zwrócić uwagę, czy operator Index Scan został zastąpiony przez Index Seek, czy zmniejszyła się liczba odczytywanych wierszy oraz czy zniknęły kosztowne operatory sortowania, skanowania lub dodatkowego wyszukiwania klucza.

Samo wystąpienie operatora Index Seek nie jest jednak dowodem poprawy. Wyszukiwanie może zwrócić dużą część tabeli, a następnie wymagać dodatkowych operacji. Indeks może też prowadzić do operatora Key Lookup, czyli odczytu brakujących kolumn z indeksu klastrowanego lub sterty dla wielu znalezionych wierszy. W takim przypadku warto sprawdzić, czy indeks ma właściwe kolumny kluczowe oraz czy kolumny używane tylko do odczytu powinny zostać dodane jako kolumny dołączone przez INCLUDE. Decyzję należy oprzeć na rzeczywistych statystykach z Query Store, a nie wyłącznie na procentowym koszcie operatorów pokazywanym w planie.

Statystyki wykonania i ryzyko błędnej interpretacji

Query Store przechowuje statystyki w przedziałach czasowych, dlatego wyniki trzeba porównywać dla porównywalnych okresów i obciążeń. Średni czas trwania może ukryć sporadyczne, bardzo wolne wykonania, natomiast suma czasu może wskazywać zapytanie często uruchamiane, choć każde pojedyncze wykonanie trwa krótko. Przy ocenie indeksu należy analizować co najmniej liczbę wykonań, średni oraz maksymalny czas trwania, zużycie procesora i liczbę odczytów. Znaczenie ma także okres po wdrożeniu zmiany, ponieważ statystyki mogą wymagać czasu na zebranie reprezentatywnej próbki.

Zmiana planu po utworzeniu indeksu nie zawsze oznacza, że indeks był jedyną przyczyną poprawy. W tym samym czasie mogły zmienić się statystyki, wartości parametrów, obciążenie systemu, ustawienia bazy albo dane w tabeli. Odwrotny problem również jest możliwy: nowy indeks może pomóc jednemu zapytaniu, ale zwiększyć koszt operacji INSERT, UPDATE i DELETE, ponieważ każda zmiana danych musi aktualizować dodatkową strukturę. Przed pozostawieniem indeksu należy więc sprawdzić jego użycie oraz wpływ na inne zapytania, a także zweryfikować, czy nie dubluje istniejącego indeksu pod względem klucza i pokrywanych kolumn.

Praktyczny tok oceny skuteczności indeksowania

Bezpieczna ocena obejmuje zapis stanu wyjściowego, utworzenie lub modyfikację indeksu, obserwację Query Store oraz porównanie wyników z okresem bazowym. Przydatne jest odnotowanie definicji zapytania, parametrów, czasu zmiany i oczekiwanego efektu. Po wdrożeniu należy sprawdzić, czy SQL Server zarejestrował nowy plan oraz czy poprawa występuje dla typowych wartości parametrów. Jeżeli plan jest niestabilny, sama średnia może być myląca, dlatego trzeba porównać poszczególne plany i rozkład wyników w czasie.

Jeżeli nowy plan jest wyraźnie gorszy, Query Store może pomóc wykryć regresję i wskazać plan używany wcześniej. W SQL Server dostępne są mechanizmy wymuszania planu z poziomu Query Store, ale takie działanie powinno być traktowane jako środek kontrolny, a nie zamiennik analizy przyczyny. Należy równolegle sprawdzić statystyki, parametry zapytania, definicję indeksu i aktualne dane. W środowisku z laptopem lub komputerem PC analiza tego typu dotyczy zwykle oprogramowania bazodanowego zainstalowanego na urządzeniu albo problemu z wydajnością aplikacji korzystającej z SQL Server; diagnostyka powinna rozdzielać awarię sprzętu, konfiguracji systemu i samego silnika bazy danych.