W niektórych przypadkach optymalizator zapytań nie potrafi znaleźć najlepszego planu wykonania. W niektórych przypadkach można wyznaczyć lepszy plan wykonania za pomocą wskazówek optymalizacji (ang. optimizer hints). Aby skorzystać z podpowiedzi trzeba zmodyfikować zapytanie. Dodatkowo istnieje jeszcze inna opcja pozwalająca na zmianę planu wykonania - dosłownie przewodnik planu (ang. plan guide). Przewodników planu wykonania można użyć kiedy nie możemy lub z jakichś powodów nie chcemy zmienić tekstu zapytania. SQL Serwer używa przewodników planu aby dołączyć wskazówki lub ustalony plan wykonania do zapytań.

Wskazówki optymalizacyjne

Wskazówki optymalizacyjne (ang. optimizer hints) są dyrektywami dotyczącymi wykonania zapytań. Można użyć ich z instrukcją SELECT i innymi instrukcjami służącymi do modyfikacji danych. Istnieją trzy główne rodzaje wskazówek: wskazówki tabel (ang. table hints), wskazówki zapytań (ang. query hints) i wskazówki złączeń (ang. join hints). Kiedy używamy wskazówek to zmieniamy treść zapytań. SQL Serwer musi wykonać zapytanie lub jego część zawsze w ten sam sposób. Zapytanie może być częścią aplikacji więc może być trudne do zmiany. Rozkład dystrybucji danych może ulec zmianie po pewnym czasie i wskazówka która poprawiała wydajność w przeszłości może powodować po pewnym czasie jej spadek. Powinno używać się wszelkich innych sposobów jak tworzenie odpowiednich indeksów i statystyk oraz używanie przewodników planu wykonania przed użyciem wskazówek. Używaj wskazówek w ostateczności i sprawdzaj ich użyteczność co jakiś czas. Wskazówki definiujemy w sekcji OPTION klauzul SELECT, INSERT, UPDATE, DELETE i MERGE. Nie można używać wskazówek w podzapytaniach. Jeżeli wiele zapytań jest powiązanych operacją UNION to można użyć klauzuli OPTION dopiero po ostatnim zapytaniu. Można używać wskazówek zapytań w klauzuli INSERT za wyjątkiem przypadku w którym używamy wewnątrz wyrażenia SELECT. Następujące wskazówki zapytań są obsługiwane przez SQL Serwer 2012:

  • { HASH | ORDER } GROUP
  • { CONCAT | HASH | MERGE } UNION
  • { LOOP | MERGE | HASH } JOIN
  • EXPAND VIEWS
  • FAST number_rows
  • FORCE ORDER
  • IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
  • KEEP PLAN
  • KEEPFIXED PLAN
  • MAXDOP number_of_processors
  • MAXRECURSION number
  • OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ ,...n ] )
  • OPTIMIZE FOR UNKNOWN
  • PARAMETERIZATION { SIMPLE | FORCED }
  • RECOMPILE
  • ROBUST PLAN
  • USE PLAN N'xml_plan'
  • TABLE HINT ( exposed_object_name [ , <table_hint> [ [, ]...n ] ]

Szczegółowe wytłumaczenie działania każdej wskazówki można znaleźć na stronie Books online. Poniższe dwa zapytania zwracają ten sam zagregowany zbiór wynikowy. Pierwsze zapytanie pozwala zdecydować SQL Serwerowi jakiej techniki agregacji ma użyć natomiast drugie wymusza użycie operatora Stream Aggregate.

-- Hash match aggregate
SELECT qty, COUNT(*) AS num
FROM Sales.OrderDetails
GROUP BY qty;
-- Forcing stream aggregate
SELECT qty, COUNT(*) AS num
FROM Sales.OrderDetails
GROUP BY qty
OPTION (ORDER GROUP);

Plan wykonania: ep00001 Drugie zapytanie używa operatora Stream Aggregate, jednakże ponieważ ten operator wymaga posortowanych wejść to SQL Serwer dodał dodatkowo operator Sort do planu wykonania. Pomimo tego, że agregacja strumieniowa może być szybsza od agregacji skrótu to drugie zapytanie jest wolniejsze ponieważ musi posortować wejścia. Można dać wskazówkę dla pojedynczej tabeli w zapytaniu. Wskazówki tabel zawierają blokowanie i metody dostępu do pojedynczej tabeli lub widoku. Wskazówki tabel używamy w klauzuli FROM poprzedzając je słowem kluczowym WITHSQL Serwer obsługuje następujące wskazówki tabel:

  • NOEXPAND
  • INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
  • FORCESEEK [ ( index_value ( index_column_name [ ,... ] ) ) ]
  • FORCESCAN
  • FORCESEEK
  • KEEPIDENTITY
  • KEEPDEFAULTS
  • IGNORE_CONSTRAINTS
  • IGNORE_TRIGGERS
  • HOLDLOCK
  • NOLOCK
  • NOWAIT
  • PAGLOCK
  • READCOMMITTED
  • READCOMMITTEDLOCK
  • READPAST
  • READUNCOMMITTED
  • REPEATABLEREAD
  • ROWLOCK
  • SERIALIZABLE
  • SPATIAL_WINDOW_MAX_CELLS = integer
  • TABLOCK
  • TABLOCKX
  • UPDLOCK
  • XLOCK

Szczegółowe wytłumaczenie działania każdej wskazówki można znaleźć na stronie Books online. Prawdopodobnie najbardziej popularną wskazówką jet taka która wymusza użycie konkretnego indeksu. Poniżej znajdują się dwa zapytania z których pierwsze pozwala optymalizatorowi na wybór metody dostępu a drugie wymusza użycie indeksu nieklastrowanego.

-- Clustered index scan
SELECT orderid, productid, qty
FROM Sales.OrderDetails
WHERE productid BETWEEN 10 AND 30
ORDER BY productid;
-- Forcing a nonclustered index usage
SELECT orderid, productid, qty
FROM Sales.OrderDetails WITH (INDEX(idx_nc_productid))
WHERE productid BETWEEN 10 AND 30
ORDER BY productid;

Plan wykonania: ep00002 SQL Serwer obsługuje następujące wskazówki złączeń:

  • LOOP
  • HASH
  • MERGE
  • REMOTE

Szczegółowe wytłumaczenie działania każdej wskazówki można znaleźć na stronie Books online. Poniższe dwa zapytania zwracają ten sam zbiór wynikowy. Pierwsze zapytanie pozwala zdecydować SQL Serwerowi jakiego algorytmu złączenia użyć natomiast drugie wymusza użycie operatora merge join.

-- Nested loops join
SELECT O.custid, O.orderdate, OD.orderid, OD.productid,OD.qty
FROM Sales.Orders AS O
INNER JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid
WHERE O.orderid < 10250;
-- Forced merge join
SELECT O.custid, O.orderdate, OD.orderid, OD.productid,OD.qty
FROM Sales.Orders AS O
INNER MERGE JOIN Sales.OrderDetails AS OD
ON O.orderid = OD.orderid
WHERE O.orderid < 10250;

Plan wykonania: ep00003

Plan Guides

W przewodnikach planu można określić albo klauzulę OPTION albo plan wykonania dla wyrażenia które optymalizujesz. Taki przewodnik musi zawierać również wyrażenie T-SQL dla którego jest tworzone aby optymalizator mógł porównać zapytanie z zapisanym w przewodniku wyrażeniem. Przewodników planu wykonania nie można używać w wersji SQL Serwer 2012 Express. Można utworzyć następujące typy przewodników planu:

  • OBJECT - są używane przez optymalizator do dopasowania zapytań wewnątrz procedur składowanych, funkcji skalarnych użytkownika, funkcji tablicowych oraz wyzwalaczy DML.
  • SQL - są używane przez optymalizator do dopasowania samodzielnych zapytań.
  • TEMPLATE - są używane przez optymalizator do optymalizacji samodzielnych zapytań które mogą być parametryzowane.

Przewodniki planu tworzymy za pomocą procedury systemowej sys.sp_create_plan_guide. Przewodnik można wyłączyć, włączyć lub usunąć za pomocą procedury systemowej sys.sp_control_plan_guide. Można utworzyć przewodnik z zapisanego w pamięci planu wykonania za pomocą procedury systemowej sys.sp_create_plan_guide_from_handle. Za pomocą funkcji systemowej sys.fn_validate_plan_guide można sprawdzić poprawność przewodnika planu. Przewodnik może być niepoprawny po zmianach w schemacie bazy danych. Aby pobrać sparametryzowaną wersję zapytania można użyć procedury systemowej sys.sp_get_query_template. Ta procedura jest szczególnie przydatna przy tworzeniu przewodników TEMPLATE. Rozważmy następującą procedurę składowaną:

CREATE PROCEDURE Sales.GetCustomerOrders
(@custid INT)
AS
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE custid = @custid;

Dla zdecydowanej większości klientów, np klienta z identyfikatorem 71 zapytanie w procedurze nie jest bardzo selektywne dlatego skan tabeli lub indeksu klastrowanego jest najlepszym rozwiązaniem. Jednakże dla pewnej ilości klientów z tylko kilkoma zamówieniami wyszukanie w indeksie  jest lepsze np dla klienta o identyfikatorze 13. Jeżeli użytkownik pierw wykona procedurę dla klienta 13 to zapisany w pamięci plan wykonania nie będzie odpowiedni dla większości klientów. Tworząc przewodnik planu który będzie używał wskazówki zapytania która wymusi optymalizację dla klienta 71 zoptymalizujemy procedurę dla większości klientów. Poniższy kod tworzy taki przewodnik planu:

EXEC sys.sp_create_plan_guide
@name = N'Cust71',
@stmt = N'
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE custid = @custid;',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetCustomerOrders',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@custid = 71))';

