A mostani rendszeren, amin dolgozom, találtunk egy tárolt eljárást, ami elég furcsán működött. Az volt a tünet, hogy működött, működött, aztán lassabban működött, még lassabban, míg nagyjából minden második hívása timeoutra futott.
Nézelődtünk mindenfele, hogy mi lehet a gond, néztünk táblákat, indexeket, benchmarkoltunk. Mondjuk egy dolgot nem nagyon néztünk meg: a tároltat honnan és hogyan hívjuk meg.
Aztán feltűnt, hogy amikor simán újra létrehoztuk a tároltat, megint jó volt egy ideig. Erre már csak utánanéztünk, hogy honnan és hogyan hívjuk meg. Kiderült, hogy öt különféle helyről öt különféle módon hívjuk meg.
Felderültünk: parameter sniffing.
Magyarul: egy sokféle paraméterrel ellátott, össze-vissza hívott annyira általános tárolt eljárás, hogy szinte már nem is jó semmire.
Amikor először lefutott a tárolt eljárás, akkor elkészült hozzá az execution plan. Mondjuk ezt éppen a customer oldalról hívtuk, megadtuk az oda tartozó paramétereket és ez alapján készült el a végrehajtási terv. Ha legközelebb nem a customer oldalról hívjuk, más paramétereket használunk (de a customer paraméterei is benne vannak, csak éppen null-lal), akkor észreveszi, hogy „ezt a tároltat már futtattam, itt egy végrehajtási terv”, és az a terv alapján hajtja végre. Ami persze most nem lesz hatékony, mert az első végrehajtással mondhatni másra optimalizáltuk.
Első kézenfekvő megoldás lehetne a tárolt eljárást recompile-lal hívni, vagyis hogy minden alkalommal hozzon létre új végrehajtási tervet és az alapján működjön. A tervek, amiket létrehoz és később használni szeretne, alapesetben bekerülnek a cache-be, de ebben az esetben simán eldobjuk azokat. Talán érthető, hogy ez nem annyira jó megoldás.
Második kézenfekvő megoldás lehetne a hintelés. Használjuk az optimize for hintet, hogy megmondjuk, melyik paraméter szerint optimalizálja a végrehajtási tervet. Mivel ennek a tároltnak volt 12 bemenő paramétere, ez egyrészt nehéz lenne, másrészt megint csak azt érnénk el, hogy egy kombinációra jól menne, egy másikra meg nem lenne túl gyors.
Ezért a harmadik megoldást választottuk: megnéztük, hogy valóban csak így egyben tudjuk elképzelni ezt a tárolt eljárást? A válasz nagyon gyorsan az lett, hogy nem.
Kiderült, hogy öt helyről hívjuk, nagyon más paraméterekkel. Ezért öt felé fogjuk vágni, mindegyik esetben csak az arra a speciális lekérdezésre tartozó paraméterekkel. Így még optimizálni is sokkal könnyebb.
Először kettészedtük: a legtöbbet futtatott paraméterek mentén és a „többi” mentén. Vagyis az általános maradt még négy helyen, egy helyen pedig egy másolata fut, amiből kiheréltünk neki nem kellő paramétereket és kicsit optimalizáltunk. Az eredmény megdöbbentő: elmúlt minden gondunk.
Ez mutatja az eredeti tároltat (OrderSearch) a szétválasztás előtt és után. A sárga részt nézzétek, ahol bekarikáztam, ott tettük ki a másikat, hogy egy helyről másikat hívunk.
Itt máshogyan látszik, hogy mikor a teher egy részét levettük erről a tárolról, mennyire megkönnyebbült a szerver.
Itt pedig a másolat tároltat látjátok, amit csak azért teszek ide, hogy látszódjon: a kettő messze nem veszi igénybe annyira a szervert, mint az eredeti egyedül.
Már azzal hatalmasat nyertünk, hogy egy tároltat lemásoltunk és külön-külön execution plant kapott a két változat a kétféle hívás miatt. De ezzel még nincs vége, az eredeti még mindig négy helyen szerepel, különféle paraméter hívásokkal, különféle célokra. Ezt is még szétszedjük, aztán mind az ötöt még egyszer megnézzük és optimalizáljuk az adott feladatra.
