Parameter Sniffing i cache planów zapytań

Dziś zajmiemy się dosyć ciekawym mechanizmem jakim jest Parameter Sniffing, który mimo iż jest pozytywnym zjawiskiem może czasem nieść ze sobą negatywne skutki – zapraszam do lektury.

Zapewne każdy z Was słyszał o planach zapytania oraz o tym, że czas znalezienia wystarczająco dobrego planu jest zróżnicowany i może być znaczący w kontekście całego czasu potrzebnego na wykonanie zapytania. SQL Server stara się zaoszczędzić czas potrzebny na optymalizację poprzez umieszczanie planów w tzw. plan cache czyli w wydzielonym miejscu pamięci przeznaczonym na plany wykonania. Tak więc raz wygenerowany plan może być użyty ponownie przez inne “podobne” zapytania (cały proces cache’owania planów rządzi się swoimi prawami i z pewnością poświęcę temu osobny artykuł). Mechanizm ten jak już wspomniałem niesie ze sobą dobre rezultaty jednakże czasem pojawiają się anomalie odbijające się na wydajności naszych zapytań, które postaram się przedstawić w poniższej demonstracji.

Na samym początku wykonajmy dwa bardzo podobne zapytania, korzystające z bazy AdventureWorks2014. Każde zapytanie ma tą samą strukturę i pobiera pola ProductId oraz OrderQty pochodzące z połączenia tabeli SalesOrderHeader oraz SalesOrderDetail. Jedyną różnicą jest fakt, iż jedno zapytanie wyświetli dane dla produktu o ID równym 897 drugie z kolei dla produktu o id 870:

USE AdventureWorks2014
GO

SELECT sod.ProductID,sod.OrderQty FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID=sod.SalesOrderID
WHERE sod.ProductID=897
GO

SELECT sod.ProductID,sod.OrderQty FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID=sod.SalesOrderID
WHERE sod.ProductID=870

Na powyższym zrzucie ekranowym widać, że mimo iż strukturalnie oba zapytania były bardzo podobne to w praktyce optymalizator wybrał różne sposoby na ich wykonanie: raz użył Index Seek + Key Lookup za drugim razem z kolei Clustered Index Scan. Dlaczego podjął taką decyzję? Ponieważ bazował na statystykach i dla wartości 870 optymalnym wyjściem było przeskanowanie całej tabeli zamiast wykonanie operacji lookup. Po najechaniu na strzałki pomiędzy operatorami dowiemy się, że pierwsze zapytanie zwróciło tylko dwa wiersze z kolei drugie aż 4688 – tak więc wybranie takich, a nie innych planów jest w pełni uzasadnione. Tak więc możemy uprościć, że dla konkretnych wartości atrybutu ProductID mamy odmienne plany zapytania bazując na ich estymacji liczebności (cardinality estimation).

Drugiem krokiem jaki sobie pokażemy jest stworzenie analogicznych zapytań tym razem w ramach procedury składowanej:

CREATE PROC dbo.usp_GetProductsOrderQty @ProductID int
AS
SELECT sod.ProductID,sod.OrderQty FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID=sod.SalesOrderID
WHERE sod.ProductID=@ProductID

Następnie wywołajmy ją poleceniem EXEC:

EXEC dbo.usp_GetProductsOrderQty @ProductID=897
GO

podejrzyjmy plan zapytania:

Wygląda dokładnie tak samo jak wcześniej gdy wywołaliśmy to zapytanie bezpośrednio bez procedury. Wywołajmy teraz naszą procedurę raz jeszcze tym razem z drugą zawartością parametru:

EXEC dbo.usp_GetProductsOrderQty @ProductID=870
GO

Plan wykonania przedstawia się następująco:

Jest on dokładnie taki sam dla obu wartości parametrów! Nie widzimy już Index Scan, a jedynie Index Seek + Key Lookup. Czy jest to optymalny wybór? Dla pierwszej wartości parametru już tak – dla drugiej już nie. Po przejrzeniu właściwości planu możemy zobaczyć następujący zapis:

Plan został skompilowany z wartością parametru równą 897, jednakże wartość przekazana do procedury równa jest 870. Tak więc plan ten został umieszczony w plan cache za pierwszym razem po wykonaniu procedury z wartością parametru 897 i wykorzystany ponownie dla wartości 870. Zadziałał tutaj opisywany mechanizm Parametr Sniffingu, gdzie użyta została pierwsza wartość parametru i dla tej wartości wygenerowany został plan został umieszczony w cache. Oczywiście w przedstawianym przypadku strata na czasie wykonania jest niewielka i właściwie możemy zignorować fakt, iż nasze zapytanie nie zostało wykonane poprzez Index Scan (pamiętajmy również, że zaoszczędzony został czas potrzebny na kompilację planu). Co jednak gdy okaże się, iż nasze procedury raz działały dobrze, a raz źle? Może tak się stać i wtedy też opisywany mechanizm może być winowajcą. Takie przypadki mogą się zdarzyć szczególnie dla skośnych rozkładów częstości tj. gdy jedne wartości parametru zwracają dużo mniej (lub dużo więcej) rekordów niż inne. Jak sobie z tym poradzić? Na kilka sposobów, które postaram się przedstawić – pierwszym z nich będzie użycie słów kluczowych WITH RECOMPILE przy wywołaniu procedury:

EXEC dbo.usp_GetProductsOrderQty @ProductID=870 WITH RECOMPILE
GO

 

