Modyfikacja planu wykonania przy wykorzystaniu Plan Guide

Do napisania niniejszego artykułu skłoniło mnie pytanie jakie usłyszałem na jednym ze spotkań Data Community. Chodziło w nim mianowicie o to, że zadający miał problem z zapytaniem, którego nie mógł zmodyfikować, a plan wykonania jaki wybierał SQL Server jest całkowicie nieakceptowalny i niewydajny. Dodam jeszcze, że osoba ta nie miała do dyspozycji Query Store i najnowszej wersji SQL Server. Jakie jest jedno z rozwiązań tego problemu? O tym postaram się dziś opowiedzieć.

Często zdarza się, że zapytania zostały napisane przez kogoś innego, a my musimy radzić sobie z ich optymalizacją. Jeszcze gorszym scenariuszem jest fakt, że nie możemy zmodyfikować samego zapytania, a wiemy, że ktoś użył tam niewłaściwego hinta który więcej szkodzi niż pomaga. W cache możemy również zaobserwować, że SQL Server wybrał niezbyt optymalny algorytm złączenia – co możemy w takiej sytuacji zrobić? Mamy kilka możliwości (szczególnie w nowszej wersji SQL Server) i jedną z nich jest bohater niniejszego artykułu, a mianowicie Plan Guide, który pozwala nam wpływać na zapytania bez modyfikacji ich właściwej treści – zapraszam do lektury.

Algorytm działania SQL Server w tym aspekcie przedstawia się następująco:

Tak jak możecie zauważyć do standardowej ścieżki postępowania doszło sprawdzenie czy nie ma zdefiniowanego plan guide. W tym miejscu warto zdać sobie sprawę, że Plan Guide nie eliminuje kompilacji i sam w sobie nie ma na nią jakiegoś magicznego wpływu.

Aby zobrazować działanie tego mechanizmu stwórzmy sobie najpierw następującą procedurę:

use wideworldImporters
GO

CREATE PROC dbo.GetQuantityPerStockItemDeliveryDate
AS
SELECT StockItemName,
       I.ConfirmedDeliveryTime,
       SUM(SIL.Quantity) AS Qty
FROM [Sales].[Invoices] AS I
     INNER JOIN [Sales].[InvoiceLines] AS SIL
	ON SIL.InvoiceID = I.InvoiceID
     INNER JOIN [Warehouse].[StockItems] AS WSI 
	ON WSI.StockItemID = SIL.StockItemID
WHERE I.ConfirmedDeliveryTime IS NOT NULL
GROUP BY StockItemName,
         I.ConfirmedDeliveryTime
OPTION (MAXDOP 1, LOOP JOIN);

Przypuśćmy, że w takiej formie kod dostarczył do nas dostawca i my w żaden sposób nie możemy go zmieniać. Jak widzimy nie jest to majstersztyk i cechuje go kilka rzeczy, które przysparzają nam problemów tj. hint MAXDOP 1, który ogranicza nasze zapytanie do tego aby działało na jednym wątku, oraz wymuszenie algorytmu złączenia NESTED LOOPS. Oba hinty nie są zbyt bezpieczne i mogą powodować problemy wydajnościowe. Plan tego zapytania wygląda następująco:

Widzimy, że analizowane przez nas zapytanie jest jednowątkowe i jedynym użytym algorytmem złączenia zgodnie z wpisanym hintem jest Nested Loops. Samo zapytanie wykonuje się bardzo długo można powiedzieć, że wręcz nieakceptowalnie. Spróbujmy to w jakiś sposób naprawić – aby to zrobić użyjemy wspomnianego już Plan Guide. Możemy go stworzyć na kilka sposobów, w tym miejscu zrobimy to za pomocą procedury składowanej o nazwie sp_create_plan_guide:

sp_create_plan_guide   
@name = N'MyGuide',  
@stmt = N'SELECT StockItemName,
       I.ConfirmedDeliveryTime,
       SUM(SIL.Quantity) AS Qty
FROM [Sales].[Invoices] AS I
     INNER JOIN [Sales].[InvoiceLines] AS SIL
	ON SIL.InvoiceID = I.InvoiceID
     INNER JOIN [Warehouse].[StockItems] AS WSI 
	ON WSI.StockItemID = SIL.StockItemID
