Slik leser du en kjøreplan i SQL Server

Før SQL Server kjører en spørring, lager optimalisatoren en kjøreplan (execution plan): oppskriften på hvilke indekser som skal leses, i hvilken rekkefølge tabellene skal kobles, og hvordan. Når en spørring er treg, er det i planen du finner svaret. Planer kan se overveldende ut, men du trenger ikke forstå alle operatorene. Denne guiden viser hvordan du henter fram planen, hvordan du leser den, og hvilke fem–seks ting du nesten alltid bør se etter.

Kort fortalt
  • Bruk den faktiske planen (Ctrl+M i SSMS) når du kan. Den viser både estimerte og faktiske radantall.
  • Les planen fra høyre mot venstre: dataene flyter fra tabellene ut mot resultatet.
  • Se etter store avvik mellom estimerte og faktiske rader, gule advarsler, key lookups og skanninger av store tabeller.
  • Prosentene («Cost») er estimater og kan lyve. Stol mer på faktiske rader og tid.

Estimert og faktisk plan

PlanHvordanHva den viser
EstimertCtrl+L i SSMS, eller SET SHOWPLAN_XML ONPlanen optimalisatoren har valgt, uten å kjøre spørringen. Bare estimater.
FaktiskCtrl+M i SSMS og kjør spørringen, eller SET STATISTICS XML ONSamme plan, pluss faktiske rader, antall kjøringer, tid, minnebruk og ventinger.
Fra cache / Query Storesys.dm_exec_query_plan eller Query StorePlanen som faktisk brukes i produksjon, men bare estimater.

En estimert plan er trygg å hente for en spørring som tar en time eller endrer data. Den faktiske planen er likevel langt mer nyttig, fordi den viser hvor optimalisatoren tok feil. Kombiner gjerne med:

SET STATISTICS IO, TIME ON;
-- kjør spørringen
-- Fanen «Messages» viser logiske lesinger per tabell og CPU-tid

Antall logiske lesinger (8 KB-sider lest fra minnet) er det beste enkeltmålet på hvor mye arbeid en spørring gjør. Det varierer ikke med hvor travel serveren er, og er derfor ypperlig til å sammenligne før og etter en endring.

Hente planen fra produksjon

-- De 10 spørringene som har brukt mest CPU, med plan
SELECT TOP (10)
       qs.total_worker_time / 1000 AS TotalCpuMs,
       qs.execution_count          AS Kjøringer,
       SUBSTRING(t.text, 1, 200)   AS Spørring,
       qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

Klikk på XML-en i resultatet, så åpner SSMS den grafisk. Query Store er ofte enda bedre, fordi den beholder historikken – se guiden om Query Store. Fra SQL Server 2019 kan du også hente siste faktiske plan med sys.dm_exec_query_plan_stats når LAST_QUERY_PLAN_STATS er slått på for databasen.

Slik leser du planen

Hver boks er en operator. Pilene viser dataflyten, og tykkelsen på pilen viser hvor mange rader som går gjennom. Start øverst til høyre og følg dataene mot venstre. Hold musen over en operator (eller trykk F4 for egenskapsvinduet) for detaljer: estimerte og faktiske rader, antall kjøringer, predikater og hvilke kolonner som leses.

Estimerte mot faktiske rader

Det viktigste du kan se etter. Optimalisatoren velger plan ut fra hvor mange rader den tror hver operator gir. Står det «Estimated: 1» og «Actual: 250 000», er planen bygget for en helt annen situasjon enn den virkelige, og valg av join-type, rekkefølge og minnetildeling blir feil. Finn den operatoren lengst til høyre der avviket oppstår. Vanlige årsaker:

  • Utdatert statistikk – se guiden om vedlikehold.
  • Parameter sniffing: planen ble laget for en annen parameterverdi.
  • Funksjoner på kolonner, tabellvariabler eller komplekse uttrykk som optimalisatoren ikke kan estimere.

Seek og scan

OperatorHva den gjørNår er det et problem?
Index SeekSlår direkte opp i indeksenSjelden – men en seek som kjøres 100 000 ganger kan være dyr.
Index Scan / Clustered Index ScanLeser hele indeksen eller tabellenNår tabellen er stor og spørringen bare trenger noen få rader.
Table ScanLeser en heap (tabell uten clustered indeks)Samme som scan. Vurder en clustered indeks.

