Dynamisk SQL och vikten av statistik

Som SQL server konsult så möter jag ofta kunder som har problem med prestanda i vissa stored procedures. Ibland fungerar de bra och ibland så tar samma procedur flera timmar att exekvera utan att mängden data som behandlas i proceduren har förändrats nämnvärt.

Några av de vanligaste orsakerna bakom dessa problem (utan inbördes rangordning) är:

– Fragmenterade index
– Icke uppdaterad statistik
– Dålig spridning på värden som man söker på vilket leder till “parameter sniffing”
– Låsningar
– Dynamisk SQL

Detta inlägg fokuserar på Dynamisk SQL i kombination med icke uppdaterad statistik. Eftersom Dynamisk SQL kod i en procedure kompileras varje gång så kommer det att bli en ny exekveringsplan (för den dynamiska koden) vid varje anrop till proceduren. Det fungerar bra så länge inte det är en komplex fråga som tar lång tid att kompilera eller att statistiken är missvisande. Låt säga att du söker efter ett datum på en kolumn som är indexerad, och att det fanns poster som matchade det datumet när statistiken uppdaterades. I det fallet så kan SQL ta fram en exekveringsplan som är effektiv för ditt urval, men om datumet ligger utanför intervallet av datum som fanns när statistiken uppdaterades så utgår SQL från att det bara är 1 post och optimerar exekveringsplanen efter det. Vad som händer i det senare fallet är att du får en exekveringsplan baserad på antagandet att det bara är 1 post och i verkligheten så kan det handla om miljontals rader vilket kräver en helt annan exekveringsplan. Detta har vi sett flertalet exempel på med frågor som brukar ta någon minut tar helt plötsligt timmar. De olika exekveringsplanerna kan hämtas ifrån DMV:erna och jämföras. I den “felaktiga” exekveringsplanen kan man se att “estimated number of rows” är 1 och i den effektiva exekveringsplanen så är det uppskattade antalet närmare verkligheten.  

Det finns tre lösningar på detta problem:
– Skriva om koden för att få bort den dynamiska sql:en, eller lägga in WITH(OPTIMIZE FOR UNKNOWN)
– Uppdatera statistiken manuellt inför varje exekvering, eller automatiskt efter att ny data har lagts in eller med tätare intervall.
– Lägga in en plan guide för frågan.

De olika alternativen har sina fördelar och nackdelar, men i vissa fall är det bara updatering av statistiken som är möjlig, exempelvis vid inköpta system.

Har ni problem med SQL server prestanda? Våra SQL konsulter hjälper gärna till.

/Håkan Winther