MAX_PLANS_PER_QUERY

MAX_PLANS_PER_QUERY to limit liczby planów wykonania przechowywanych dla jednego zapytania w Query Store.

Rola MAX_PLANS_PER_QUERY w SQL Server Query Store

MAX_PLANS_PER_QUERY jest opcją konfiguracyjną Query Store w Microsoft SQL Server. Określa maksymalną liczbę planów wykonania, które Query Store może przechowywać dla jednego zapytania rozpoznawanego przez silnik. Plan wykonania opisuje między innymi sposób dostępu do tabel i indeksów, kolejność operatorów, rodzaj użytych operatorów oraz przewidywane koszty operacji. To właśnie porównywanie kolejnych planów pozwala analizować zmiany wydajności zapytania w czasie.

Limit dotyczy pojedynczego zapytania, a nie całej bazy danych ani całego Query Store. Jeżeli to samo zapytanie jest wykonywane wielokrotnie, ale SQL Server wybiera dla niego różne plany, każdy z nich może zostać zarejestrowany jako osobny wariant. Zbyt wysoka liczba wariantów może zwiększać ilość metadanych przechowywanych w Query Store i utrudniać analizę. Zbyt niski limit może natomiast ograniczyć historię potrzebną do wykrycia problemu z regresją planu.

Jak limit wpływa na historię planów wykonania

SQL Server może wygenerować różne plany dla tego samego zapytania, gdy zmieniają się statystyki, dane, dostępne indeksy, ustawienia bazy lub parametry użyte podczas kompilacji. Różnice mogą wystąpić także wtedy, gdy zapytanie otrzymuje odmienne wartości parametrów i silnik podejmuje inne decyzje optymalizatora. Query Store rejestruje te warianty, aby można było sprawdzić, czy określony plan powoduje wzrost czasu wykonania albo liczby odczytów.

MAX_PLANS_PER_QUERY działa jako ograniczenie liczby planów przypisanych do jednego zapytania. Po osiągnięciu limitu Query Store nie powinien bez końca gromadzić kolejnych wariantów tego samego zapytania. W praktyce oznacza to, że przy bardzo zmiennym procesie kompilacji nie wszystkie historyczne plany będą dostępne jednocześnie. Podczas diagnozy trzeba więc brać pod uwagę zarówno statystyki zapytania, jak i ustawienia retencji, rozmiar Query Store oraz sposób czyszczenia najstarszych danych.

Konfiguracja i kontrola ustawienia

Opcję można ustawić dla konkretnej bazy danych za pomocą instrukcji ALTER DATABASE. Przykładowa konfiguracja ma postać: ALTER DATABASE [NazwaBazy] SET QUERY_STORE (MAX_PLANS_PER_QUERY = 200);. Wartość należy dobierać do charakteru obciążenia oraz celu prowadzenia historii. Liczba 200 jest domyślnym limitem używanym przez Query Store, ale sama obecność tej wartości nie gwarantuje, że dla każdego zapytania zostanie zapisanych dokładnie 200 planów.

Stan konfiguracji można odczytać z widoku katalogowego sys.database_query_store_options. Przykładowe zapytanie diagnostyczne to: SELECT max_plans_per_query FROM sys.database_query_store_options;. Przed zmianą ustawienia należy sprawdzić, czy Query Store jest włączony oraz czy analizowana baza używa wersji SQL Server obsługującej daną opcję. Sama zmiana limitu nie wymusza ponownej kompilacji zapytań i nie zmienia bezpośrednio planu aktualnie używanego przez optymalizator.

Znaczenie ustawienia podczas analizy SQL Server

Podczas badania regresji wydajności warto porównać liczbę planów zapisanych dla konkretnego zapytania z wartością MAX_PLANS_PER_QUERY. Pomocne są widoki Query Store, w szczególności sys.query_store_query, sys.query_store_plan oraz sys.query_store_query_text. Pozwalają one powiązać tekst zapytania z jego identyfikatorem i sprawdzić, jakie plany zostały zapisane. Statystyki wykonania można zestawiać z planami, aby odróżnić zmianę planu od problemu wynikającego na przykład ze wzrostu liczby danych.

Jeżeli limit ustawiono zbyt nisko dla zapytań często zmieniających plan, historia może nie obejmować wariantu potrzebnego do odtworzenia wcześniejszej regresji. Podwyższenie wartości zwiększa potencjalną liczbę przechowywanych planów, ale może również zwiększyć wykorzystanie miejsca przez Query Store. Jeżeli limit jest wysoki, należy monitorować rozmiar magazynu, stan jego pracy i politykę czyszczenia. MAX_PLANS_PER_QUERY nie naprawia wolnego zapytania, nie tworzy indeksów i nie wymusza konkretnego planu; ogranicza wyłącznie zakres historii planów przechowywanej przez Query Store.