En scan er ikke alltid feil. Skal spørringen ha det meste av tabellen, er en scan riktigere enn tusenvis av seeks. Men en scan av en tabell på 10 millioner rader for å finne én kunde, betyr at det mangler en indeks eller at predikatet ikke er SARGable. Se guiden om indekser.

Key Lookup

En Key Lookup (eller RID Lookup på en heap) betyr at SQL Server fant radene i en nonclustered indeks, men måtte hente flere kolonner fra tabellen – én rad om gangen. For noen få rader er det greit. For mange tusen rader er det dyrt. Egenskapen Output List viser hvilke kolonner som mangler; legg dem til i indeksen med INCLUDE:

CREATE NONCLUSTERED INDEX IX_Ordre_KundeId
ON dbo.Ordre (KundeId)
INCLUDE (OrdreDato, Totalbeløp);

Join-typer

JoinPasser nårFaresignal
Nested LoopsFå rader i den ytre inndataen, og en indeks på den indre.Ytre side har langt flere faktiske rader enn estimert.
Hash MatchStore, usorterte datamengder.Advarsel om spill til tempdb; stor minnetildeling.
Merge JoinBegge sider er sortert på join-kolonnen.En dyr Sort-operator er lagt inn bare for å muliggjøre merge.
Adaptive JoinVelger mellom hash og nested loops under kjøring (batch mode).Sjelden et problem.

Advarsler

Et gult utropstegn på en operator er alltid verdt å lese. De vanligste:

  • Type conversion in expression … may affect «SeekPlan» – implisitt konvertering, typisk nvarchar-parameter mot varchar-kolonne. Indeksen kan ikke brukes til seek. Se guiden om datatyper.
  • Operator used tempdb to spill data – sorteringen eller hash-operasjonen fikk for lite minne, vanligvis fordi radestimatet var for lavt.
  • Columns with no statistics eller No join predicate – manglende statistikk eller en glemt join-betingelse.
  • Excessive grant – spørringen fikk tildelt langt mer minne enn den brukte, som kan gi kø for andre.

Planen kan også inneholde et grønt forslag om Missing Index. Ta det som et hint, ikke en ordre: forslaget tar ikke hensyn til eksisterende indekser og foreslår ofte brede indekser med mange INCLUDE-kolonner.

Andre operatorer å legge merke til

  • Sort: dyr på store datamengder. Kan ofte unngås med en indeks i riktig rekkefølge.
  • Parallelism (Gather/Repartition Streams): spørringen går parallelt.
  • Table Spool / Index Spool: SQL Server lager en midlertidig tabell underveis. En Eager Index Spool betyr at den bygger en indeks for hver kjøring – lag heller indeksen permanent.
  • Compute Scalar med skalare brukerfunksjoner: kan skjule store kostnader som planen ikke viser.

Verktøy som gjør det enklere

SSMS kan sammenligne to planer (høyreklikk, Compare Showplan), og vise live-fremdrift med Include Live Query Statistics. Gratisverktøyet SentryOne Plan Explorer (nå SolarWinds) og nettstedet Paste the Plan gjør store planer lettere å lese og dele. Se guiden om verktøy.

Vanlige spørsmål

Hvorfor stemmer ikke kostnadsprosentene?

Prosentene er basert på optimalisatorens estimater, også i den faktiske planen. Er estimatene feil, er prosentene feil. En operator som står med 2 prosent kan i virkeligheten bruke det meste av tiden. Fra SSMS 18 vises faktisk tid per operator – bruk den.

Hvorfor er spørringen rask i SSMS, men treg i applikasjonen?

Som regel fordi SSMS og applikasjonen har ulike SET-innstillinger (særlig ARITHABORT) og derfor får hver sin plan i cachen, ofte i kombinasjon med parameter sniffing. Se guiden om trege spørringer.

Må jeg forstå XML-en?

Sjelden. Men noen detaljer, som kompileringsverdiene for parametere (ParameterCompiledValue), finner du lettest i egenskapsvinduet til rotoperatoren (SELECT-boksen helt til venstre) eller i XML-en.

Les også