Evaluer forespørgselspræstation med udførelsesplaner og DMV'er

Når en forespørgsel kører langsommere end forventet, er det første skridt at forstå, hvordan databasemotoren udfører den. Eksekveringsplaner viser dig de præcise operatorer, dataadgangsmetoder og ressourceomkostninger, som optimeringsværktøjet valgte til en forespørgsel. Dynamiske management views (DMV'er) supplerer dette ved at eksponere runtime-performance data på tværs af alle forespørgsler i databasen, så du kan finde de dyreste, før du kaster dig ud i en enkelt plan.

Læs udførelsesplaner

En eksekveringsplan er det sæt instruktioner, som forespørgselsoptimereren producerer for at hente og behandle data. Den definerer, hvilke tabeller der skal tilgås først, om indekser eller scanningstabeller skal bruges, og hvordan man sammenføjer, filtrerer, sorterer og aggregerer resultater. Optimereren vurderer flere kandidatplaner og vælger den med den laveste estimerede pris.

Der findes to typer udførelsesplaner:

• Estimeret eksekveringsplan: Genereret uden at køre forespørgslen. Den viser de planlagte operatorer og estimerede rækkeantal baseret på statistikker. Brug estimerede planer til hurtig analyse uden at påvirke databasen.
• Faktisk eksekveringsplan: Fanges under forespørgselskørsel. Den inkluderer den estimerede plan plus reelle rækketal, faktiske udførelsestider, hukommelsestildelinger og advarsler. Den faktiske plan afslører uoverensstemmelser mellem, hvad optimereren forventede, og hvad der faktisk skete.

For at vise en estimeret plan, kør SET SHOWPLAN_XML ON før forespørgslen eller vælg Vis estimeret udførelsesplan i SQL Server Management Studio (SSMS). For at indfange en faktisk plan, kør SET STATISTICS XML ON eller vælg Inkluder Faktisk Udførelsesplan i SSMS, før forespørgslen udføres.

Selvom de estimerede og faktiske planer ligner hinanden, er planens driftstidsmålinger afgørende for at diagnosticere præstationsproblemer. For eksempel, hvis det estimerede rækkeantal for en tabelscanning er 100, men det faktiske antal rækker er 10.000, kan det indikere forældede statistikker, der fører til et dårligt planvalg. Optimereren sammensætter planen baseret på statistik første gang, den støder på en forespørgsel. Hvis disse statistikker ikke afspejler den nuværende datafordeling, kan planen klare sig dårligt.

Identificer almindelige problemer i udførelsesplaner

Udførelsesplanerne læses fra venstre mod højre, oppefra og ned. De første operatorer tilgår basistabellerne, og den sidste operator producerer forespørgselsresultatet. Hold øje med disse almindelige problemer:

Operatørtyper fortæller dig, hvordan lokomotivet tilgår data. Der findes mange operatortyper, som hver repræsenterer en forskellig metode til at hente eller behandle data. For eksempel repræsenterer en Index Seek-operator en meget effektiv metode, der målretter specifikke rækker ved hjælp af indeksnøgler. En Table Scan eller Index Scan-operator repræsenterer derimod en mindre effektiv metode, der læser hver række. Hvis du ser en scanning på et stort bord, har du sandsynligvis brug for et indeks. For eksempel, hvis e-handelsapplikationen forespørger efter dato, og planen viser en Clustered Index Scan i tabellen Orders , kan tilføjelsen af et ikke-clustered indeks i kolonnen OrderDate ændre denne scanning til en seek. Bemærk, at ikke alle scanninger er dårlige. Hvis en tabel er lille, eller søgebetingelsen returnerer de fleste rækker i en tabel, kan en scanning være den mest effektive adgangsmetode. Overvej altid konteksten af forespørgslen og størrelsen af dataene. Kend dine data og brug eksekveringsplaner for at bekræfte, om adgangsmetoden giver mening.

Estimerede versus faktiske rækkeantal viser, om optimeringsværktøjets antagelser stemmer overens med virkeligheden. Optimereren baserer sin plan på statistik, metadata, der beskriver fordelingen og tætheden af data i dine tabeller. Hvis disse statistikker er forældede, divergerer de estimerede og de faktiske rækketællinger. Når optimereren undervurderer rækkeantal, kan den vælge en indlejret loop-join (som behandler én række ad gangen fra den inderste tabel i en join), når en hash-join (som bygger en hashtabel i hukommelsen til hurtige opslag) ville være hurtigere, eller allokere for lidt hukommelse til en sorteringsoperation. Statistikker kan blive forældede efter betydelige dataændringer, så opdatering af statistikker med UPDATE STATISTICS eller aktivering af automatiske statistikopdateringer kan hjælpe optimereren med at træffe bedre beslutninger.

Key Lookup-operatorer vises, når motoren finder rækker gennem et ikke-klynget indeks, men har brug for ekstra kolonner fra det klyngede indeks. For hver matchende række udfører motoren en ekstra rundtur til det klyngede indeks for at hente disse kolonner. Hvis filteret returnerer mange rækker, løber de ekstra opslag hurtigt op. For eksempel, hvis e-handelsapplikationen filtrerer ordrer efter CustomerID , men også vælger OrderDate, TotalAmount, og ShippingAddress, og det ikke-klyngede indeks på CustomerID ikke inkluderer disse kolonner, viser planen et Key Lookup for hver matchende ordre. Du kan eliminere Key Lookups ved at tilføje de manglende kolonner som inkluderede kolonner i indekset. Husk, at inkluderede kolonner øger indeksstørrelsen, hvilket kan sænke skrivninger, så vej læseydelsen op mod skriveomkostningerne.

Tykke pile mellem operatorerne repræsenterer antallet af rækker, der flyder mellem dem. En uventet tyk pil tidligt i planen (læst fra venstre mod højre, oppefra og ned) betyder ofte, at et manglende filter eller indeks lader for mange rækker passere.

Manglende indeksforslag vises som grøn markeret tekst øverst i den grafiske eksekveringsplan i SSMS. Når optimeringsværktøjet opdager, at et indeks kan reducere omkostningerne ved en forespørgsel betydeligt, fremstår en anbefaling direkte i planen. Højreklik på forslaget og vælg Manglende indeksdetaljer for at generere en CREATE INDEX erklæring, du kan gennemgå og køre. Disse forslag er nogle af de nemmeste gevinster, du kan få ved at læse en eksekveringsplan.

Advarsler vises som en gul trekant med et udråbstegn (⚠) på den berørte operatør. Hver advarsel peger på en optimeringsmulighed. Almindelige advarsler inkluderer:

• Manglende statistikker: Optimereren kunne ikke finde statistik for en kolonne, så den gættede på rækkeantal i stedet for at bruge den faktiske datafordeling. For at løse dette problem skal du oprette statistik over de kolonner, der bruges i dine forespørgsler, eller opdatere eksisterende statistikker, hvis de er forældede.
• Overdreven hukommelsesfordeling: Forespørgslen anmodede om mere hukommelse, end den havde brug for, hvilket spildte ressourcer, som andre forespørgsler kunne bruge. Dette problem opstår ofte, når optimereren overvurderer rækketælling. Opdatering af statistik eller omskrivning af forespørgslen til at filtrere rækker tidligere kan hjælpe med at reducere hukommelsestildelinger.
• No join-prædikat: To tabeller joines uden en ordentlig betingelse, hvilket giver et kartesisk produkt, der returnerer alle mulige rækkekombinationer. Tjek din forespørgsel for en manglende eller forkert ON klausul.
• Implicit konvertering: En datatype-mismatch tvinger motoren til at konvertere værdier under kørsel, hvilket kan omdanne en indekssøgning til en scanning. For eksempel, hvis en WHERE klausul sammenligner en nvarchar parameter med en varchar kolonne, konverterer motoren hver række i kolonnen til før nvarchar sammenligning. For at rette implicitte konverteringer, match datatyperne i dine forespørgselsparametre med kolonnedefinitionerne.
• Sorterings- eller hashspil: En sorterings- eller hashoperation løb tør for sin tildelte hukommelse og spildte mellemliggende resultater til tempdb. Disse operationer er den næstmest almindelige driver for høj CPU efter scanninger. Hvis du ser en spildadvarsel, har optimereren sandsynligvis undervurderet rækkeantallet og bedt om for lidt hukommelse. At løbe UPDATE STATISTICS for at opdatere tabellens statistikker eller omskrive forespørgslen for at reducere antallet af rækker før sorteringen kan ofte eliminere spild.

Udførelsesplaner er et kraftfuldt værktøj til at forstå forespørgselspræstation. De viser dig præcis, hvordan motoren udfører en forespørgsel, og hvor flaskehalsene er. Ved at lære at læse udførelsesplaner effektivt kan du hurtigt identificere og løse performanceproblemer i dine databaseforespørgsler.

Forespørg DMV'er efter køretidsdata

DMV'er eksponerer realtids- og akkumulerede ydelsesdata fra databasemotoren. Azure SQL Database kræver VIEW DATABASE STATE tilladelse til at forespørge dem. Mens udførelsesplaner viser, hvordan en enkelt forespørgsel kører, viser DMV'er dig, hvad der sker på tværs af alle forespørgsler, hvilket hjælper dig med at finde de dyreste først.

Find de dyreste forespørgsler

CPU-tid, logiske læsninger og eksekveringsantal er de mest almindelige målepunkter til at identificere dyre forespørgsler. Høj CPU-tid eller logiske læsninger indikerer, at en forespørgsel er ressourcekrævende, mens et højt antal udførelser betyder, at selv en moderat dyr forespørgsel kan have stor indflydelse på den samlede ydeevne. Start med at gennemgå de bedste forespørgsler efter gennemsnitlig CPU-tid eller logiske læsninger for at finde kandidater til optimering.

sys.dm_exec_query_stats returnerer aggregerede ydelsesstatistikker for cachede forespørgselsplaner. Tilslut den sys.dm_exec_sql_text for at se forespørgselsteksten og sys.dm_exec_query_plan hente eksekveringsplanen.

Følgende forespørgsel finder de 10 øverste forespørgsler målt på gennemsnitlig CPU-tid:

SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_cpu_time DESC;

Dette script hjælper dig med at identificere, hvilke forespørgsler der fortjener din opmærksomhed. Høje avg_logical_reads i forhold til resultatmængden peger ofte på manglende indeks eller ineffektive planer. Vær dog forsigtig, når du tolker disse resultater. En forespørgsel med høj gennemsnitlig CPU-tid, der kun kører én gang om dagen, kan være mindre betydningsfuld end en moderat forespørgsel, der kører tusindvis af gange i timen. Overvej altid både gennemsnitsomkostningerne og antallet af udførelser, når du prioriterer. Du kan også bestille for avg_logical_reads at finde forespørgsler, der er tunge på I/O, hvilket ofte indikerer manglende indekser eller ineffektive adgangsmetoder.

Tjek aktuelt udførende forespørgsler

Mens den forrige forespørgsel viser dig de dyreste historiske forespørgsler i plancachen og sys.dm_exec_requests giver dig et øjebliksbillede af hver forespørgsel, der aktuelt kører. Den indeholder kolonner for CPU-tid, læsninger, skrivninger, ventetype, ventetid og blokering af sessions-ID. Brug denne visning til at opdage aktive forespørgsler, der bruger for mange ressourcer eller sidder fast i at vente på låse. Dette synspunkt er et af de vigtigste DMV'er til realtids overvågning og fejlfinding.

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    t.text AS query_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id > 50
ORDER BY r.cpu_time DESC;

Denne forespørgsel filtrerer systemsessioner (sessions-ID'er 1-50) og ordrer ud efter CPU-tid. Du kan også bestille forbi logical_reads for at finde I/O-tunge forespørgsler. Kolonnerne wait_type og wait_time hjælper dig med at identificere, om en forespørgsel venter på lås, I/O eller andre ressourcer.

Opdag manglende indekser

Tidligere så vi, hvordan udførelsesplaner kan vise manglende indeksforslag til en enkelt forespørgsel. De manglende indeks-DMV'er giver dig et bredere overblik over, hvilke indeks optimizeren ville bruge på tværs af alle forespørgsler, hvis de eksisterede. Disse visninger er en god måde at finde optimeringsmuligheder, der påvirker flere forespørgsler. sys.dm_db_missing_index_details viser tabellen, ligheds- og ulighedskolonnerne samt inkluderede kolonner. sys.dm_db_missing_index_group_stats giver et forbedringsmål, der estimerer omkostningsreduktionen.

SELECT
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans) AS improvement_measure
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

Denne forespørgsel beregner en improvement_measure for hver manglende indeksanbefaling, som er et produkt af den gennemsnitlige omkostning af forespørgsler, der ville drage fordel af indekset, den gennemsnitlige procentvise forbedring og antallet af gange disse forespørgsler er blevet udført. Sortering efter dette mål hjælper dig med at prioritere, hvilke manglende indekser du skal oprette først. Husk dog, at disse resultater blot er anbefalinger baseret på de forespørgsler, der aktuelt findes i plancachen. Gennemgå altid de foreslåede indekskolonner og test deres indvirkning på både forespørgselspræstation og skriveoverhead, før du tilføjer dem til produktionen.

Overvåg aktive sessioner og venteopgaver

sys.dm_exec_sessions giver dig information om alle autentificerede sessioner, inklusive logintid, værtsnavn, programnavn og kumulativ CPU og læsninger. Kombiner det med sys.dm_os_waiting_tasks for at se, hvilke opgaver der venter, og hvilke ressourcer de venter på. Disse synspunkter bliver essentielle, når du diagnosticerer blokering og ressourcekonkurrence i en senere enhed.

Sæt det hele sammen

Udførelsesplaner og DMV'er giver dig et komplet billede af forespørgselsadfærden. Start med DMV'er for at identificere de dyreste forespørgsler. Derefter dyk ned i deres udførelsesplaner for at forstå, hvorfor de er dyre. Er det et manglende indeks, der forårsager en scanning? Forældede statistikker, der forårsager fejl i rækkeestimater? En Key Lookup, du kan eliminere? Denne systematiske tilgang, fra systemdækkende overblik til individuel forespørgselsanalyse, er den mest effektive måde at finde og løse performance-flaskehalse på.

Vigtige takeaways

Eksekveringsplaner afslører optimeringsværktøjets strategi for en forespørgsel, og faktiske planer inkluderer runtime-metrikker, der afslører uoverensstemmelser mellem estimerede og faktiske rækketællinger. Når du læser en plan, skal du fokusere på operatørtyper (seek vs. scan), estimater af rækketælling, advarsler og Key Lookup-operatører. DMV'er leverer systemomfattende ydelsesdata: brug sys.dm_exec_query_stats til at finde de dyreste forespørgsler, sys.dm_exec_requests der kører aktuelt, og de manglende indeks-DMV'er til optimeringsmuligheder. Start bredt med DMV'er for at identificere, hvor de største problemer er, og dyk derefter ned i individuelle udførelsesplaner for at forstå hvorfor.