Plany wykonania – złożone mapy wykreślone przez optymalizator SQL Server, dyktują najbardziej efektywną ścieżkę pobierania danych. Plany nie są jednak statyczne. Zmieniają się i dostosowują pod wpływem wielu czynników, od zmian ilości danych po aktualizacje systemu. Każda ze zmian planu wykonania w SQL Server może potencjalnie znacząco wpłynąć na wydajność.

Zrozumienie przyczyn tych zmian, a co ważniejsze, zrozumienie tego jak skutecznie nimi zarządzać, przekłada się na utrzymanie optymalnej wydajności w środowiskach SQL Server.

Zmiany danych a Zmiana planu wykonania w SQL Server

Jednym z najczęstszych wyzwalaczy zmian planu wykonania w SQL Server jest duża zmiana ilości danych w tabelach bazy danych. Dlaczego? SQL Server wykorzystuje specyficzny mechanizm; utrzymuje statystyki dotyczące danych przechowywanych w bazie danych. Statystyki te są następnie wykorzystywane przez optymalizator zapytań w celu określenia najbardziej wydajnego sposobu wykonania zapytania. Gdy dane ulegają zmianie – poprzez wstawianie, usuwanie lub aktualizacje na dużą skalę – statystyki te odbiegają od bieżącego stanu danych.

SQL Server ustawia progi określające, ile danych w tabeli musi się zmienić, aby automatycznie zaktualizować statystyki tabeli. Zazwyczaj próg ten wynosi około 20% wierszy w tabeli. Po przekroczeniu tego progu SQL Server oznacza statystyki jako nieaktualne. Przy następnym wykonaniu zapytania, które użyje tych statystyk, optymalizator zapytań może najpierw zaktualizować statystyki. Następnie, w oparciu o te świeże dane, może wygenerować nowy plan wykonania.

Takie zachowanie jest zasadniczo zaprojektowane tak, aby plany wykonania były jak najbardziej wydajne. Jeśli jednak zmiana danych jest nagła lub ciągła, może to prowadzić do częstych (i irytujących) zmian w planach wykonania.

Nieaktualne statystyki a zmiana planu wykonania w SQL Server

Statystyki w SQL Server zapewniają podsumowanie rozmieszczenia danych w tabelach. Obejmuje to informacje takie jak średnia wartość, wartości minimalne i maksymalne oraz rozkład wartości w każdej kolumnie. Podsumowania te pomagają optymalizatorowi przewidzieć, na ile wierszy będzie miało wpływ zapytanie. To z kolei wpływa na wybraną strategię wykonania.

Ponieważ zmiany planu wykonania w SQL Server gromadzą się w bazie danych, dokładność tych statystyk zanika. Ten spadek trafności może wprowadzać optymalizator w błąd. W niektórych przypadkach optymalizator może wybrać skanowanie indeksu, gdy wyszukiwanie indeksu byłoby bardziej odpowiednie, lub może błędnie ocenić liczbę wierszy zwróconych przez filtr, co prowadzi do nieoptymalnych metod łączenia lub niewłaściwych przydziałów pamięci.

Konsekwencje tych błędnych informacji są bolesne:

• Nieefektywna wydajność zapytań: Optymalizator może wybrać plan, który działa słabo, ponieważ opiera się na nieaktualnych statystykach. Może to spowodować, że zapytania, które kiedyś były wykonywane szybko, będą działać wolniej, zużywając więcej zasobów, takich jak procesor i we/wy, co prowadzi do szerszych problemów z wydajnością systemu.
• Nieprawidłowa alokacja zasobów: Nieprawidłowe szacunki mogą prowadzić do nadmiernego lub niedostatecznego wykorzystania zasobów SQL Server. Przeszacowania mogą spowodować, że baza danych zarezerwuje więcej pamięci niż to konieczne, pozostawiając mniej dostępnej dla innych procesów. Niedoszacowanie może prowadzić do niewystarczającej alokacji pamięci, powodując częste operacje we/wy na dysku, których można było uniknąć.

Ograniczenie ryzyka dezaktualizacji statystyk

