Ladění dotazů: Query Hints

V předchozích článcích jsme se podívali na Join Hints a Table Hints. Nyní nám zbývá představit si posledního člena z trojlístku, kterým jsou Query Hints. Změnou tohoto nastavení lze ovlivnit chování celého dotazu a tedy všech jeho operací.

Postupně si projdeme pár příkladů a podíváme se na vygenerované plány. Začneme nejprve prvním referenčním dotazem s původním plánem.

SELECT
  P.LastName
  ,P.FirstName
  ,E.EmailAddress
  ,A.City
FROM
  Person.Person AS P
  LEFT JOIN Person.EmailAddress AS E ON E.BusinessEntityID = P.BusinessEntityID
  LEFT JOIN Person.BusinessEntityAddress AS BEA ON BEA.BusinessEntityID = P.BusinessEntityID
  LEFT JOIN Person.Address AS A ON A.AddressID = BEA.AddressID
WHERE
  P.LastName = 'Smith'

JOIN QUERY HINTS

Již dříve jsme si nastínili princip JOIN hintů.

SELECT
  P.LastName
  ,P.FirstName
  ,E.EmailAddress
  ,A.City
FROM
  Person.Person AS P
  LEFT JOIN Person.EmailAddress AS E ON E.BusinessEntityID = P.BusinessEntityID
  LEFT JOIN Person.BusinessEntityAddress AS BEA ON BEA.BusinessEntityID = P.BusinessEntityID
  LEFT JOIN Person.Address AS A ON A.AddressID = BEA.AddressID
WHERE
  P.LastName = 'Smith'
OPTION (LOOP JOIN)

Při porovnání původního a změněného plánu, můžeme konstatovat, že došlo k nahrazení různých typů spojení tabulek jedním uvedeným typem.

MAXDOP (MAX Degree Of Parallelism)

Možná jste již někdy při různých příležitostech narazili na volbu MAXDOP. Touto volbou můžete omezit, zakázat nebo naopak povolit zpracování dotazu na více procesorech souběžně. Někdy paralelní zpracování může pomoci a snížit dobu běhu dotazu, jindy je naopak nežádoucí a je potřeba ho zakázat.

Začněme jednoduchým dotazem, který provede výpis a seřazení dat, bez jakékoli dodatečné optimalizace.

SELECT
  *
FROM
  Sales.OrderTracking
ORDER BY
  EventDetails

Povšimněte si prosím grafického symbolu žluté kolečko s dvěma šipkami značící použití více jak jednoho threadu pro danou operaci. Jinými slovy Optimizer se rozhodl práci s daty paralelizovat.

Z výsledného plánu se lze dočíst, že byly použity čtyři vlákna, což je maximální počet dostupných CPU.

Nyní dotaz změníme a přidáme volbu, která upravuje počet použitých vláken na 1.

SELECT
  *
FROM
  Sales.OrderTracking
ORDER BY
  EventDetails
OPTION (MAXDOP 1)

Na této verzi grafického znázornění plánu zmizely symboly značící paralelizaci.

Výsledný plán můžete porovnat s původním plánem. Nicméně je vidět, že bylo opravdu použito pouze jedno vlákno. Za domácí úkol si zkuste modifikovat dotaz a doplnit OPTION (MAXDOP 0), popř. číslo vyšší než je počet logických jader vašeho systému.

RECOMPILE vs. KEEP PLAN

Častým pomocníkem při ladění dotazů je možnost vynutit si ručně nové sestavení plánu. Při jemném dolaďování parametrů dotazu a následném porovnávání s předchozími verzemi, nemusí Optimizer nutně provést čerstvé sestavení plánu, ale použít již nějaký starší plán uložený v cache paměti.

SELECT
  *
FROM
  Sales.OrderTracking
ORDER BY
  EventDetails
OPTION (RECOMPILE)

Existuje více kritérií podle kterých si Optimizer určí nutnost rekompilace plánu. Příkladem může být změna schématu tabulky (přidání, odebrání sloupce atp.). Pokud je do tabulky vloženo/smazáno/změněno určité množství řádků, provede se taktéž rekompilace.

KEEP PLAN naopak posouvá hranice v počtu změněných řádků v tabulce. Rekompilace se tedy neprovádí tak často.

KEEPFIXED PLAN potlačuje rekompilaci ještě více, takže nastává až při změně schématu tabulky.

Table Hint vs. Query Hint

V minulém článku jsme si pověděli něco o Table Hints. Nejprve si uvedeme příklad a pak se jej pokusím osvětlit.

SELECT
  P.LastName
  ,P.FirstName
  ,E.EmailAddress
FROM
  Person.Person AS P WITH(FORCESCAN)
  LEFT JOIN Person.EmailAddress AS E ON E.BusinessEntityID = P.BusinessEntityID
WHERE
  P.LastName = 'Smith'
--OPTION (TABLE HINT (P, FORCESEEK))

Pokud si spustíme dotaz se zakomentovaným řádkem s OPTION (TABLE HINT (P, FORCESEEK)), bude se prohledávat tabulka [Person] pomocí operace Index Scan. Celé toto nastavení lze však potlačit právě pomocí přidání Query Hint. Po odkomentování posledního řádku dojde k potlačení volby FORCESCAN a naopak se použije volba FORCESEEK.

Přehled dalších voleb a jejich detailní popis naleznete v oficiální dokumentaci ke Query Hints