Performance Tuning

Uge 10: Plan Caching

I denne uge vil jeg, som lovet i sidste uge, forsøge at hjælpe dig godt i gang med at forstå Plan Caching og de afledte effekter den mekanisme har på SQL serverens performance. Fra sidste uge kan du måske huske at enhver logisk query mod SQL serveren kræver en fysisk Execution Plan. Denne Execution Plan bliver gemt i Plan Cache for fremtidigt brug.

Ad-hoc SQL queries

Hver gang man eksekverer en ad-hoc SQL query mod SQL serveren, vil SQL serveren lave en unik Execution Plan for hver unik query. Men hvad er en unik query?

SQL serveren beregner en hash værdi af hele SQL querien (inkl. evt. hard-kodede parametre og disses værdier), og bruger denne hash værdi til at finde eksisterende Execution Plans i Plan Cache. Hvis en eksisterende plan eksisterer, vil denne blive brugt, ellers vil en ny plan blive udarbejdet og denne bliver så gemt i Plan Cache. Et eksempel:

select * from Sales.SalesOrderHeader
where CustomerID = 11000

GO

select * from Sales.SalesOrderHeader
where CustomerID = 30052

GO

select * from Sales.SalesOrderHeader
where CustomerID = 11223

GO

De 3 ovenstående queries vil hver generere en ny Execution Plan, fordi der er hard-kodede parametre. Og heraf bliver hash værdien for de 3 queries unik. Den afledte effekt er at der nu eksisterer 3 execution planer til næsten identiske queries. Dette problem bliver kaldt Plan Cache Pollution.

Plan Cachen bliver forurenet med nye Execution Plans som er svære at genbruge og der går værdifuld hukommelse til spilde som ellers kunne bruges på andre processser i SQL serveren. Målet er at have en så høj re-use count på Execution Plans fra Plan Cache.

Plan Stability

En måde at optimere genbrugen af Execution Plans fra Plan Cache, er at lave parameter værdier i SQL queries (med @parameternavn) eller, og endnu bedre, lave stored procedures som skal returnere data. På den måde kan SQL serveren genbruge Execution Plans fra Plan Cache meget effektivt.

Lad mig tage samme eksempel query som fra uge 8 som var den der udførte et Bookmark Lookup:

Som kendt fra uge 8, så giver Bookmark Lookup kun mening hvis man skal bruge få rækker fra sin tabel. Hvis man kommer over Tipping Point, så er det mere cost effektivt at lave hele tabel- eller clustered index scan. Men hvis SQL serveren genbruger en execution plan fra Plan Cache, så bliver denne beslutning slet ikke overvejet længere – SQL serveren genbruger blindt de gemte execution plans, også selv om performance bliver utrolig langsom.

Plan caching

Fra ovenstående eksempel, kan vi se at SQL serveren blindt genbruger en cached execution plan med Bookmark Lookup. Prøv at se forskellen på Estimated Number of Rows og Actual Number of Rows. SQL serveren genbruger en execution plan hvor der var 1 række i resultatsættet, mens der reelt i denne query er 1.499 rækker.

Årsan til dette er at der ikke er Plan Stability. Baseret på det estimerede antal rækker, anvender SQL serveren Bookmark Lookup i stedet for det mere effektive Table/Clustered Index Scan (hvis Tipping Point er nået). Dette er et ret udbredt og generelt problem for mange installationer af SQL serveren og disses performance problemer.

Men hvordan kan man så undgå disse Bookmark Lookups og genbrug af execution plans fra Plan Cache? Det kan du måske allerede gætte: Ved at lave et covering Non-Clustered Index som passer med den query der eksekveres. På den måde får du altid den bedste execution plan for din query og uanset hvordan du baserer dine parametre, vil SQL serveren altid svare hurtigt og effektivt. Men pas på med at lave for mange indexes for at tilgodese alle queries. Det er ret så vigtigt!

Opsummering – Plan Caching

En kort introduktion til Plan Cache og hvilke fordele og ulemper dette kan medføre på SQL serveren. Det er et tve-ægget sværd at arbejde med – på den ene side er Plan Cache et meget effektivt værktøj, da man kan genbruge allerede eksisterende execution plans, på den anden side et meget farligt værktøj, da man kan komme til at genbruge allerede eksisterende execution plans…

Husk at skrive dig på maillisten nedenfor, så du ikke misser næste udgave af denne blogserie. Jeg lover dig kun at sende en mail, når der er nyt i denne serie.

Få besked om næste indlæg

Skriv dig gerne op til at modtage en mail, ved næste indlæg. Det kan du gøre nedenfor.



Marketing stuff

Our emails contain marketing stuff, so we need to give you some fine quality fine print: brianbonk will use the information you provide on this form to email you with updates and marketing. You can change your mind at any time by clicking the unsubscribe link in the footer of any email you receive from us, or by contacting us at help@brianbonk.dk. We use Mailchimp as our marketing platform. By checking the box to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing, and that we may process your information in accordance with these terms.

Følg mig på Instagram

Leave a Reply

Your email address will not be published. Required fields are marked *

en_USEnglish