Ladění dotazů: Table Hints

Table Hints jsou další možností, jak změnit výchozí chování Query Optimizeru. Table Hints lze použít pro většinu klasických DML příkazů jako jsou SELECT, UPDATE, INSERT, DELETE a MERGE. Při ladění a porovnávání více pracovních variant dotazu umožňují měnit např. způsob zamykání řádků v tabulce, použití konkrétního indexu při dohledávání dat, vynucovat způsob vyhledávání dat v tabulce, případně další vlastnosti. Zamykání dat v tabulce je samo o sobě zajímavé téma, které jsem již popsal v sérii článků věnované této problematice, kde si například v článku DB Lock: Granularita a Hierarchie povšimnout použití Table Hint pro změnu způsobu zamykání řádků ROWLOCK při změně záznamů v tabulce.

V následující ukázce se podíváme, jak přinutit Query Optimizer použít konkrétní index.

Z předchozího článku Ladění dotazů: Join Hints si vypůjčíme testovací příklad a podíváme se na původní plán.

SELECT
  P.LastName
  ,P.FirstName
  ,E.EmailAddress
FROM
  Person.Person AS P
  LEFT JOIN Person.EmailAddress AS E ON E.BusinessEntityID = P.BusinessEntityID
WHERE
  P.LastName = 'Smith'

Z plánu je patrné, že se při filtrování tabulky [Person].[Person] automaticky použil nejvhodnější index, jenž obsahuje filtrovaný sloupec LastName tj. IX_Person_LastName_FirstName_MiddleName. Nyní budeme zkoumat, jak se změní plán v případě existence pouze PK indexu.

SELECT
  P.LastName
  ,P.FirstName
  ,E.EmailAddress
FROM
  Person.Person AS P WITH(INDEX(PK_Person_BusinessEntityID))
  LEFT JOIN Person.EmailAddress AS E ON E.BusinessEntityID = P.BusinessEntityID
WHERE
  P.LastName = 'Smith'

Primární cluster index je postaven nad sloupcem BusinessEntityID. Dotaz však filtruje záznamy podle LastName, ale my jsme Optimizeru vnutili použití pouze PK indexu. Optimizer tedy nemá jinou možnost, než vzít celý index a procházet jej záznam po záznamu a na výstup posílat pouze záznamy s hodnotou ve sloupci LastName = ‚Smith‘. Celkově se tedy prodlouží doba zpracování dotazu. Plán obsahuje jeden vykřičník, který značí, že je vybrán nesprávný index.

V dalším příkladě půjdeme ještě dále. Založíme si nový index, který bude obsahovat pouze sloupec FirstName.

CREATE INDEX [IX_Person_FirstName] ON [Person].[Person]
(
  [FirstName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
       ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Dotaz pozměníme tak, aby při vyhledávání záznamů podle LastName použil nově vzniklý index IX_Person_FirstName.

SELECT
  P.LastName
  ,P.FirstName
  ,E.EmailAddress
FROM
  Person.Person AS P WITH(INDEX(IX_Person_FirstName))
  LEFT JOIN Person.EmailAddress AS E ON E.BusinessEntityID = P.BusinessEntityID
WHERE
  P.LastName = 'Smith'

Plán je o něco málo složitější, ale není to nic překvapivého. Možná leckoho překvapí, že lze dohledat záznam, jenž je filtrován podle pole, které se v indexu nevyskytuje. Každý záznam indexu obsahuje i sloupec primárního klíče, který přidává unikátnost a jednoznačnou identifikovatelnost záznamu v rámci tabulky.

Z detailu operace Index Scan podle indexu IX_Person_FirstName tedy vidíme, že výstupem jsou dva sloupce BusinessEntityID a FirstName. Index Scan projde všechny záznamy v tabulce a jelikož neobsahuje filtrovaný sloupec, pošle je všechny na výstup. Povšimněte si počet průchodů, který je roven číslu 4 (operace je paralelizovaná a prováděna 4 thready). Key Lookup operace provádí dohledání LastName sloupce a jeho filtrování pro každý jednotlivý řádek, který je vrácen operací Index Scan. Dohledání řádku z tabulky je realizováno na základě sloupce BusinessEntityID, jenž je v celé tabulce unikátní. Počet průchodů je roven počtu řádků na výstupu operace Index Scan. Opět je tento plán ještě o poznání pomalejší než plán předchozí.

V posledním příkladě si ukážeme změnu operace procházení tabulky užitím hintu FORCESCAN.

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'

Místo původní operace Index Seek se použije operace Index Scan, která prochází všechny řádky tabulky sice podle správného indexu IX_Person_LastName_FirstName_MiddleName. Stále je to však procházení všech záznamů v tabulce.

Table Hints jsou zajímavým nástrojem, jak pochopit princip procházení a vyhledávání záznamů v tabulce. Názorně se můžete přesvědčit, jaký má dopad použití nesprávné operace či nesprávně zvoleného indexu na výkon a optimalizaci dotazu.