Ad hoc

2020-11-27

Ammusest ajast on mul 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) ja siis 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 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 As 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 sul selliseid ühekordseid päringuid, seda tõenäosem on, et Ad Hoc on vajalik. Kui aga ühekordseid päringu plaane on vähe, siis on ju 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!