„Parameter Sniffing“ problem

Team building
jun 7, 2017

Ako često pišete SQL upite ili „stored procedure“ (u daljem tekstu SP), verovatno ćete primetiti da izvršavanje istih SP-a je nekad brže, a nekad sporije. Razlika u periodu izvršavanja se takođe može desiti ako pozivate SP iz različitih okruženja, npr SQL Server Management Studio-a ili iz neke vaše aplikacije koja je klijent. Razlog ovakvog ponašanje je takozvani „parameter sniffing“. Ne znam kako bih ovo preveo na srpski jezik, a da ne zvuči neobično, tako da ćemo u daljem tekstu nastaviti da koristimo njegov originalni naziv na engleskom.

 

Šta je „Parameter sniffing“?

SQL Server izvršno okruženje “snifuje” vrednosti parametara pri prvom izvršavanju SP. Vrednosti parametara su “snifovane” za sledeće tipove setova izraza:

  • Stored procedure
  • Upite prosleđene preko sp_executesql SP

SQL server kompajlira SP koristeći “parameter sniffing” pri prvom izvršavanju SP i određeni “plan izvršavanja” snima u “keš”. Sva sledeća izvšavanja iste SP ponovo koriste plan iz “keša” bez obzira da li su prosleđeni različiti parametri.

 

Potencijalni problemi:

Optimalni plan koji je generisan za kombinacuju parametara koja je prosleđena pri prvom izvršavanju, ne mora da bude optimalan za druge kombinacije parametara.

Primer:
Kada je SP izvršena prvi put, parametri koji su prosleđeni zahtevaju da upit pročita veliki broj slogova i optimizator upita odlučuje da je skeniranje tabela ili indeksa (na engleskom “index scan”) je najefikasniji metod za dobijanje slogova.
Kada je ista SP izvršena ponovo sa različitom kombinacijom parametara koji vraća samo jedan određeni slog, SQL Server koristi “keširani” plan izvršavanja i obavlja skeniranje tabele ili indeksa, čak iako je pretraga indeksa (na engleskom index seek) efikasniji metod za ovu kombinaciju parametara.
Treba imati na umu da ovo nije greška u SQL Serveru, to je očekivano ponašanje.

 

Demo:

Kreirajmo sledeću SP:

Parametere sniffing1

Svi parametri su opcioni, najmanje jedan parameter mora biti prosleđen.
Prvo pozivnje SP sa BP brojem. Drugo pozivanje sa ulicom i treće sa ZIP kodom.

Parametere sniffing2

Prvo pozivanje sa ulicom:

Parametere sniffing3

Prvo pozivanje sa ZIP kodom:

Parametere sniffing4

 

Kako izbeći bočne efekte koji su prouzrokovani “parameter sniffing-om”?:

Postoji nekoliko opcija/pravaca kojima se može ići:

  1. Onemogućavanje/isključivanje “parmetre sniffing-a”.
  2.  Rekompajliranje plana izvršavanja svaki put pri izvršavanju SP.
  3. Dekompozicija SQL izraza ili stablo odlujčicanja koje prati svaku specifičnu kombinaciju parametara
  4. Upotreba dinamičkog SQL-a

 

“Parameter sniffing” možemo da onemogućimo na sledeća tri načina:

a) Korišćenjem OPTIMIZE FOR UNKNOWN direktive u upitu:

Parametere sniffing5

b) Maskiranjem parametara lokalnim promenljivama:

Parametere sniffing6

c) korišćenjem „Trace Flag 4136“:

Parametere sniffing7

 

Kada onemogućiti “parameter sniffing”?:

  1. Kada je osrednji plan izvršavanja prihvatljiv.
  2. Kada treba razrešiti veliko vreme izvršavanja potrebno za neke kombinacije.
  3. Cena poboljšavanja je plaćena tako što će biti primenjena na sve kombinacije parametara.

 

U situaciji kada je poznat najvažniji parameter za SP, opcija je da optimizujemo plan izvršavanja za taj specifičan parameter sa klauzolom “OPTION OPTIMIZE FOR” na sledeći način:

Parametere sniffing8

Rekompajliranje plana izvršavanja pri svakom izvršavanju:

Parametere sniffing9

Prednosti:

  • Dobar plan izvršavanja za svaku kombinaciju parametara
  • Lako za održavanje

Mane:

  • Kompajliranje pri svakom izvršavanju, zahteva procesorke resurse pri svakom pozivanju
  • Plan izvršavanja je dobar, ali ne i optimalan.

 

Dekompozicija ili stablo odlučivanja:

 

Parametere sniffing10

Prednosti:

  • Optimalni plan izvršavanja za svaku kombinaciju parameta
  • Koristi postojeći plan izvršavanja pri svakom sledećem izvršavanju i ne koristi dodatne procesorkse resurse

Mane:

  • Problem održavanja – zahteva po jednu SP za svaki plan izvršavanja koji želimo da koristimo
  • Problem održavanja – neodrživo za više od četiri opcija planova izvršavanja.

 

Dinamički SQL

Parametere sniffing11

Prednosti:

  • Optimalni plan izvršavanja za svaku kombinaciju paramet
  • Koristi postojeći plan izvršavanja pri svakom sledećem izvršavanju i ne koristi dodatne procesorkse resurse
  • Može se upravljati velikim brojem opcionih parametara

Mane:

  • Potrebne dodatne sigurnosne „dozvole“
  • Problem održavanja koda – podložno greškama u pisanju upita
  • Podložno injektovanju SQL-a

 

Zaključak:

Ne postoji ultimativno rešenje. U većini slučaja rešenje predstavlja kombinaciju primene dva, a ponekad čak i više od dva pristupa. Ali postoje neke opšte smernice koje bi trebalo pratiti u potrazi za optimalnim rešenjem za konkretan problem:

  • Generalna preporuka je da uvek treba pokušati iskorišćavati keširani plan izvršavanja, kad god je to moguće i izbegavati onemogućavanja “parameter sniffing-a”.
  • Definišite cilj optimizacije.
  • Imajte u vidu zahteve poslovne logike.
  • Imajte u vidu dizajn aplikacije.
  • Koristite “query hints” kada je moguće.
  • Koristite rekompajliranje SP vrlo oprezno.
  • Legitimno je kombinovanje više pristupa.

 

Dušan Stanojević

Senior Developer