Forstå forespørgselsplaner

  • 11 minutter

Det er vigtigt at forstå, hvordan databaseoptimatorer fungerer, før du dykker ned i detaljer om udførelsesplanen. SQL Server bruger en omkostningsbaseret forespørgselsoptimering, som beregner omkostningerne for flere mulige planer baseret på statistikker, der er baseret på de kolonner, der bruges, og de potentielle indekser for hver handling i forespørgselsplanen. Disse oplysninger hjælper optimeringsfunktionen med at bestemme de samlede omkostninger for hver plan. Komplekse forespørgsler kan have tusindvis af mulige udførelsesplaner, men optimeringsprogrammet evaluerer ikke hver enkelt. I stedet bruges heuristik til at identificere planer, der sandsynligvis vil fungere godt, og derefter vælger den laveste omkostningsplan ud fra dem, der evalueres.

Da forespørgselsoptimeringsprogrammet er omkostningsbaseret, er det afgørende at give det nøjagtige input til beslutningstagningen. SQL Server er afhængig af statistikker til at spore distributionen af data i kolonner og indeks, og disse statistikker skal holdes opdateret for at undgå at generere ikke-optimale udførelsesplaner. Selvom SQL Server automatisk opdaterer sine statistikker, når data ændres i en tabel, kan det være nødvendigt at foretage hyppigere opdateringer af data, der ændrer sig hurtigt. Optimeringen tager højde for mange faktorer, når du opretter en plan, herunder databasens kompatibilitetsniveau, rækkeestimater baseret på statistikker og tilgængelige indeks.

Når en bruger sender en forespørgsel til databaseprogrammet, sker følgende proces:

  1. Forespørgslen fortolkes for at få korrekt syntaks, og hvis den er korrekt, oprettes der et fortolkningstræ af databaseobjekter.
  2. Fortolkningstræet inputs derefter til en komponent i databaseprogrammet, der kaldes Algebrizer for binding. Dette trin validerer, at kolonner og objekter i forespørgslen findes, og identificerer de datatyper, der behandles. Outputtet er et forespørgselsbehandlertræ, der fungerer som input til næste trin.
  3. Forespørgselsoptimering er CPU-krævende, så databaseprogrammet cachelagrer udførelsesplaner i et særligt hukommelsesområde kaldet plancachen. Hvis der allerede findes en plan for forespørgslen, hentes den fra cachen. Hver forespørgsel i cachen har en hashværdi, der er genereret på baggrund af T-SQL i forespørgslen, der kaldes query_hash. Programmet genererer en query_hash for den aktuelle forespørgsel og kontrollerer, om der er match i plancachen.
  4. Hvis der ikke findes en plan, bruger Forespørgselsoptimering den omkostningsbaserede optimering til at generere flere indstillinger for udførelsesplan baseret på statistikker om de kolonner, tabeller og indekser, der bruges i forespørgslen. Outputtet er en plan for udførelse af forespørgsler.
  5. Forespørgslen udføres ved hjælp af en udførelsesplan fra plancachen eller en ny plan, der blev oprettet i det forrige trin. Outputtet er resultaterne af din forespørgsel.

Bemærk

Du kan få mere at vide om, hvordan forespørgselsprocessoren fungerer, i Vejledning til forespørgselsbehandlingsarkitektur

Lad os se på et eksempel. Overvej følgende forespørgsel:

SELECT orderdate,
        AVG(salesAmount)
FROM FactResellerSales
WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;

I dette eksempel kontrollerer SQL Server, om kolonnerne OrderDate, ShipDate og SalesAmount findes i tabellen FactResellerSales . Hvis disse kolonner findes, genererer SQL Server en hashværdi for forespørgslen og undersøger plancachen for en tilsvarende hashværdi. Hvis der findes en matchende hashværdi, forsøger programmet at genbruge planen. Hvis der ikke findes en tilsvarende hashværdi, undersøger SQL Server de tilgængelige statistikker for kolonnerne OrderDate og ShipDate .

