SQLServer_Simple_Forced_00

Autoparametryzacja SIMPLE i FORCED w SQL Server

Niedawno pisałem o zjawisku Parameter Sniffing i związanym z nim cache’owaniem planów wykonania zapytań. Dziś powiemy sobie o powiązanym mechanizmie automatycznej parametryzacji w SQL Server.

Jak powszechnie wiadomo – SQL Server umieszcza w wydzielonej części pamięci operacyjnej plany zapytań w celu ich późniejszego ponownego użycia bez konieczności kosztownej i zasobochłonnej kompilacji. W niektórych przypadkach SQL Server może automatycznie parametryzować zapytania tak aby wygenerowany plan był możliwy do ponownego wykorzystania. Automatyczna parametryzacja może mieć miejsce tylko wtedy gdy SQL Server wie, że wartość parametru nie będzie miała wpływu na plan. Dla przykładu wykonajmy poniższe zapytanie na bazie AdventureWorks2014:

SELECT * FROM Production.Product
WHERE ProductID=1

plan wykonania dla poniższego zapytania przedstawia się następująco:

Jak widać dane pobierane są prostym Index Seek – najciekawsza sprawa dzieje się w samym zapytaniu. Na powyższym zrzucie ekranowym zaznaczona została część zapytania, która została automatycznie sparametryzowana. Dlaczego tak się stało? Ponieważ warunek był na polu ProductID tabeli Production.Product, który jest kluczem głównym tabeli, a co za tym idzie SQL Server był pewny, że bez względu na wartość podaną w warunku WHERE plan zawsze będzie wyglądał tak samo. Weźmy natomiast trochę bardziej skomplikowane zapytanie:

SELECT * FROM Sales.SalesOrderDetail AS SOD
JOIN Sales.SalesOrderHeader AS SOH
ON SOD.SalesOrderID=SOH.SalesOrderID
WHERE ProductID =790

Tym razem zapytanie nic nie zostało sparametryzowane – dzieje się ze względu na fakt, iż SQL Server nie jest pewny, że plan będzie wyglądał tak samo dla każdej wartości parametru. Poniżej można dostrzec, że wygenerowany plan jest zupełnie inny dla analogicznego zapytania z inną wartością parametru.

Tak więc SQL Server będzie automatycznie parametryzował zapytania tylko w przypadku gdy pewność, iż plan się nie zmieni w zależności od wartości parametru. Taki tryb automatycznej parametryzacji nazywamy SIMPLE. Obok niego istnieje również drugi tryb działania nazywany trybem FORCED gdzie SQL Server nie jest już tak ostrożny. Wybór pomiędzy poszczególnymi trybami jest możliwy do włączenia na poziomie całej bazy danych używając prostej komendy ALTER DATABASE … SET:

USE [master]
GO
ALTER DATABASE [AdventureWorks2014] SET PARAMETERIZATION FORCED WITH NO_WAIT
GO

Oczywiście ustawienie FORCED jest dosyć niebezpieczne bo każde zapytanie w ramach bazy danych będzie parametryzowane. Dla przykładu ponowne uruchomienie poprzedniego zapytania skutkuje powstaniem odpowiedniego parametryzowanego planu:

Zapytanie zostało przymusowo sparametryzowane – tak więc pierwszy plan jaki trafi do cache będzie używany ponownie dla każdego zapytania. Jak możecie się domyślać może to mieć zarówno pozytywne jak i negatywne skutki wydajnościowe. W przypadku ustawienia FORCED każda wartość klauzul filtrujących czyli tzw. literal zostanie zastąpiony parametrem nie tylko w zapytaniach SELECT ale również w INSERT, UPDATE i DELETE. Jest oczywiście kilka wyjątków gdzie wartości te nie będą poddane parametryzacji tj.:

  • zapytania INSERT…EXECUTE
  • Zapytania w procedurach, wyzwalaczach, funkcjach które mają swoje mechanizmy cache’owania
  • zapytania z hintem RECOMPILE
  • zapytania z słowem kluczowym COMPUTE, WHERE CURRENT OF
  • Zapytania donoszące się do zmiennych lokalnych
  • Zapytania z wycofanymi konstrukcjami języka TSQL
  • Zapytania w kursorach
  • Zapytania z XQuery
  • tzw. Prepared Queries (temat rozszerzymy w jednym z przyszłych wpisów)

