Query Store a parametryzacja

Query Store a parametryzacja — różne wartości parametrów mogą generować odrębne plany dla tego samego zapytania.

Jak Query Store rozpoznaje zapytania z parametrami

Query Store w SQL Server zapisuje informacje o zapytaniach wykonywanych przez silnik, między innymi tekst zapytania, plan wykonania, statystyki wykonawcze oraz czas wystąpienia poszczególnych operacji. Parametryzacja wpływa na sposób identyfikowania zapytania, ponieważ wartości przekazane do parametru nie zawsze zmieniają logiczną postać instrukcji. Przykładowo zapytanie wykonane jako SELECT * FROM dbo.Orders WHERE CustomerId = @id może być wielokrotnie uruchamiane z różnymi wartościami parametru, a SQL Server traktuje je jako tę samą strukturę zapytania, jeżeli pozostałe istotne warunki identyfikacji pozostają zgodne.

W Query Store należy odróżnić tekst zapytania, identyfikator zapytania, identyfikator planu oraz query_hash. query_hash jest skrótem opisującym strukturę logiczną zapytania i może być taki sam dla różnych instrukcji o podobnym kształcie. Query Store przechowuje natomiast własne identyfikatory oraz kontekst wykonania, dlatego dwa pozornie podobne zapytania nie zawsze trafią do jednego rekordu. Znaczenie mogą mieć między innymi sposób parametryzacji, tekst instrukcji, ustawienia sesji oraz obiekt, w którym zapytanie zostało wykonane.

Parametryzacja prosta, wymuszona i jawna

SQL Server może zastosować parametryzację prostą dla części zapytań ad hoc. W takim przypadku literał występujący w instrukcji może zostać zastąpiony parametrem wewnętrznym, aby umożliwić ponowne użycie planu. Zakres tej optymalizacji zależy od rodzaju zapytania i reguł silnika. Parametryzacja nie jest gwarantowana dla każdej instrukcji, ponieważ niektóre konstrukcje mogą uniemożliwiać bezpieczne zastąpienie literałów parametrami.

Parametryzację można także wymusić na poziomie bazy danych za pomocą opcji PARAMETERIZATION FORCED. Jest to ustawienie dotyczące zachowania optymalizatora i powinno być oceniane razem z charakterystyką obciążenia. Inną metodą jest parametryzacja jawna, na przykład przez użycie sp_executesql z deklaracją typu parametru. Jawna parametryzacja pozwala aplikacji przekazywać wartości oddzielnie od tekstu instrukcji, lecz nie oznacza automatycznie, że jeden plan będzie optymalny dla wszystkich wartości. Plan nadal może zostać skompilowany na podstawie wartości dostępnej podczas kompilacji.

Dlaczego jedna instrukcja może mieć kilka planów

Różne wartości tego samego parametru mogą prowadzić do bardzo odmiennych rozkładów danych. Jeżeli jedna wartość wybiera dużą część tabeli, optymalizator może preferować skanowanie indeksu lub całej tabeli. Dla wartości występującej rzadko korzystniejszy może być seek po indeksie oraz odczyt niewielkiej liczby wierszy. Problem powstaje wtedy, gdy plan skompilowany dla jednego rozkładu danych zostaje ponownie wykorzystany przy wartości o zupełnie innej selektywności.

Query Store rejestruje plany powiązane z zapytaniem, dlatego może ujawnić, że ta sama instrukcja korzystała w różnych okresach z kilku planów. W statystykach można analizować między innymi liczbę wykonań, czas trwania, zużycie procesora, odczyty logiczne i zapisy. Istotne jest sprawdzenie, czy różnice dotyczą rzeczywiście wartości parametrów, czy też wynikają ze zmiany indeksów, aktualizacji statystyk, ustawień kompatybilności bazy, zmian wersji silnika albo innych warunków kompilacji.

W nowszych wersjach SQL Server dostępna jest funkcja Parameter Sensitive Plan Optimization, znana jako PSP Optimization. Mechanizm ten pozwala optymalizatorowi utrzymywać więcej niż jeden wariant planu dla parametryzowanego zapytania, gdy rozkład wartości parametru uzasadnia takie rozróżnienie. Query Store może wtedy pokazywać zapytanie nadrzędne oraz powiązane warianty planów. Nie należy jednak utożsamiać każdego zestawu wielu planów z działaniem PSP, ponieważ kilka planów może wynikać również z ręcznego wymuszenia planu, rekompilacji lub zmian środowiska wykonawczego.

Analiza rozbieżności w Query Store

Analizę warto rozpocząć od wyszukania instrukcji o wysokim całkowitym czasie trwania lub wysokim czasie procesora, a następnie porównać statystyki dla poszczególnych planów i przedziałów czasowych. Sam średni czas wykonania może ukrywać problem: plan może działać szybko dla większości wartości, lecz bardzo wolno dla określonego zakresu. Dlatego przydatne są także wartości minimalne, maksymalne i odchylenia statystyk, o ile są dostępne w używanym widoku Query Store.

Należy sprawdzić tekst zapytania, typy parametrów i sposób ich przekazywania. Parametr typu nvarchar użyty wobec kolumny typu varchar może powodować niejawne konwersje i pogorszyć możliwość wykorzystania indeksu. Podobnie przekazanie wartości o nieodpowiedniej precyzji, długości lub typie może zmienić estymację liczby wierszy. Ważne jest także rozróżnienie planu przechowywanego historycznie od planu aktualnie używanego przez sesje, ponieważ Query Store opisuje historię, a nie zastępuje bieżącej obserwacji wykonania.

Jeżeli analiza potwierdzi regresję, Query Store może służyć do porównania planu wcześniejszego z planem wolniejszym. W uzasadnionym przypadku można użyć funkcji wymuszenia planu, ale jest to działanie kontrolne, a nie uniwersalne rozwiązanie problemu parametryzacji. Przed jego zastosowaniem trzeba zweryfikować indeksy, aktualność statystyk, typy parametrów, zapytanie źródłowe i obciążenie. Stabilny plan dla jednej wartości może bowiem pogorszyć wykonanie dla innych wartości, a właściwym rozwiązaniem może być zmiana zapytania, statystyk, indeksowania albo zastosowanie mechanizmu PSP dostępnego w danej wersji SQL Server.