Jeżeli wykonamy procedurę z różnymi parametrami po wyczyszczeniu pamięci aby mieć pewność, że stary plan nie jest zapisany w pamięci to SQL Serwer zawsze zoptymalizuje zapytanie dla wartości kolumny custid równej 71 i użyje skanowania indeksu klastrowanego.  Wykonajmy zapytanie dla wartości 13 i  71 i sprawdźmy plan wykonania:

-- Clearing the cache
DBCC FREEPROCCACHE;
-- Executing the procedure with different parameters
EXEC Sales.GetCustomerOrders @custid = 13;
EXEC Sales.GetCustomerOrders @custid = 71;

Plan wykonania: ep112211 Listę przewodników zapytań bazy danych możemy zobaczyć używając widoku sys.plan_guides. Można również podejrzeć listę wskazówek użytych w każdym przewodniku planu jak w poniższym zapytaniu:

SELECT plan_guide_id
,NAME
,scope_type_desc
,is_disabled
,query_text
,hints
FROM sys.plan_guides;

Poniższy kod czyści bazę danych:

EXEC sys.sp_control_plan_guide N'DROP', N'Cust71';
DROP PROCEDURE Sales.GetCustomerOrders;

Ćwiczenia

I. Stworzenie procedury ze wskazówką RECOMPILE

  1. Otwórz nowe okno SSMS i ustaw kontekst bazy danych na bazę danych TSQL2012.
    USE TSQL2012
    GO
    
  2. Napisz procedurę składowaną pobierającą wszystkie zamówienia dla pojedynczego klienta. Użyj klauzuli OPTION do wymuszenia rekompilacji. Zauważ, że realna procedura może zawierać wiele instrukcji i rekompilacja całej procedury może nie być najlepszym rozwiązaniem. Użycie przewodnika planu może również nie być najlepszym rozwiązaniem ponieważ plan z przewodnika może nie być najwydajniejszym planem dla wszystkich wartości parametru wejściowego. Wskazówka zapytania może być w takim wypadku najlepsza.
    CREATE PROCEDURE Sales.GetCustomerOrders
    (@custid INT)
    AS
    SELECT orderid, custid, empid, orderdate
    FROM Sales.Orders
    WHERE custid = @custid
    OPTION (RECOMPILE);
    
  3. Przetestuj procedurę z wartościami 13 i 71 i przejrzyj plan wykonania zapytań.
    EXEC Sales.GetCustomerOrders @custid = 13;
    EXEC Sales.GetCustomerOrders @custid = 71;
    
    Plan wykonania: ep_1
  4. Wyczyść bazę danych.
    DROP PROCEDURE Sales.GetCustomerOrders;
    

Podsumowanie

  1. Można wymusić wykonanie zapytania w specyficzny sposób za pomocą wskazówek optymalizatora.
  2. Używając przewodników zapytań można wymusić odpowiednie wykonanie zapytania bez jego modyfikacji.