WHERE I.ConfirmedDeliveryTime IS NOT NULL
GROUP BY StockItemName,
         I.ConfirmedDeliveryTime
OPTION (MAXDOP 1, LOOP JOIN);',  
@type = N'OBJECT',  
@module_or_batch = N'dbo.GetQuantityPerStockItemDeliveryDate',  
@params = NULL,  
@hints = N'OPTION(HASH JOIN)';

Jak możecie zauważyć procedura ta przyjmuje kilka parametrów – powiedzmy o nich kilka słów:

• name – nazwa plan guide
• stmt – zapytanie do którego będziemy się odnosić (musi to być dokładnie takie zapytanie jak w procedurze/zapytaniu adhoc inaczej plan guide nie zadziała lub nie zostanie stworzony. Nawet najmniejszy biały znak spowoduje, że hash z ciągu znaków zapytania będzie inny co uniemożliwi użycie guide’a.)
• type – mamy do dyspozycji trzy typy guide’ów:
  • OBJECT – odnosi się do obiektów takich jak procedury, funkcje użytkownika czy też np. wyzwalacze
  • SQL – odnosi się do zapytań adhoc nie będących częścią żadnego obiektu
  • TEMPLATE – specjalny typ odnoszący się do zapytań adhoc pozwalający na nadpisanie ustawienia PARAMETRIZATION
• module_or_batch – nazwa modułu (np. procedury) jeśli tworzymy plan guide typu OBJECT
• params – dla plan guide SQL oraz TEMPLATE ciąg parametrów dla których ma być zastosowany dany plan guide
• hints – hinty jakie mają zostać dołączone do zapytania (dołączone jako OPTION w zapytaniu podanym w parametrze stmt). Jeśli przekażemy do tego parametru NULL to będzie to równoznaczne z usunięciem wszystkich hintów z zapytania.

Całkiem dużo możliwości i może się to wydawać dosyć skomplikowane jednakże w rzeczywistości jest kompletnie inaczej. W powyższym Plan guide użyliśmy typu OBJECT ze względu na fakt, że chcemy go zastosować do kodu TSQL, który jest wewnątrz procedury składowanej. Po jego stworzeniu i wykonaniu procedury raz jeszcze plan zapytania wygląda następująco:

Zniknęły problematyczne złączenia Nested Loops, a samo zapytanie może wykorzystać wiele wątków bo hint MAXDOP 1 został usunięty. Proste prawda? A to jeszcze nie wszystko co mamy do dyspozycji.

Omawiane obiekty mogą również być pomocne w przypadku gdy mamy do czynienia z negatywnymi skutkami tzw. parameter sniffingu przy skośnym rozkładzie częstości. Możemy wtedy wymusić optymalizację dla konkretnej wartości parametru – dla przykładu poniżej znajdziecie definicję kolejnej procedury testowej o nazwie GetInvoicLinesByTaxRate, która przyjmuje jeden parametr:

CREATE PROC GetInvoiceLinesByTaxRate @TaxRate DECIMAL(18,2)
AS
SELECT SI.TotalDryItems,TaxAmount,Quantity,Description FROM Sales.InvoiceLines AS SIL
JOIN Sales.Invoices AS SI
ON SI.InvoiceID=SIL.InvoiceID
WHERE TaxRate=@TaxRate

Procedura nie jest stabilna bo w zależności od wartości parametru otrzymujemy różne plany zapytania.

Dla wartości 15:

Dla wartości 10:

Mamy zatem do czynienia z klasycznym wyborem pomiędzy Nested Loops, a Hash Match – przypuśćmy, że naszym celem będzie “zasugerowanie” aby optymalizacja była wykonana dla wartości parametru 15 gdyż zdecydowana większość wywołań tej procedury zawiera właśnie tą wartość. Do tego celu użyjemy oczywiście OPTIMIZE FOR:

sp_create_plan_guide   
@name = N'OptimizeForTestPlanGuide',  
@stmt = N'SELECT SI.TotalDryItems,TaxAmount,Quantity,Description FROM Sales.InvoiceLines AS SIL
JOIN Sales.Invoices AS SI
ON SI.InvoiceID=SIL.InvoiceID
WHERE TaxRate=@TaxRate',  
@type = N'OBJECT',  
@module_or_batch = N'GetInvoiceLinesByTaxRate',  
@params = NULL,  
@hints = N'OPTION (OPTIMIZE FOR (@TaxRate = 10.00))';