Oprócz tego istnieje ogromna lista dodatkowych wyjątków kiedy autoparametryzacja nie ma miejsca jak np OPENQUERY czy IDENTITY ale nie będę ich tutaj wymieniał (możecie je znaleźć w dokumentacji).

Powiedzieliśmy o tym, że właściwość Parametrization ustawiamy na poziomie całej bazy danych – czy jest możliwe wymuszenie parametryzacji na poziomie pojedynczego zapytania? Oczywiście, że tak!  Służy do tego opcja PARAMETRIZATION SIMPLE/FORCED jednakże jej użycie nie jest takie proste jakbyśmy się tego spodziewali ponieważ możemy jej użyć wyłącznie w tzw. Plan Guide. Zilustrujmy to na przykładzie – na samym początku przywróćmy naszą bazę do trybu SIMPLE:

USE [master]
GO
ALTER DATABASE [AdventureWorks2014] SET PARAMETERIZATION SIMPLE WITH NO_WAIT
GO

Następnie wykonajmy nasze zapytanie sprawdzając czy zostanie ono automatycznie parametryzowane czy też nie.

Teraz stwórzmy sobie Plan Guide (póki co potraktujmy ten obiekt jako czarną skrzynkę – ogólnie za pomocą tego tworu możemy wpłynąć na plany konkretnych zapytań bez ich modyfikacji):

DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'SELECT * FROM Sales.SalesOrderDetail AS SOD JOIN Sales.SalesOrderHeader AS SOH 
ON SOD.SalesOrderID=SOH.SalesOrderID WHERE ProductID =897',  
    @stmt OUTPUT,   
    @params OUTPUT;  
EXEC sp_create_plan_guide   
    N'PlanGuide_test',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)';

Po ponownym wykonaniu zapytania otrzymaliśmy następujący plan zapytania:

Czyli bez względu na ustawienie bazy możemy wymusić parametryzacje dla poszczególnych zapytań. Zapewne wielu z Was zastanawia się jak jeszcze można wykorzystać Plan Guide – następny artykuł będzie im właśnie poświęcony zachęcam więc do zaglądania na stronę.

Podsumowując nasz dzisiejszy temat automatyczna parametryzacja może być bardzo przydatnym narzędziem w naszej codziennej pracy. Domyślnie parametryzowane są tylko te zapytania gdzie SQL Server ma pewność co do tego, że ich plan się nie zmieni w zależności od wartości parametru – taki tryb pracy nazywamy SIMPLE i jest to ustawienie bazy danych. Możemy wymusić bardziej agresywne parametryzowanie – FORCED licząc się z tym, że czasem może ona przynieść nieoczekiwane skutki. W przypadku gdy chcemy zmodyfikować zachowanie opisywanego mechanizmu dla jednego zapytania możemy to zrobić poprzez tzw. Plan guide , który pozwala zmodyfikować zachowanie zapytań bez ich modyfikacji.

2 Comments

  1. Niedawno zwrócono mi uwagę, że przy porównywaniu kosztów wykonania kilku zapytań w danym wsadzie, na końcu każdego z nich warto zastosować OPTION(RECOMPILE). O tym nie wiedziałem, a skompilowanie nowych planów spowodowało że otrzymałem bardzo duże różnice w kosztach wykonania, w porównaniu z planami z parametryzacją.

    • Rada była całkiem dobra ze względu na to, że zapytanie wykonane ponownie może być pobrane z cache przez co czas jego wykonania będzie nieco zakłamany i będzie krótszy o czas optymalizacji i kompilacji planu.

Leave a Reply