SQL Serveri parameeter: Optimize for Ad Hoc Workloads ja Query Store
Serveri jõudluse häälestamisel on üks kriitilisemaid ressursse mälu (Plan Cache). Ad hoc ehk dünaamiliselt genereeritud päringud võivad tekitada olukorra, kus mälupuhver täitub ühekordsete täitmisplaanidega, surudes sealt välja korduvalt kasutatavad protseduurid ja andmed.
Optimize for Ad Hoc Workloads: See seadistus muudab plaanide mällu salvestamise loogikat:
-
Esmakordne päring: Server ei salvesta täisplaani, vaid ainult väikese viida (plan stub / hash).
-
Teistkordne päring: Kui täpselt sama päring jõuab serverisse uuesti, genereeritakse ja salvestatakse täismõõdus plaan.
See kaitseb serverit olukordades, kus süsteem on üle ujutatud unikaalsete päringutega (tüüpiline puudulikult parameetriseeritud koodi või teatud ORM-ide puhul). Miinuspoolena kaasneb riskiolukord: kui päringut käivitatakse täpselt kaks korda, peab server plaani genereerima kaks korda, mis kulutab lisaks CPU ressurssi.
Muutus SQL Server 2016+ versioonides Varasemates versioonides võis tekkida hirm, et ad hoc optimeerimise sisselülitamisel kaob nähtavus ühekordsete päringute üle. SQL Server 2016 ja uuemates versioonides on see probleem lahendatud tänu Query Store'ile.
Query Store salvestab päringute statistika ja plaanid sõltumata sellest, kas plaan on mälus (RAM) või mitte. Isegi kui serveri tasemel on plan cache kaitstud ühekordsete plaanide eest, püüab Query Store need andmed kinni.
Miks lülitada "Ad Hoc" optimeerimine tänapäeval alati sisse? Kui andmebaasis on Query Store aktiivne, siis on see kombinatsioon parim lahendus järgmistel põhjustel:
-
Mälu säästmine: Plan Cache püsib puhas ühekordsetest prügiplaanidest, hoides ruumi väärtuslikumate andmete jaoks.
-
Nähtavus säilib: Query Store annab täieliku ülevaate ka nendest päringutest, millest mällu jäi vaid hash. Analüüsivõimekus ei vähene.
-
Väiksem halduskoormus: Kui varem pidime kaaluma CPU vs RAM kulu, siis tänapäevaste ressursside juures on mälu säästmine ja Query Store'i analüütika kombinatsioon turvalisem valik enamiku töökoormuste puhul.
Kokkuvõte Kui kasutuses on SQL Server 2016 või uuem ning Query Store on sisse lülitatud, on soovitatav konfigureerida ka optimize for ad hoc workloads = 1. See tagab puhtama mälukasutuse ilma, et me kaotaksime diagnostilist infot päringute jõudluse kohta. Seadistus on eriti kriitiline süsteemides, kus on palju dünaamilist SQL-i.