Den WHERE delsætning, der refererer til kolonnen ShipDate , kaldes prædikatet i denne forespørgsel. Hvis der er et ikke-inkluderet indeks, der indeholder kolonnen ShipDate , vil SQL Server sandsynligvis inkludere det i planen, forudsat at omkostningerne er lavere end hentning af data fra det grupperede indeks. Optimeringsfunktionen vælger derefter den laveste omkostningsplan blandt de tilgængelige indstillinger og udfører forespørgslen.

Forespørgselsplaner kombinerer en række relationsoperatorer for at hente data og hente oplysninger, f.eks. anslået antal rækker. Et andet element i udførelsesplanen er den hukommelse, der kræves til handlinger som f.eks. sammenføjning eller sortering af data, også kaldet hukommelsestilskuddet. Hukommelsestilskuddet fremhæver vigtigheden af statistikker. Hvis SQL Server estimerer, at en operator returnerer 10.000.000 rækker, når den faktisk returnerer 100, tildeles der en større hukommelsestildelt til forespørgslen. Et for stort hukommelsestilskud kan medføre to problemer. Først kan forespørgslen vente RESOURCE_SEMAPHORE , hvilket angiver, at den venter på, at SQL Server tildeler en stor mængde hukommelse. SQL Server venter som standard 25 gange omkostningerne for forespørgslen (i sekunder), før den udføres, op til 24 timer. Hvis der for det andet ikke er tilstrækkelig hukommelse, når forespørgslen udføres, overføres den til tempdb, hvilket er langsommere end at arbejde i hukommelsen.

Udførelsesplanen gemmer også andre metadata om forespørgslen, f.eks. niveauet for databasekompatibilitet, graden af parallelitet og de angivne parametre, hvis forespørgslen er parameteriseret.

Forespørgselsplaner kan vises enten i en grafisk repræsentation eller i et tekstbaseret format. Tekstbaserede indstillinger aktiveres med SET-kommandoer og gælder kun for den aktuelle forbindelse. Disse planer kan ses overalt, hvor du kan køre T-SQL-forespørgsler.

De fleste DBA'er foretrækker grafiske planer, fordi de giver dig mulighed for at se planen som helhed, herunder planens form . Der er flere måder at få vist og gemme grafiske forespørgselsplaner på. Det mest almindelige værktøj til dette formål er SQL Server Management Studio. Derudover er der tredjepartsværktøjer, der understøtter visning af grafiske udførelsesplaner.

Der er tre forskellige typer udførelsesplaner.

Anslået udførelsesplan

Denne type udførelsesplan genereres af forespørgselsoptimeringsprogrammet. Metadataene og størrelsen af tildelingen af forespørgselshukommelsen er baseret på estimater fra de statistikker, der findes i databasen på tidspunktet for kompilering af forespørgsler. Hvis du vil se en tekstbaseret anslået plan, skal du køre kommandoen SET SHOWPLAN_ALL ON , før du udfører forespørgslen. Når du kører forespørgslen, kan du se trinnene i udførelsesplanen, men forespørgslen udføres ikke, og du kan ikke se nogen resultater. Indstillingen SET forbliver i kraft, indtil du har slået den fra.

Faktisk udførelsesplan

Denne type plan er den samme som den anslåede plan. Den indeholder dog også udførelseskonteksten for forespørgslen. Denne kontekst indeholder det anslåede og faktiske rækkeantal, eventuelle kørselsadvarsler, den faktiske grad af parallelitet (antal anvendte processorer) og de forløbne og CPU-gange, der bruges under udførelsen. Hvis du vil se en tekstbaseret faktisk plan, skal du køre kommandoen SET STATISTICS PROFILE ON , før du udfører forespørgslen. Forespørgslen udføres, og du får både planen og resultaterne.

Live Query-statistik

Denne indstilling for visning af planen kombinerer de anslåede og faktiske planer i en animeret plan, der viser udførelsesstatus via operatorerne. Det opdateres hvert sekund og viser det faktiske antal rækker, der flyder gennem operatorerne. En anden fordel ved Live Query Statistics er, at den viser afleveringen fra operatoren til operatoren, hvilket kan være nyttigt i forbindelse med fejlfinding af problemer med ydeevnen. Da denne type plan er animeret, er den kun tilgængelig som en grafisk plan.