Jak można zauważyć na powyższych zrzutach ekranowych – plan został skompilowany z wartościami bieżącymi parametru.Oczywiście taki sposób nie zawsze jest użyteczny gdyż wymaga aby każda aplikacja kliencka miała zawarte w wywołaniu słowa kluczowe – możemy zrobić to inaczej i zawrzeć je w definicji samej procedury:

ALTER PROC dbo.usp_GetProductsOrderQty @ProductID int WITH RECOMPILE
AS
SELECT sod.ProductID,sod.OrderQty FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID=sod.SalesOrderID
WHERE sod.ProductID=@ProductID

Sposób ten pozwala na uniknięcie problemu z aplikacjami klienckimi jednakże należy pamiętać, że każde wywołanie procedury będzie uruchomione z rekompilacją planu zapytania. Oczywiście nasze procedury mogą mieć zawarte w sobie wiele zapytań – dodanie WITH RECOMPILE będzie miało wpływ na każde z nich. Jeżeli chcemy aby rekompilowany był plan tylko jednego zapytania w ramach procedury możemy użyć OPTION (RECOMPILE):

ALTER PROC dbo.usp_GetProductsOrderQty @ProductID int
AS
SELECT sod.ProductID,sod.OrderQty FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID=sod.SalesOrderID
WHERE sod.ProductID=@ProductID OPTION (RECOMPILE)

W naszym przykładzie mamy tylko jedno zapytanie w procedurze dlatego też OPTION (RECOMPILE) będzie miało ten sam efekt co WITH RECOMPILE – ale warto pamiętać o tej opcji dla bardziej zaawansowanych procedur.

Oprócz powyższych opcji mamy jeszcze jedną – z użyciem słów kluczowych OPTIMIZE FOR. Tak jak sama nazwa wskazuje pozwala ona jawnie wskazać dla jakiej wartości parametru chcemy aby tworzony był plan np.

ALTER PROC dbo.usp_GetProductsOrderQty @ProductID int
AS
SELECT sod.ProductID,sod.OrderQty FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID=sod.SalesOrderID
WHERE sod.ProductID=@ProductID OPTION( OPTIMIZE FOR (@ProductID=870))

Używając powyższego zapisu stworzony zostanie plan dla wartości 870 bez względu na wartość parametru podanego przy wywołaniu procedury. Do OPTIMIZE FOR nie musimy podawać jawnie wartości której chcemy użyć do optymalizacji planu – ale również możemy podać słowo kluczowe UNKNOWN:

ALTER PROC dbo.usp_GetProductsOrderQty @ProductID int
AS
SELECT sod.ProductID,sod.OrderQty FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID=sod.SalesOrderID
WHERE sod.ProductID=@ProductID OPTION( OPTIMIZE FOR UNKNOWN)

Po uruchomieniu procedury we właściwościach znajdziemy następujący wpis:

Tym razem nie podana została wartość kompilacji dla parametru – co ciekawe po najechaniu na estymowaną liczbę wierszy w planie otrzymamy również dosyć “dziwną” liczbę:

Skąd się wzięła się liczba 456.079? Oczywiście ze statystyk – bez wchodzenia w szczegóły możemy powiedzieć, że jest to średnia ilość wierszy dla każdej wartości parametru. Może się Wam wydawać, iż opcja ta jest lekiem na całe zło jednakże należy uważać z jej zastosowaniem gdyż pamiętajmy, że z jej użyciem jest jak z typową statystyką – ma podstawy matematyczne ale czasem może mijać się z rzeczywistością. W podobny sposób działają tzw. zmienne lokalne, których wartości optymalizator nie widzi w momencie optymalizacji i używa dokładnie tej samej estymowanej liczby wierszy co w przypadku użycia OPTIMIZE FOR UNKNOWN:

ALTER PROC dbo.usp_GetProductsOrderQty @ProductID int
AS
DECLARE @ProdID int =@ProductID
SELECT sod.ProductID,sod.OrderQty FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID=sod.SalesOrderID
WHERE sod.ProductID=@ProdID

Do wyliczenia estymowanej liczby wierszy zostały użyte zapisy w statystykach – podobnie jak w poprzednim przypadku.

Podsumowując Parameter Sniffing jest świetnym mechanizmem w bazach transakcyjnych gdzie można wiele zyskać na uniknięciu zasobożernych operacji kompilacji planów. Szczególnie sprawdza się on w przypadku gdy mamy do czynienia z w miarę równym rozkładem częstości o niezbyt dużych odchyleniach. W przypadku gdy wykryjemy, iż nasze procedury raz wykonują się bardzo szybko, a raz bardzo wolno, może mieć to związek właśnie z opisywanym mechanizmem. Po bardziej szczegółowej analizie i wykryciu problemu możemy mu przeciwdziałać używając WITH RECOMPILE lub jednej z opcji zapytań jak np. OPTIMIZE FOR UNKNOWN, jednakże jak zawsze należy z tym uważać, stosować z rozsądkiem i nic nie stosować bez uprzedniej analizy problemu. Powinniśmy również poddawać rekompilacji najmniejszą część zapytania jaką możemy np. pojedyncze zapytanie zamiast całe procedury. Jest to ważne dlatego, że jeśli zapytanie przy każdym wywołaniu będzie rekompilowane to stracimy historię jego wykonania zawartą w niektórych widokach dynamicznych (np. sys.dm_exec_query_stats) co znacznie ogranicza możliwości troubleshootingu jaki mamy do dyspozycji. Wartym wspomnienia faktem jest również to, że od SQL Server 2016 możemy kontrolować Parameter Sniffing na poziomie bazy danych – jak to zrobić opisałem tutaj.