• Ręczna aktualizacja statystyk:Użyj UPDATE STATISTICS po znaczących zmianach danych, aby upewnić się, że statystyki dokładnie odzwierciedlają aktualny stan danych, pomagając utrzymać optymalną wydajność zapytań.
• Dostosowanie progu aktualizacji statystyk: Zmodyfikuj domyślne ustawienia w SQL Server, aby obniżyć próg automatycznej aktualizacji statystyk. Powinno to skutkować częstszymi aktualizacjami bez konieczności ręcznej interwencji.
• Wdrożenie asynchronicznych aktualizacji statystyk: Włączenie opcji AUTO_UPDATE_STATISTICS_ASYNC, aby umożliwić aktualizacje w tle, zapobiegając opóźnieniom zapytań i zapewniając, że plany wykonania są oparte na najnowszych danych.

Kondycja indeksu

Kondycja indeksów jest jednym z najważniejszych czynników wpływających na zdolność SQL Server do wyboru najbardziej wydajnego planu wykonania zapytania. Gdy indeksy są dobrze utrzymane, zapewniają szybkie ścieżki do danych, które optymalizator wykorzystuje do konstruowania wysokowydajnych planów. Jednak kwestie takie jak fragmentacja indeksów mogą potencjalnie poważnie wpływać na te decyzje, prowadząc do mniej optymalnej wydajności zapytań.

• Fragmentacja: Z biegiem czasu, gdy dane są modyfikowane w bazie danych – dodawane, aktualizowani, usuwane – indeksy mogą ulec fragmentacji. Fragmentacja ta oznacza, że logiczna kolejność danych indeksu staje się rozproszona, nie dostosowując się do fizycznej kolejności na dysku. Konsekwencje są następujące:
• Zwiększony czas operacji I/O: Pofragmentowane indeksy powodują, że stary dobry SQL Server wykonuje więcej operacji I/O, ponieważ strony danych nie są ciągłe. To z kolei powoduje, że silnik odczytuje wiele stron z różnych lokalizacji na dysku.
• Nieefektywne plany wykonania: Optymalizator opiera swoje decyzje na fizycznym układzie i statystykach indeksów. Jeśli fragmentacja indeksu prowadzi do błędnego oszacowania kosztu związanego z użyciem indeksu, optymalizator z większym prawdopodobieństwem wybierze skanowanie tabeli zamiast korzystania z pofragmentowanego indeksu.

Rozwiązania:

• Konserwacja indeksów: Regularne zadania konserwacyjne, takie jak przebudowa lub reorganizacja indeksów, mogą złagodzić fragmentację. Wybór między przebudową a reorganizacją zależy od stopnia fragmentacji:
  • Przebudowa indeksów: Proces ten tworzy nową wersję indeksu, eliminując fragmentację, odzyskując miejsce na dysku i zmieniając kolejność wierszy indeksu zgodnie z ich logiczną kolejnością. Przebudowa jest generalnie bardziej zasobochłonna i jest zalecana, gdy fragmentacja jest poważna.
  • Reorganizacja indeksów: Mniej intensywna niż przebudowa, reorganizacja fizycznie zmienia kolejność stron na poziomie arkusza, aby dopasować je do kolejności logicznej. Jest to lżejsza operacja i może być wykonywana częściej jako środek zapobiegawczy przed fragmentacją.
• Regularne monitorowanie
• Narzędzia i funkcje: skorzystaj z funkcji takich jak SQL Server Agent, aby zautomatyzować proces konserwacji indeksów. Narzędzia takie jak SQL Server Management Studio (SSMS) zapewniają wbudowane raporty i pulpity nawigacyjne do monitorowania kondycji i wydajności indeksów.

Modyfikacje zapytań i schematów

Kiedy bawisz się strukturą zapytania lub dostosowujesz schemat bazy danych, fale, podobnie jak niewielki kamyk wrzucony do dużego stawu, wpływają na całą powierzchnię. Nawet niewielkie zmiany mogą zmusić SQL Server do wygenerowania nowych planów wykonania.

