SqlServer.no

Hvorfor er spørringen treg? Feilsøk ytelse i SQL Server

Av Sandberg Software AS Publisert

«Databasen er treg» er en av de vanligste klagene i IT-avdelinger. Noen ganger er det serveren, men oftest er det én eller noen få spørringer som gjør mye mer arbeid enn nødvendig. Denne guiden viser en systematisk fremgangsmåte for å finne ut hva som er tregt og hvorfor – med kjøreplaner, Query Store og ventestatistikk – og går gjennom de vanligste årsakene og hvordan du løser dem.

Kort fortalt
  • Finn ut om problemet er én spørring eller hele serveren.
  • Query Store viser hvilke spørringer som bruker mest ressurser, og om planen har endret seg.
  • Les den faktiske kjøreplanen, og se etter store avvik mellom estimerte og faktiske rader.
  • De vanligste årsakene: manglende indeks, ikke-SARGable betingelser, implisitt konvertering, parameter sniffing, utdatert statistikk og blokkering.
  • Mål før og etter med SET STATISTICS IO, TIME ON.

Steg 1: Én spørring eller hele serveren?

Start med å avgrense. Er det én skjerm eller én rapport som er treg, eller er alt tregt? Skjedde det plutselig, eller har det gradvis blitt verre?

  • Plutselig, én spørring: Ofte en endret kjøreplan – parameter sniffing eller ny statistikk.
  • Gradvis, én spørring: Datamengden har vokst, og en plan som var god for 10 000 rader er dårlig for 10 millioner.
  • Plutselig, alt: Blokkering, en ny tung jobb, problemer med lagring eller nettverk, eller minnepress.
  • Periodisk: Se etter planlagte jobber – backup, CHECKDB, indeksvedlikehold, ETL.

Hva skjer akkurat nå?

SELECT r.session_id,
       r.status,
       r.wait_type,
       r.wait_time,
       r.blocking_session_id,
       r.cpu_time,
       r.logical_reads,
       r.total_elapsed_time / 1000 AS SekunderBrukt,
       DB_NAME(r.database_id) AS DatabaseNavn,
       t.text AS Spørring
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

Den gratis prosedyren sp_WhoIsActive av Adam Machanic gir det samme og mye mer – inkludert kjøreplan og blokkeringskjeder – og er verdt å installere på alle servere.

Hva venter SQL Server på?

Ventestatistikk viser hva SQL Server har brukt tid på å vente på siden oppstart. Det gir et bilde av flaskehalsen:

VentetypePeker typisk mot
PAGEIOLATCH_*Lesing av datasider fra disk – for lite minne, manglende indekser eller treg lagring.
WRITELOGSkriving til transaksjonsloggen – treg loggdisk eller mange små transaksjoner.
LCK_M_*Blokkering – se guiden om blokkering.
CXPACKET / CXCONSUMERParallellisme. Normalt i seg selv, men mye kan tyde på dårlige planer eller feil MAXDOP.
SOS_SCHEDULER_YIELDCPU-press.
RESOURCE_SEMAPHORESpørringer venter på minne til sortering og hashing.
ASYNC_NETWORK_IOKlienten leser resultatet tregt – ofte en applikasjon som henter for mange rader og behandler dem én og én.

Steg 2: Finn de dyre spørringene med Query Store

Query Store er SQL Servers «ferdskriver» for spørringer: den lagrer spørringstekst, kjøreplaner og kjøretidsstatistikk over tid, i selve databasen. Den er på som standard for nye databaser fra SQL Server 2022, og kan slås på i eldre versjoner fra 2016:

ALTER DATABASE Salg SET QUERY_STORE = ON
(OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);

I SSMS finner du rapportene under databasen, Query Store. De mest nyttige er Top Resource Consuming Queries og Regressed Queries – den siste viser spørringer som har blitt tregere fordi planen har endret seg. Herfra kan du også tvinge en tidligere, god plan, noe som er en rask nødløsning mens du finner den egentlige årsaken.

