2020-11-27
Mul on ammu olnud teadmine, et "Ad Hoc" väärtus SQL Serveri instantsi parameetrite juures tuleb sisse lülitada. Hiljuti aga sattusin lugema kirjutist teemal, millal "Ad Hoc" sisse lülitada ja millal mitte (When to Turn On Optimize for Ad Hoc Workloads? - Pinal Dave). Hiljem jõudsin sellise artikli juurde Why Multiple Plans for One Query Are Bad - Brent Ozar. Ehk on olukorrad, kus selle väärtuse sisselülitamine ei ole hea mõte.
Lühidalt Ad Hoc-ist. Kui käivitatakse päring, siis päringu käivitamiseks luuakse plaan. Kui on palju ühekorseid päringuid, siis mälu, mida kasutatakse plaanide hoidmiseks, saab ruttu täis ja seda tuleb hakata tühjendama. Samuti on otsing paljude plaanide hulgast aeglasem, kui üksikute plaanide hulgast.
Siin tuleb appi Ad Hoc. Kui see väärtus sisse lülitada, siis peale päringu plaani genereerimist ei lisata seda plaanide hulka, vaid tehakse sellest hash ja salvestatakes ainult see hash. Kui nüüd tuleb teine samasugune päring, siis kirjutatakse plaan mällu.
Aga milles siis probleem, miks ei võiks Ad Hoc olla kogu aeg sisse lülitatud? Selgub, et teatud olukordades ei ole see hea mõte. Kõige halvem on see siis, kui igat päringut tehakse täpselt 2 korda. Sellisel juhul genereeritakse iga päringu jaoks plaan 2 korda. Ja siit tuleb nüüd leida tasakaal. Ehk mida rohkem on selliseid ühekordseid päringuid, seda tõenäolisem on, et Ad Hoc on vajalik.
Kui ühekordseid päringu plaane on vähe, siis on mõistlik, et juba esmasel plaani koostamisel kirjutatakse plaan mällu, mitte ei kirjutata mällu hash. Ja jälle jõuame vastuseni, et kas see väärtus lülitada sisse või mitte - siis vastus on, et sõltub olukorrast!