Po ponownym uruchomieniu procedury z wartością 10 zobaczymy, że wartością skompilowaną tego parametru jest 15:

DBCC FREEPROCCACHE

EXEC GetInvoiceLinesByTaxRate @TaxRate=10.000

Szybko i bez większego wysiłku problem został rozwiązany bez najmniejszej ingerencji w kod procedury.

Kolejną funkcjonalnością o jakiej chciałbym wspomnieć jest wymuszenie konkretnego planu wykonania dla danego zapytania. Myślę, że aktualnie jeśli kogoś zapytamy o to w jaki sposób wymusić plan zapytania to w przeważającej ilości przypadków usłyszymy “Query Store”, ale również dobrą odpowiedzią jest Plan Guide! Może nie jest to aż tak łatwe jak w przypadku QS ale jednak da się – sprawdźmy jak możemy tego dokonać. Zanim przejdziemy do właściwego zadania usuńmy plan guide który stworzyliśmy w poprzednim przykładzie – użyjemy do tego celu procedury sp_control_plan_guide:

EXEC sp_control_plan_guide N'DROP', N'OptimizeForTestPlanGuide';

Procedura ta służy za wiele operacji na Plan Guide i może ona przyjmować następujące samo opisujące parametry:

• DROP
• DROP ALL
• DISABLE
• DISABLE ALL
• ENABLE
• ENABLE ALL

Oczywiście jeśli wybierzemy z powyższej listy którąś z operacji “ALL” to nie podajemy nazwy Plan Guide ponieważ odnosi się ona do wszystkich obiektów tego typu w bieżącej bazie danych.

Wracając do meritum czeka nas dosyć problematyczne zadanie – musimy mianowicie znaleźć plan, który chcemy wymusić. Jak to zrobić? Myślę, że najłatwiej ściągnąć go z cache – wykonajmy naszą procedurę z poprzedniego przykładu parametrem 15 bo właśnie plan dla tego parametru nas interesuje:

DBCC FREEPROCCACHE

EXEC GetInvoiceLinesByTaxRate @TaxRate=15.00

Następnie używając widoków dynamicznych możemy podejrzeć cache i znajdujący się w nim plan zapytania. Oczywiście warunek WHERE powinien być napisany tak aby jednoznacznie określić zapytanie o które nam chodzi:

SELECT query_plan,st.text  
    FROM sys.dm_exec_query_stats AS qs   
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp  
    WHERE st.text LIKE N'%Sales.InvoiceLines AS SIL%'
	AND st.text NOT LIKE '%query_plan%';

Możemy taki plan wykorzystać w plan guide – najlepiej przypisać sobie go do zmiennej gdyż kopiowanie z rezultatu SSMS może nie być najlepszym rozwiązaniem gdyż narzędzie to nie wyświetla całej wartości w przypadku długich stringów, a jedynie jej przyciętą formę:

DECLARE @query_plan nvarchar(max)

SET @query_plan=(SELECT query_plan  
    FROM sys.dm_exec_query_stats AS qs   
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp  
    WHERE st.text LIKE N'%Sales.InvoiceLines AS SIL%'
	AND st.text NOT LIKE '%query_plan%')

exec sp_create_plan_guide   
@name = N'OptimizeForTestPlanGuide',  
@stmt = N'SELECT SI.TotalDryItems,TaxAmount,Quantity,Description FROM Sales.InvoiceLines AS SIL
JOIN Sales.Invoices AS SI
ON SI.InvoiceID=SIL.InvoiceID
WHERE TaxRate=@TaxRate',  
@type = N'OBJECT',  
@module_or_batch = N'GetInvoiceLinesByTaxRate',  
@params = NULL,  
@hints = @query_plan;

Nasz Plan guide został stworzony – dlatego też możemy go przetestować dla wartości 10 znanego już parametru@TaxRate:

DBCC FREEPROCCACHE

EXEC GetInvoiceLinesByTaxRate @TaxRate=10.00

Plan wykonania oczywiście wyglądał tak jak było to zamierzone – dodatkowo warto zwrócić uwagę na właściwość “Use plan” która ustawiona na True daje nam informację, że plan został wymuszony:

Oczywistym jest fakt, że sprawdzanie działania omawianego mechanizmu w taki sposób nie jest najlepszym pomysłem w przypadku środowisk produkcyjnych. Aby sprawdzić czy Plan guide działa w prawidłowy sposób możemy również użyć Profilera lub Extended Events – postaram się to przedstawić na podstawie tego drugiego narzędzia. Skrypt tworzący sesję wygląda następująco:

CREATE EVENT SESSION [PlanGuides] ON SERVER 
ADD EVENT sqlserver.plan_guide_successful(
    ACTION(sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.plan_guide_unsuccessful(
    ACTION(sqlserver.session_id,sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\XEvents\PlanGuides.xel',
max_file_size=(10),
max_rollover_files=(1))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,STARTUP_STATE=OFF)
GO

Będziemy śledzić dwa zdarzenia plan_guide_successful oraz plan_guide_unsucessful, których znaczenia chyba nie trzeba omawiać. Po uruchomieniu naszej sesji xE i kilkukrotnym uruchomieniu procedury testowej w pliku wynikowym znalazłem następujące wpisy:

Oczywiście niepowodzenie również może zostać przez nas zarejestrowane. Zdarzenie to pojawi się wtedy gdy nasz plan z jakiegoś powodu nie może zostać wymuszony. Dla przykładu usuńmy indeks klastrowany PK_Sales_Invoices z tabeli (wcześniej trzeba usunąć wszystkie klucze obce do niego się odnoszące):

ALTER TABLE [Warehouse].[StockItemTransactions] DROP CONSTRAINT [FK_Warehouse_StockItemTransactions_InvoiceID_Sales_Invoices]
GO

ALTER TABLE [Sales].[InvoiceLines] DROP CONSTRAINT [FK_Sales_InvoiceLines_InvoiceID_Sales_Invoices]
GO

ALTER TABLE [Sales].[CustomerTransactions] DROP CONSTRAINT [FK_Sales_CustomerTransactions_InvoiceID_Sales_Invoices]
GO

ALTER TABLE [Sales].[Invoices] DROP CONSTRAINT [PK_Sales_Invoices]
GO

Po raz kolejny po uruchomieniu zapytania testowego nasz plik śledzący odnotował to czego się spodziewaliśmy:

Wytłumaczenie jest tutaj również oczywiste gdyż SQL Server chciał użyć Plan Guide ale ze względu na fakt, iż indeks który w planie wskazanym przez Plan Guide nie istnieje to cała struktura musiała zostać zignorowana i plan musiał zostać wygenerowany na nowo. W przypadku gdy musimy sprawdzić czy nasze Plan Guide działają (np. po migracji)  możemy użyć Extended Events lub funkcji sys.fn_validate_plan_guide w połączeniu z widokiem sys.plan_guides:

select 
	sp.name,
	sp.create_date,
	sp.query_text,
	sp.scope_type_desc,
	val.message 
from sys.plan_guides AS sp
cross apply  sys.fn_validate_plan_guide(sp.plan_guide_id) as val

Jak możecie zauważyć mamy tutaj komplet informacji wraz z informacją o tym dlaczego plan nie może zostać użyty. Osobiście jeśli miałbym wybierać to ten sposób jest dużo bardziej przyjazny niż XE i z nim polecam się zapoznać.

Wiemy zatem jak działać z Plan Guide oraz z zapytaniami będącymi częścią obiektów takich jak procedury składowane. Jak wspomniałem już wcześniej możemy również używać tego mechanizmu dla zapytań adhoc jednakże jest to o wiele rzadziej używane podejście. Jest to również problematyczne gdyż najmniejszy komentarz czy tez inna zmiana powoduje, że z perspektywy SQL Server jest to całkowicie inne zapytanie. Tworzenie PG przy pomocy procedury sp_create_plan_guide jest bardzo podobne do omówionego wcześniej przykładu dla procedur składowanych z tym, że dla parametru type tejże procedury podajemy SQL dlatego też pozwolę sobie ominąć omawianie tego przypadku. Pokażę Wam natomiast dodatkową opcję pozwalającą na wymuszenie planu, a jest nią hint USE PLAN. Zanim do niego przejdziemy przywrócćmy wcześniej usunięte klucze:

ALTER TABLE [Sales].[Invoices] ADD  CONSTRAINT [PK_Sales_Invoices] PRIMARY KEY CLUSTERED 
(
	[InvoiceID] ASC
)
GO
USE [WideWorldImporters]
GO

ALTER TABLE [Sales].[InvoiceLines]  
WITH CHECK ADD  CONSTRAINT [FK_Sales_InvoiceLines_InvoiceID_Sales_Invoices] 
FOREIGN KEY([InvoiceID])
REFERENCES [Sales].[Invoices] ([InvoiceID])
GO

ALTER TABLE [Sales].[InvoiceLines] 
CHECK CONSTRAINT [FK_Sales_InvoiceLines_InvoiceID_Sales_Invoices]
GO

ALTER TABLE [Sales].[CustomerTransactions]  
WITH CHECK ADD  CONSTRAINT [FK_Sales_CustomerTransactions_InvoiceID_Sales_Invoices] 
FOREIGN KEY([InvoiceID])
REFERENCES [Sales].[Invoices] ([InvoiceID])
GO

ALTER TABLE [Sales].[CustomerTransactions] 
CHECK CONSTRAINT [FK_Sales_CustomerTransactions_InvoiceID_Sales_Invoices]
GO
ALTER TABLE [Warehouse].[StockItemTransactions]  
WITH CHECK ADD  CONSTRAINT [FK_Warehouse_StockItemTransactions_InvoiceID_Sales_Invoices] 
FOREIGN KEY([InvoiceID])
REFERENCES [Sales].[Invoices] ([InvoiceID])
GO

ALTER TABLE [Warehouse].[StockItemTransactions] 
CHECK CONSTRAINT [FK_Warehouse_StockItemTransactions_InvoiceID_Sales_Invoices]
GO

Użycie USE PLAN nie odbiega od użycia standardowych hintów i w naszym przypadku testowym wygląda następująco:

DECLARE @TaxRate decimal(18,2)=10.000
SELECT SI.TotalDryItems,TaxAmount,Quantity,Description 
FROM Sales.InvoiceLines AS SIL JOIN Sales.Invoices AS SI ON SI.InvoiceID=SIL.InvoiceID WHERE TaxRate=@TaxRate
OPTION (USE PLAN '<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.5" Build="13.0.1601.5"><BatchSequence><Batch><Statements><StmtSimple StatementText="&#xd;&#xa;CREATE PROC GetInvoiceLinesByTaxRate @TaxRate DECIMAL(18,2)&#xd;&#xa;AS&#xd;&#xa;SELECT SI.TotalDryItems,TaxAmount,Quantity,Description FROM Sales.InvoiceLines AS SIL&#xd;&#xa;JOIN Sales.Invoices AS SI&#xd;&#xa;ON SI.InvoiceID=SIL.InvoiceID&#xd;&#xa;WHERE TaxRate=@TaxRate" StatementId="1" StatementCompId="4" StatementType="SELECT" StatementSqlHandle="0x09005608BD5239F32582870B02DEBEB43F360000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="1499152386" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="12.3698" StatementEstRows="1269.15" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x91F6A2F8C969FFBC" QueryPlanHash="0x399586B022427C79" CardinalityEstimationModelVersion="130"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan CachedPlanSize="56" CompileTime="19" CompileCPU="19" CompileMemory="520"><ThreadStat Branches="1"></ThreadStat><MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="1600"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209703" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="2"></OptimizerHardwareDependentProperties><RelOp NodeId="0" PhysicalOp="Parallelism" LogicalOp="Gather Streams" EstimateRows="1269.15" EstimateIO="0" EstimateCPU="0.0357313" AvgRowSize="128" EstimatedTotalSubtreeCost="12.3698" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="Description"></ColumnReference><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="Quantity"></ColumnReference><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="TaxAmount"></ColumnReference><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[Invoices]" Alias="[SI]" Column="TotalDryItems"></ColumnReference></OutputList><Parallelism><RelOp NodeId="1" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="1269.15" EstimateIO="0" EstimateCPU="0.00565681" AvgRowSize="128" EstimatedTotalSubtreeCost="12.334" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch"><OutputList><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="Description"></ColumnReference><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="Quantity"></ColumnReference><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="TaxAmount"></ColumnReference><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[Invoices]" Alias="[SI]" Column="TotalDryItems"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><Hash BitmapCreator="1"><DefinedValues><DefinedValue><ColumnReference Column="Opt_Bitmap1004"></ColumnReference></DefinedValue></DefinedValues><HashKeysBuild><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="InvoiceID"></ColumnReference></HashKeysBuild><HashKeysProbe><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[Invoices]" Alias="[SI]" Column="InvoiceID"></ColumnReference></HashKeysProbe><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1125.99" EstimateIO="3.6935" EstimateCPU="0.125624" AvgRowSize="137" EstimatedTotalSubtreeCost="3.81912" TableCardinality="228265" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="InvoiceID"></ColumnReference><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="Description"></ColumnReference><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="Quantity"></ColumnReference><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="TaxAmount"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="InvoiceID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="Description"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="Quantity"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="TaxAmount"></ColumnReference></DefinedValue></DefinedValues><Object Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Index="[PK_Sales_InvoiceLines]" Alias="[SIL]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[WideWorldImporters].[Sales].[InvoiceLines].[TaxRate] as [SIL].[TaxRate]=[@TaxRate]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[InvoiceLines]" Alias="[SIL]" Column="TaxRate"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@TaxRate"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp><RelOp NodeId="3" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="705.1" EstimateIO="8.41506" EstimateCPU="0.0388198" AvgRowSize="15" EstimatedTotalSubtreeCost="8.45388" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[Invoices]" Alias="[SI]" Column="InvoiceID"></ColumnReference><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[Invoices]" Alias="[SI]" Column="TotalDryItems"></ColumnReference></OutputList><Filter StartupExpression="0"><RelOp NodeId="4" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="70510" EstimateIO="8.41506" EstimateCPU="0.0388198" AvgRowSize="15" EstimatedTotalSubtreeCost="8.45388" TableCardinality="70510" Parallel="1" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[Invoices]" Alias="[SI]" Column="InvoiceID"></ColumnReference><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[Invoices]" Alias="[SI]" Column="TotalDryItems"></ColumnReference></OutputList><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[Invoices]" Alias="[SI]" Column="InvoiceID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[Invoices]" Alias="[SI]" Column="TotalDryItems"></ColumnReference></DefinedValue></DefinedValues><Object Database="[WideWorldImporters]" Schema="[Sales]" Table="[Invoices]" Alias="[SI]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp><Predicate><ScalarOperator ScalarString="PROBE([Opt_Bitmap1004],[WideWorldImporters].[Sales].[Invoices].[InvoiceID] as [SI].[InvoiceID])"><Intrinsic FunctionName="PROBE"><ScalarOperator><Identifier><ColumnReference Column="Opt_Bitmap1004"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[WideWorldImporters]" Schema="[Sales]" Table="[Invoices]" Alias="[SI]" Column="InvoiceID"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Predicate></Filter></RelOp></Hash></RelOp></Parallelism></RelOp><ParameterList><ColumnReference Column="@TaxRate" ParameterCompiledValue="(10.00)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>')

Patrząc na to jak trzeba plan wymusić zdaję sobie sprawę z wielu trudności jakie to przysparza. Nigdy nie używajcie tego w swoim kodzie “na sztywno” – jedyne zastosowanie jakie przychodzi mi do głowy to sytuacja gdy testujemy różne warianty wydajnościowe itp.

Ostatnim elementem, o którym chciałbym wsponieć jest Plan guide typu Template, który pozwala sterować trybem parametryzacji. Jednakże aby się nie powtarzać się odsyłam Was do artykułu, który opisuje ten aspekt i omawia pokrótce plan guide z nim związany – znajdziecie go tutaj.

To by było na tyle jeśli chodzi o Plan Guide. Jak zapewne zauważyliście jest to bardzo ciekawy mechanizm w SQL Server i już nieco zapomnianym mimo obecności w naszym ulubionym siliniku bazodanowym od wersji 2005. Współcześnie dużo łatwiej użyć Query Store do analogicznych operacji jednakże nie każdy posiada SQL Server 2016 lub wyżej. Według mnie usuwanie hintów z zapytań jest bardzo proste w tym mechanizmie i właśnie tutaj wypatrywał bym szansy na efektywne użycie Plan Guides. To co wydaje się logiczne to fakt, żeby oczywiście nie nadużywać opisywanego mechanizmu i używać go z dużą ostrożnością co zresztą dotyczy większości rzeczy związanych z SQL Server. Mimo wszystko mam nadzieję, że wiedza ta komuś się przyda bo w tym wypadku jak i wielu innych “lepiej znać niż nie”.