-- De 10 spørringene som har brukt mest CPU totalt
SELECT TOP (10)
       q.query_id,
       qt.query_sql_text,
       SUM(rs.count_executions)                        AS AntallKjøringer,
       SUM(rs.avg_cpu_time * rs.count_executions) / 1000 AS TotalCpuMs,
       AVG(rs.avg_duration) / 1000                     AS SnittVarighetMs
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY TotalCpuMs DESC;

Husk å se på totalt forbruk, ikke bare den tregeste enkeltkjøringen. En spørring på 50 millisekunder som kjøres 2 millioner ganger i døgnet, belaster serveren mer enn en rapport på 30 sekunder som kjøres én gang.

Steg 3: Les kjøreplanen

Kjøreplanen viser hvordan SQL Server faktisk henter dataene. I SSMS slår du på Include Actual Execution Plan (Ctrl+M) før du kjører spørringen. Kombiner med I/O- og tidsstatistikk:

SET STATISTICS IO, TIME ON;

SELECT k.Navn, SUM(o.Totalbeløp) AS Omsetning
FROM dbo.Kunde AS k
JOIN dbo.Ordre AS o ON o.KundeId = k.KundeId
WHERE o.OrdreDato >= '2026-01-01'
GROUP BY k.Navn;

Logical reads i meldingsfanen er det beste målet på hvor mye arbeid spørringen gjør, og det varierer ikke med hva som tilfeldigvis ligger i cache. Bruk det til å sammenligne før og etter en endring.

Se etter dette i planen:

  • Store avvik mellom estimerte og faktiske rader. Estimerer optimalisereren 1 rad og får 500 000, er alle valgene etter det steget basert på feil premisser. Årsaken er ofte utdatert statistikk, tabellvariabler, funksjoner eller parameter sniffing.
  • Scan av store tabeller der du forventet seek.
  • Key lookups med høyt antall kjøringer – vurder en dekkende indeks.
  • Gule advarselstrekanter: implisitt konvertering, manglende statistikk, eller sortering og hashing som renner over til tempdb (spill).
  • Tykke piler: Mange rader som flyttes mellom operatorer, gjerne langt flere enn resultatet til slutt inneholder.

De vanligste årsakene

1. Manglende eller feil indeks

Den klassiske årsaken. Se guiden om indekser for hvordan du lager dekkende indekser med riktig kolonnerekkefølge.

2. Ikke-SARGable betingelser og implisitt konvertering

Funksjoner rundt kolonner i WHERE (YEAR(Dato) = 2026, LTRIM(Kode) = 'X') og ulike datatyper mellom kolonne og parameter hindrer indekssøk. Skriv om betingelsen slik at kolonnen står alene, og la parametertypene matche kolonnene.

3. Parameter sniffing

Når en lagret prosedyre eller parameterisert spørring kompileres, lager SQL Server en plan tilpasset parameterverdiene ved første kjøring, og gjenbruker planen. Det er vanligvis bra. Men hvis første kjøring var for en kunde med 3 ordrer, og neste er for en kunde med 3 millioner, kan planen være katastrofal – eller omvendt. Symptomet er en spørring som «plutselig» blir treg, og som blir rask igjen når planen kastes ut av cachen.

Mulige løsninger, fra enklest til mest målrettet:

-- Ny plan ved hver kjøring: enkelt, men koster kompilering hver gang
SELECT ... FROM dbo.Ordre WHERE KundeId = @KundeId
OPTION (RECOMPILE);

-- Optimaliser for en typisk verdi, eller for en «gjennomsnittlig» fordeling
SELECT ... FROM dbo.Ordre WHERE KundeId = @KundeId
OPTION (OPTIMIZE FOR (@KundeId UNKNOWN));

SQL Server 2022 har i tillegg Parameter Sensitive Plan optimization, som automatisk kan holde flere planer for samme spørring avhengig av parameterverdiene. I Query Store kan du også tvinge en plan som fungerer godt for alle verdier.