• Zmiana powiązań: Zmiana sposobu powiązania tabel może zmienić ścieżkę pobierania danych bardziej dramatycznie niż zmiana trasy głównej autostrady.
• Dostosowywanie filtrów: Modyfikacja klauzul WHERE nie tylko filtruje dane w inny sposób – może całkowicie zmienić środowisko pobierania danych.
• Wprowadzanie nowych operacji: Dodanie elementów takich jak GROUP BY lub ORDER BY zmienia wymagania zapytania, prosząc o dane na nowe sposoby, które mogą obciążać system na różne sposoby.

Modyfikacje schematu: Zmiana schematu bazy danych może mieć również daleko idące konsekwencje:

• Dostosowanie indeksów: Dodanie lub usunięcie indeksów może ułatwić nawigację lub wprowadzić zamieszanie, wpływając na szybkość i wydajność wyszukiwania danych.
• Modyfikacja kolumn: Zmiana typów danych, dodanie ograniczeń NULL lub zmiana rozmiarów kolumn może wpłynąć na sposób przechowywania i dostępu do danych, wymagając nowych planów wykonania w celu efektywnej obsługi tych zmian.
• Dostosowywanie ograniczeń i relacji: Zmiany w kluczach podstawowych, kluczach obcych lub ograniczeniach kontrolnych mogą zmienić reguły integralności danych i relacje między tabelami, wpływając na zachowanie złączeń i ważność istniejących planów wykonania.

Solutions:

• Testowanie: Wdrożenie zmian w kontrolowanym środowisku pozwala na ocenę wpływu na wydajność, zapewniając wgląd w potencjalne problemy w świecie rzeczywistym.
• Odświeżanie statystyk: Aktualizacja statystyk po wprowadzeniu modyfikacji gwarantuje, że optymalizator posiada najbardziej aktualne dane.
• Bieżące monitorowanie: Ciągłe monitorowanie wydajności zapytań i planów wykonania po wprowadzeniu zmian pomaga w szybkim identyfikowaniu i korygowaniu nieefektywnych wykonań.

Aktualizacje a zmiany planu wykonania w SQL Server

Po aktualizacjach programu SQL Server zwykle następują modyfikacje optymalizatora zapytań, który jest odpowiedzialny za podejmowanie decyzji o najbardziej efektywnym sposobie wykonywania zapytań. Ewolucja optymalizatora może prowadzić do różnic w sposobie określania planów wykonania.

Jedną z najbardziej znaczących zmian, które mogą wystąpić wraz z aktualizacją, jest modyfikacja estymatora kardynalności. Jego zadaniem jest przewidywanie liczby wierszy przetwarzanych przez różne operacje zapytań, co ma duży wpływ na wybór planu wykonania. Aktualizacje mogą udoskonalić te przewidywania, co oznaczaÑ różne wybory planu, które mogą nie być zgodne z poprzednimi optymalizacjami.

Rozwiązania:

• Dokładne testowanie: Testy powinny obejmować testy porównawcze wydajności, aby zidentyfikować wszelkie potencjalne regresje.
• Zarządzanie planami: SQL Server zapewnia narzędzia takie jak Query Store do przechwytywania i zachowywania dobrych planów wykonania. Można to wykorzystać do wymuszenia korzystania z tych planów nawet po aktualizacji, dopóki nie będzie możliwe bardziej trwałe rozwiązanie.
• Stopniowe wdrażanie: Stopniowe wdrażanie aktualizacji w różnych środowiskach pozwala zespołom monitorować ich wpływ i w razie potrzeby dostosowywać strategie.

Zmiany w konfiguracji serwera

To oczywiste, że sposób tworzenia i wykonywania planów wykonania nie jest obojętny na ustawienia konfiguracji serwera.

