Parameter sniffing – Dårligt for vores performance?

Det er vigtigt hele tiden at optimere vores queries så SQL Serveren performer optimalt, men på trods af vores sorte bælte i at skrive SQL Query kan vi godt nogle gange komme ud for at SQL Serverens Query optimizer vælger en executionplan vi ikke lige havde forventet. En af de ting der kan drille os er parameter sniffing.

For at forstå hvad det er og hvordan det løses skal vi lige omkring SQL Query optimizeren og forstå hvad der sker i motorrummet når SQL Serveren finder den bedste executionplan til os. SQL Serverens query optimizer gør et utrolig godt arbejde med at finde den bedste plan til os. Det er en omfattende proces som er for omfattende at komme ind på her, men tro mig, det er smart og SQL Query optimizeren giver oftest en rigtig god executionplan til os.

Processen med at lave en executionplan er en dyr process for SQL Serveren, i den forstand at det tager forholdsvis mange ressourcer at finde den bedste plan. Derfor er det også at foretrække at SQL Serveren kan genbruge planen, når vi nu eksekverer det samme SQL Query igen.

Her er et eksempel som er meget forsimplet og i virkeligheden et SQL Query som serveren vil betragte som simpelt og som vil få en triviel plan.. men se venligst bort fra det.

Disse 2 queries ser umiddelbart ens ud, men for SQL Serveren er de forskellige fordi vores filter er forskellig (LastName):

Hvis vi spørger efter ‘Henderson’ vil vi få en helt 3. plan, og sådan vil det fortsætte for hver forespørgsel hvor LastName er anderledes end de forrige.

Vi kan hjælpe serveren godt på vej ved at lave en storedprocedure og benytte parametre. Vi laver feks. denne storedprocedure:

Nu skal vi bare angive LastName når vi eksekverer den stored procedure som dette:

Nu kan SQL Serveren nøjes med en executionplan og optimizeren skal derved ikke generere en ny executionplan hver gang vi vil forespørge et nyt LastName – Det er ofte rigtig godt for vores performance og det ønsker vi.

Men…

Data i langt de fleste tabeller er relativt ensartet fordelt på værdier, men nogle gange kommer vi ud for at tabeller har en markant overvægt af en værdi og det er i disse tabeller at vi kan opleve parameter sniffing fænomenet.

For at illustrere har jeg et eksempel: Jeg har en tabel med bynavne (et dumt eksempel, men det viser det det skal). Tabellen ser sådan her ud:

Vi har en nonclustered index på Navn kolonnen:

Der er følgende Stored procedure:

Vi kører nu følgende SQL Query:

Og får denne executionplan:

Da Brabrand er i langt de fleste rækker i tabellen, er det ikke uventet at se en table scan. Det har optimizeren vurderet er hurtigste måde at få resultatsættet retur.

Nu kører vi dette SQL Query:

og får denne executionplan:

Begge SQL Query laver samme plan, det er typisk det vi ønsker. Men vi kender data i vores tabel og ved at når vi spørger på Horsens, som der kun findes 1 af i tabellen, ville vi helst se et index Seek da det må være hurtigste måde at få datasættet retur.

Vi prøver at ændre rækkefølgen, først rydder vi vores plancache (Hvor vores executionplans er gemt) 

GØR DET IKKE I PRODUKTION MILJØ!

Nu kører vi vores SQL Query igen med Horsens først:

Og får denne executionplan, og vi ser at vores index bliver benyttet:

Perfekt! Nu kører vi SQL Query og spørger efter Brabrand … kan du gætte hvilken executionplan vi får? .. den samme som før, et index seek som vi ved ikke er den optimale måde at returnere datasættet på, når vi spørger efter Brabrand… Hvad sker der?

Dette er et eksempel på Parameter Sniffing

Fænomenet opstår når vi bruger storedprocedures og forespørger tabeller med så uens data som i dette eksempel. SQL Query optimizeren laver en executionplan ud fra den første værdi vi giver i vores SQL Query når vi eksekverer vores storedprocedure. Dette kan vi få bekræftet hvis vi kigger i XML af vores executionplan (Parametercompilevalue

<ParameterList>
<ColumnReference Column=”@Navn” ParameterDataType=”nvarchar(40)” ~
  ParameterCompiledValue=”N’Brabrand'” ParameterRuntimeValue=”N’Brabrand'” />
</ParameterList>

Planen bliver optimeret ud fra den værdi og er derfor ikke optimal når vi forespørger andre værdier i tabellen.

Hvad kan vi så gøre ved det?

Ingenting? Nej, vi ønsker oftest at gøre noget ved det og vi har 2 forskellige muligheder:

Recompile

Du kan vælge at benytte et Query hint og simpelthen tvinge SQL Serveren til at generere en ny plan hver gang Query kører, så er du sikker på at du får den optimale plan hver gang.

Selvom det løser problemet med parameter sniffing så skal du passe på, for er det et SQL Query der kører ofte, eller du har måske nogle stykker du har løst med recompile hintet, så går det ud over SQL Serverens generelle performance, da den skal stå og recompile en masse planer hele tiden. Men hvis dit SQL Query bruges sjældent er dette en acceptabel løsning.

Optimize for

En anden løsning du kan vælge er at benytte Query hintet Optimize for og angive Unknown. Det vil gøre at Query optimizeren, når den skal lave planen, benytter gennemsnitsværdier som den får fra statistics på tabellen, det giver en gennemsnitslig executionplan som kan være ok.

Hvis du ved hvordan din data bruges og feks. ved at i 99% af tilfældene så forespørges der på Horsens, så har du muligheden for at optimere dit Query til den værdi. Hvilket selvfølgelig vil sige at når du kører dit SQL Query og forespørger Horsens så har du den optimale plan, men spørger du efter andre værdier så er planen ikke optimal, men hvis du gør det sjældent er dette en rigtig god løsning.

Nu er der bare tilbage at beslutte om du vil tage det “performance hit” der er at recompile planen hver gang, eller om den rigtige løsning for dig er at optimere planen for unknown, eller en værdi.

Tak fordi du læste med, skulle du sidde med udfordringer på dine SQL Servere eller til udvikling til SQL så sidder jeg og mine kollegaer i Unit IT klar til at hjælpe. Vi afholder også kurser i både SQL Server tuning, Daglig administration samt SQL Query performance tuning se dem under menupunktet kurser.
Ring gerne til os på tlf: 88 333 333 til en uforpligtende snak.