4. Utdatert statistikk

Store tabeller der mange rader er lagt til siden sist statistikken ble oppdatert – for eksempel nye datoer som ikke finnes i histogrammet ennå – gir dårlige estimater. Oppdater statistikken og se om planen endres. Les mer i guiden om vedlikehold.

5. Blokkering

Spørringen er rask, men venter på låser holdt av andre. Se blocking_session_id og ventetyper som starter på LCK_M_. Løsningen ligger ofte i korte transaksjoner og radversjonering – se guiden om blokkering og deadlocks.

6. For mye data til applikasjonen

SELECT * uten filter, og ORM-er som laster hele objektgrafer, sender mye mer data enn brukeren trenger. Et annet klassisk mønster er N+1-problemet: én spørring for å hente 100 ordrer, deretter 100 nye spørringer for å hente linjene til hver ordre. Hver spørring er rask, men summen er treg. Hent bare kolonnene og radene du trenger, og hent relaterte data i samme spørring.

7. Skalare funksjoner og tabellvariabler

Skalare brukerdefinerte funksjoner kjøres én gang per rad og hindret tidligere parallellisme. SQL Server 2019 kan «inline» mange slike funksjoner automatisk, men ikke alle. Tabellvariabler hadde tidligere fast estimat på 1 rad; SQL Server 2019 forbedret dette med utsatt kompilering. Er du på en eldre versjon eller kompatibilitetsnivå, kan en midlertidig tabell (#temp) gi bedre planer.

8. Kompatibilitetsnivå

Mange av de nevnte forbedringene (Intelligent Query Processing) slås bare på når databasens kompatibilitetsnivå er høyt nok. En database som er flyttet til SQL Server 2022 men fortsatt har kompatibilitetsnivå 110 (2012), får ikke fordelene. Hev nivået kontrollert, med Query Store påslått, slik at du kan tvinge gamle planer hvis noe blir tregere.

SELECT name, compatibility_level FROM sys.databases;

ALTER DATABASE Salg SET COMPATIBILITY_LEVEL = 160;  -- SQL Server 2022

Serverinnstillinger som påvirker ytelse

  • max server memory: For lavt gir mye disklesing; for høyt kan presse operativsystemet.
  • MAXDOP og cost threshold for parallelism: Standardverdien 5 for cost threshold gjør at mange små spørringer går parallelt unødvendig. 50 er et vanlig startpunkt.
  • tempdb: Flere datafiler av lik størrelse reduserer konkurranse om allokeringssider.
  • Strømprofil: Windows-profilen Balanced kan senke CPU-frekvensen. Bruk High performance på databaseservere.

Se installasjonsguiden for anbefalte startverdier.

Vanlige spørsmål

Spørringen er rask i SSMS, men treg fra applikasjonen. Hvorfor?

Nesten alltid fordi SSMS og applikasjonen får ulike kjøreplaner. SSMS har som standard SET ARITHABORT ON, mens ADO.NET har OFF, og ulike innstillinger gir separate planer i cachen. Applikasjonens plan kan være kompilert for andre parameterverdier (parameter sniffing). Finn applikasjonens faktiske plan i Query Store i stedet for å teste i SSMS.

Hjelper det å tømme cachen med DBCC FREEPROCCACHE?

Det kan få en spørring med dårlig plan til å bli rask igjen, men det kaster alle planer på hele serveren, og alle må kompileres på nytt. Det er en nødløsning, ikke en løsning – og på et travelt system kan det gi en CPU-topp. Fjern heller én bestemt plan, eller tving en god plan med Query Store.

Bør jeg bruke NOLOCK for å gjøre spørringer raskere?

Nei, som hovedregel ikke. NOLOCK kan gi rader som leses to ganger, rader som hoppes over og data fra transaksjoner som senere rulles tilbake. Er blokkering problemet, er Read Committed Snapshot Isolation en mye tryggere løsning.

Les også