Jednym z kluczowych ustawień, które często wpływa na wybór planu wykonania, jest maksymalny stopień równoległości (MAXDOP). Określa on liczbę procesorów, które SQL Server może wykorzystać do równoległego wykonywania zapytań. Modyfikując MAXDOP, zasadniczo dyktujesz, ile rdzeni procesora SQL Server może wykorzystać do równoległego przetwarzania zapytań. Niższy MAXDOP ogranicza serwer do mniejszej liczby rdzeni, popychając go w kierunku bardziej sekwencyjnego przetwarzania zapytań. Ustawienie wyższego MAXDOP zachęca serwer do wykorzystania większej liczby rdzeni do szybszego wykonywania złożonych zapytań, choć może to prowadzić do zwiększonej rywalizacji między procesami, jeśli nie jest zarządzane ostrożnie.

Zmiany te nie zachodzą jednak w próżni. Powodują one ponowną kalibrację podstawowego zachowania optymalizatora zapytań SQL Server. Ta rekalibracja może czasami prowadzić do scenariusza, w którym wcześniej buforowane plany stają się nieoptymalne w nowych ustawieniach, co skłania SQL Server do ponownej kompilacji zapytań w nowej konfiguracji systemu. Ta rekompilacja, choć konieczna, może prowadzić do przejściowych spadków wydajności, gdy serwer dostosowuje się do nowych ustawień.

Postępowanie ze zmianami planu wykonania w SQL Server

• Stopniowe zmiany: Wprowadzaj przyrostowe zmiany w ustawieniach, takich jak MAXDOP, zamiast dużych zmian, aby lepiej ocenić ich wpływ.
• Korzystanie z linii bazowych: Wartości bazowe wydajności skonfigurowane przed wprowadzeniem zmian mogą być pomocne w pomiarze skuteczności zmiany konfiguracji.
• Zapoznaj się z najlepszymi praktykami: Stosuj się do najlepszych praktyk Microsoftu w zakresie ustawień konfiguracji. Dokumentacja SQL Server i zalecenia społeczności mogą dostarczyć wskazówek w tej kwestii.

Sniffing parametrów

Sniffing parametrów odnosi się do zachowania optymalizacyjnego SQL Server, w którym optymalizator zapytań bada wartości parametrów przekazane podczas pierwszego wykonania procedury składowanej i tworzy plan na podstawie tych wartości. Proces ten jest generalnie pozytywny – dostosowuje plan wykonania do określonego obciążenia zapytania. Istnieją jednak momenty i sytuacje, w których sniffing parametrów może prowadzić do problemów z wydajnością, gdy ten sam plan wykonania jest ponownie wykorzystywany dla znacznie różnych wartości parametrów.

Gdy początkowe wartości parametrów nie są reprezentatywne dla typowego użycia, zoptymalizowany plan może nie być idealny dla kolejnych wykonań z różnymi parametrami. Może to prowadzić do nieefektywnej wydajności zapytań, dłuższych czasów wykonania i zwiększonego wykorzystania zasobów.

Spójrzmy na przykład. Jeśli procedura składowana jest najpierw wykonywana z parametrem, który pobiera mały podzbiór wierszy, optymalizator zapytań może wybrać plan, który wykorzystuje wyszukiwanie indeksu – bardzo wydajny dla małych zestawów danych. Jeśli ten sam plan zostanie użyty dla parametru, który musi pobrać większy zestaw wierszy, plan może nie skalować się dobrze, prowadząc do wolniejszej wydajności i wyższego zapotrzebowania na zasoby.

Radzenie sobie z problemami związanymi ze sniffingiem parametrów

• Magazyn zapytań i wymuszanie planu: Funkcja Query Store programu SQL Server umożliwia przechwytywanie i przechowywanie szczegółowych danych dotyczących wydajności zapytań wraz z ich planami. Administratorzy mogą ręcznie wymusić użycie określonych planów, o których wiadomo, że działają dobrze dla niektórych zapytań, unikając w ten sposób szkodliwych skutków podsłuchiwania parametrów.
• Przewodniki po planach: Przewodniki po planach umożliwiają użytkownikom SQL Server dołączanie podpowiedzi do zapytań lub stałych planów do określonych zapytań, pomagając kontrolować wybór planu wykonania – nie jest wymagana bezpośrednia modyfikacja kodu aplikacji.