Totstandkoming estimated en actual query plans Sql Server

Als een query wordt aangeboden aan SQL Server, worden er een aantal stappen uitgevoerd:

Parsing
De query wordt geparsed. Dit houdt in dat wordt bekeken of de query correct geschreven is. De uitkomst hiervan is een parse tree (of query tree of sequence tree). De parse tree representeert de logische stappen die nodig zijn om de query uit te voeren.

DML of DDL
Als de query een Data Manupilation Language (DML) statement is, dan wordt deze geoptimaliseerd. Als het een Data Definition Language query is, dan niet. Dit omdat een DDL altijd statement bevatten die gaan over de DB structuur en er is altijd maar 1 juiste manier om bijv. een tabel aan te maken.

Algebrizer
Als de T-sql een DML statement is en hij is door de parser heen gekomen, dan wordt de parse tree doorgegeven aan de algebrizer. De algebrizer achterhaalt alle namen van de gemoeide objecten, tabellen en kolommen. Hij identificeert ook alle data types, zoals int, varchar(50), etc.. Tevens bepaalt hij ook de aggregates, zoals Group by, max, e.d. Dit proces is belangrijk omdat de query aliases of synoniemen kan bevatten. Als tabellen niet bestaan in de DB, wordt dat geconstateerd tijdens dit proces.

Query optimizer
Als de query door de algebrizer komt, dan resulteert dit in een 'query processor tree'. Deze tree wordt dan doorgegeven aan de query optimizer. De output van de algebrizer bevat ook een hash, een gecodeerde representatie van de query. De optimizer gebruikt deze hash om te kijken of er al een plan voor aanwezig is in de cache. Als er al een plan aanweig is, dan stop het proces en wordt het bestaande plan gebruikt.

De meest belangrijke input voor de query optimizer zijn statistics. Sql Server houdt statistieken bij over indexes en kolommen speciaal voor de optimizer. Door gebruik te maken van de query processor tree en de statistieken stelt de optimizer een plan op waarvan hij denkt dat dat het meest optimaal is. Hij bepaalt dan of de data wordt benaderd d.m.v. indexes, welke soort joins hij gaat gebruiken, speelt met de volgorde van de joins, probeert verschillende indexes uit en meer totdat hij vind dat hij het meest geschikte plan gevonden heeft. Tijdens deze berekeningen wijst de optimizer een nummer toe aan elke stap binnen het plan. Dit nummer representeert de schatting van de benodigde CPU-kracht en disk I/O tijd dat welke stap nodig heeft. Dit nummer is de estimated cost voor de betreffende stap. De totale cost van het plan is de optelling van de cost van elke stap. Het is daarom dan ook een 'cost-based plan'.

De optimizer zal vele plannen genereren en evalueren zal vervolgens het plan nemen met de minste 'kosten'. Dit betekend: waarvoor de minste CPU en I/O resources nodig zijn.

Als een zeer eenvoudige query moet worden uitgevoerd, bijvoorbeeld een Select zonder aggregeates, dan wordt er een triviaal plan opgesteld in plaats van veel tijd te spenderen aan het berekenen en evalueren van plannen.

Als de optimizer bij het execution plan komt, dan  wordt het estimation plan gemaakt en opgeslagen in de plan cache.

Storage engine
Nadat de optimizer zijn werk heeft gedaan, d.w.z. een exection plan heeft gegenereerd of uit de cache heeft gehaald, komt de storage engine om de hoek kijken. De storage engine voert normaal gesproken de query uit volgens het execution plan. Er zijn uitzonderingen dat de storage engine afwijkt van het plan. Deze uitzonderingen zijn:

- Als Sql server vaststelt dat het plan in aanmerking komt voor parallel execution;
- Als de statistieken de gebruikt zijn viir het plan te oud zijn of zijn veranderd ten opzichte wanneer het plan is gemaakt. Dit kan bijvoorbeeld voorkomen als het plan uit de cache komt;
- Processen of objecten  in de query, zoals toevoegen van data in een tijdelijke tabel, resulteert in het recompilen van het execution plan.

Herbruikbaarheid execution plans
Omdat het genereren van plannen resources kost, probeert Sql Server zoveel mogelijk de plannen uit de plan cache te halen. Het is dus van belang om de queries zo te schrijven dat de plannen hiervan opnieuw gebruikt kunnen worden voor een betere performance. Om dit te bereiken is het het beste om geparameteriseerde queries te gebruiken of Stored Procedurs (SP's). Als bijvoorbeeld waardes hard in de query staan gedefinieerd, dan is de kleinste verandering van een query een cache mismatch veroorzaken, waardoor het execution plan niet uit de cache kan worden gehaald omdat hij deze niet kan vinden.

Execution plans blijven niet voor atijd in het geheugen staan. Ze worden langzaam verouderd door een 'leeftijdsformule' die de kosten van een plan vermenigvuldigd met het aantal keren dat het is gebruikt. Dus een plan dat geschat 20 kost en 5 keer is gebruikt, heeft een leeftijd van 100. Een intern proces, de lazywriter, draait om alle soorten caches leeg te maken, inclusief de plan cache. Dit proces draait periodiek en scant alle objecten in de cache en vermindert de leeftijd met 1. Als een plan aan de onderstaande criteria voldoet, dan wordt het plan uit de cache gehaald:

- Er is meer geheugen nodig door het systeem;
- De leeftijd van het plan is 0 geworden;
- Het plan wordt niet meer gebruikt door een bestaande verbinding.

Overig
Om te testen hoe lang het duurt om een execution plan op te stellen, kan het handig zijn om de plan cache handmatig leeg te gooien. Dit kan met het statement:

dbcc freeproccache

let wel: dit commando gooit de plannen weg van alle databases op de server!