Dela via

Diagnostisera och felsöka hög CPU-användning i Azure SQL Database

  • Artikel

Gäller för:Azure SQL Database

Azure SQL Database innehåller inbyggda verktyg för att identifiera orsakerna till hög CPU-användning och optimera arbetsbelastningsprestanda. Du kan använda dessa verktyg för att felsöka hög CPU-användning när den inträffar eller reaktivt när incidenten har slutförts. Du kan också aktivera automatisk justering för att proaktivt minska CPU-användningen över tid för databasen. Den här artikeln lär dig att diagnostisera och felsöka hög CPU med inbyggda verktyg i Azure SQL Database och förklarar när du ska lägga till CPU-resurser.

Förstå antalet virtuella kärnor

Det är bra att förstå antalet virtuella kärnor (virtuella kärnor) som är tillgängliga för databasen när du diagnostiserar en hög CPU-incident. En virtuell kärna motsvarar en logisk PROCESSOR. Antalet virtuella kärnor hjälper dig att förstå de CPU-resurser som är tillgängliga för databasen.

Identifiera antalet virtuella kärnor i Azure-portalen

Du kan snabbt identifiera antalet virtuella kärnor för en databas i Azure-portalen om du använder en vCore-baserad tjänstnivå med den etablerade beräkningsnivån. I det här fallet innehåller prisnivån som anges för databasen på sidan Översikt antalet virtuella kärnor. En databass prisnivå kan till exempel vara "Generell användning: Standardserie (Gen5), 16 virtuella kärnor".

För databaser på den serverlösa beräkningsnivån motsvarar antalet virtuella kärnor alltid den maximala inställningen för virtuell kärna för databasen. Antalet virtuella kärnor visas på den prisnivå som anges för databasen på sidan Översikt . En databass prisnivå kan till exempel vara "Generell användning: Serverlös, standardserie (Gen5), 16 virtuella kärnor".

Om du använder en databas under den DTU-baserade inköpsmodellen måste du använda Transact-SQL för att köra frågor mot databasens antal virtuella kärnor.

Identifiera antal virtuella kärnor med Transact-SQL

Du kan identifiera det aktuella antalet virtuella kärnor för valfri databas med Transact-SQL. Du kan köra Transact-SQL mot Azure SQL Database med SQL Server Management Studio (SSMS), Azure Data Studio eller Azure Portal-frågeredigeraren.

Anslut till databasen och kör följande fråga:

SELECT 
    COUNT(*) as vCores
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE';
GO

Identifiera orsakerna till hög CPU

Du kan mäta och analysera CPU-användning med hjälp av Azure-portalen, interaktiva verktyg för Query Store i SSMS och Transact-SQL-frågor i SSMS och Azure Data Studio.

Azure-portalen och Query Store visar körningsstatistik, till exempel CPU-mått, för slutförda frågor. Om du upplever en aktuell hög CPU-incident som kan orsakas av en eller flera pågående långvariga frågor kan du identifiera frågor som körs med Transact-SQL.

Vanliga orsaker till ny och ovanlig hög CPU-användning är:

  • Nya frågor i arbetsbelastningen som använder en stor mängd PROCESSOR.
  • En ökning av frekvensen för att regelbundet köra frågor.
  • Regression av frågeplan, inklusive regression på grund av problem med parameterkänslig plan (PSP), vilket resulterar i att en eller flera frågor förbrukar mer CPU.
  • En betydande ökning av kompilering eller omkompilering av frågeplaner.
  • Databaser där frågor använder överdriven parallellitet.

För att förstå vad som orsakar din höga CPU-incident kan du identifiera när hög CPU-användning sker mot databasen och de viktigaste frågorna med cpu vid den tidpunkten.

Undersöka:

Kommentar

Azure SQL Database kräver beräkningsresurser för att implementera viktiga tjänstfunktioner som hög tillgänglighet och haveriberedskap, säkerhetskopiering och återställning av databaser, övervakning, Query Store, automatisk justering osv. Användningen av dessa beräkningsresurser kan vara särskilt märkbar för databaser med låga antal virtuella kärnor eller databaser i täta elastiska pooler. Läs mer i Resurshantering i Azure SQL Database.

Använd Azure-portalen för att spåra olika CPU-mått, inklusive procentandelen tillgänglig PROCESSOR som används av databasen över tid. Azure-portalen kombinerar CPU-mått med information från databasens Query Store, som gör att du kan identifiera vilka frågor som förbrukade CPU i databasen vid en viss tidpunkt.

Följ de här stegen för att hitta mått för CPU-procent.

  1. Gå till databasen i Azure-portalen.
  2. Under Intelligenta prestanda på den vänstra menyn väljer du Query Performance Insight.

Standardvyn för Query Performance Insight visar 24 timmars data. CPU-användning visas som en procentandel av den totala tillgängliga PROCESSORn som används för databasen.

De fem vanligaste frågorna som körs under den perioden visas i lodräta staplar ovanför diagrammet för CPU-användning. Välj ett tidsintervall i diagrammet eller använd menyn Anpassa för att utforska specifika tidsperioder. Du kan också öka antalet frågor som visas.

Screenshot shows Query Performance Insight in the Azure portal.

Välj varje fråge-ID med hög CPU för att öppna information för frågan. Information inkluderar frågetext tillsammans med prestandahistorik för frågan. Kontrollera om PROCESSORn har ökat för frågan nyligen.

Anteckna fråge-ID:t för att undersöka frågeplanen ytterligare med hjälp av Query Store i följande avsnitt.

Granska frågeplaner för de vanligaste frågorna som identifieras i Azure-portalen

Följ de här stegen för att använda ett fråge-ID i SSMS interaktiva Query Store-verktyg för att undersöka frågans körningsplan över tid.

  1. Öppna SSMS.
  2. Anslut till din Azure SQL Database i Object Explorer.
  3. Expandera databasnoden i Object Explorer.
  4. Expandera mappen Query Store .
  5. Öppna fönstret Spårade frågor .
  6. Ange fråge-ID:t i rutan Spårningsfråga längst upp till vänster på skärmen och tryck på Retur.
  7. Om det behövs väljer du Konfigurera för att justera tidsintervallet så att det matchar tiden då hög CPU-användning inträffade.

Sidan visar körningsplaner och relaterade mått för frågan under de senaste 24 timmarna.

Identifiera frågor som körs med Transact-SQL

Med Transact-SQL kan du identifiera frågor som körs med cpu-tid som de har använt hittills. Du kan också använda Transact-SQL för att köra frågor mot den senaste CPU-användningen i databasen, de vanligaste frågorna efter CPU och frågor som kompilerades oftast.

Du kan köra frågor mot CPU-mått med SQL Server Management Studio (SSMS), Azure Data Studio eller Azure Portal-frågeredigeraren. När du använder SSMS eller Azure Data Studio öppnar du ett nytt frågefönster och ansluter det till databasen (inte databasen master ).

Hitta frågor som körs med cpu-användnings- och körningsplaner genom att köra följande fråga. CPU-tid returneras i millisekunder.

SELECT
    req.session_id,
    req.status,
    req.start_time,
    req.cpu_time AS 'cpu_time_ms',
    req.logical_reads,
    req.dop,
    s.login_name,
    s.host_name,
    s.program_name,
    object_name(st.objectid,st.dbid) 'ObjectName',
    REPLACE (REPLACE (SUBSTRING (st.text,(req.statement_start_offset/2) + 1,
        ((CASE req.statement_end_offset    WHEN -1    THEN DATALENGTH(st.text) 
        ELSE req.statement_end_offset END - req.statement_start_offset)/2) + 1),
        CHAR(10), ' '), CHAR(13), ' ') AS statement_text,
    qp.query_plan,
    qsx.query_plan as query_plan_with_in_flight_statistics
FROM sys.dm_exec_requests as req  
JOIN sys.dm_exec_sessions as s on req.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as st
OUTER APPLY sys.dm_exec_query_plan(req.plan_handle) as qp
OUTER APPLY sys.dm_exec_query_statistics_xml(req.session_id) as qsx
ORDER BY req.cpu_time desc;
GO

Den här frågan returnerar två kopior av körningsplanen. Kolumnen query_plan innehåller körningsplanen från sys.dm_exec_query_plan. Den här versionen av frågeplanen innehåller endast uppskattningar av radantal och innehåller ingen körningsstatistik.

Om kolumnen query_plan_with_in_flight_statistics returnerar en körningsplan innehåller den här planen mer information. Kolumnen query_plan_with_in_flight_statistics returnerar data från sys.dm_exec_query_statistics_xml, som innehåller körningsstatistik för "under flygning", till exempel det faktiska antalet rader som hittills returnerats av en fråga som körs.

Granska cpu-användningsstatistik för den senaste timmen

Följande fråga mot sys.dm_db_resource_stats returnerar den genomsnittliga CPU-användningen över 15 sekunders intervall för ungefär den senaste timmen.

SELECT
    end_time,
    avg_cpu_percent,
    avg_instance_cpu_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC; 
GO

Det är viktigt att inte bara fokusera på avg_cpu_percent kolumnen. Kolumnen avg_instance_cpu_percent innehåller PROCESSOR som används av både användare och interna arbetsbelastningar. Om avg_instance_cpu_percent är nära 100 % är CPU-resurserna mättade. I det här fallet bör du felsöka hög CPU om appens dataflöde är otillräckligt eller om frågesvarstiden är hög.

Läs mer i Resurshantering i Azure SQL Database.

Läs exemplen i sys.dm_db_resource_stats för fler frågor.

Fråga de senaste 15 frågorna efter CPU-användning

Query Store spårar körningsstatistik, inklusive CPU-användning, för frågor. Följande fråga returnerar de 15 vanligaste frågorna som har körts under de senaste 2 timmarna, sorterade efter CPU-användning. CPU-tid returneras i millisekunder.

WITH AggregatedCPU AS 
    (SELECT
        q.query_hash, 
        SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_ms, 
        SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_ms, 
        MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms, 
        MAX(max_logical_io_reads) max_logical_reads, 
        COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans, 
        COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids, 
        SUM(CASE WHEN rs.execution_type_desc='Aborted' THEN count_executions ELSE 0 END) AS aborted_execution_count, 
        SUM(CASE WHEN rs.execution_type_desc='Regular' THEN count_executions ELSE 0 END) AS regular_execution_count, 
        SUM(CASE WHEN rs.execution_type_desc='Exception' THEN count_executions ELSE 0 END) AS exception_execution_count, 
        SUM(count_executions) AS total_executions, 
        MIN(qt.query_sql_text) AS sampled_query_text
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
    JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
    JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id
    JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_id
    WHERE 
            rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception') AND 
        rsi.start_time>=DATEADD(HOUR, -2, GETUTCDATE())
     GROUP BY q.query_hash), 
OrderedCPU AS 
    (SELECT *, 
    ROW_NUMBER() OVER (ORDER BY total_cpu_ms DESC, query_hash ASC) AS RN
    FROM AggregatedCPU)
SELECT *
FROM OrderedCPU AS OD
WHERE OD.RN<=15
ORDER BY total_cpu_ms DESC;
GO

Den här frågan grupperas efter ett hashvärde för frågan. Om du hittar ett högt värde i number_of_distinct_query_ids kolumnen undersöker du om en fråga som körs ofta inte är korrekt parametriserad. Icke-parametriserade frågor kan kompileras på varje körning, vilket förbrukar betydande CPU och påverkar prestanda för Query Store.

Om du vill veta mer om en enskild fråga noterar du frågehashen och använder den för att identifiera CPU-användningen och frågeplanen för en viss frågehash.

Fråga de vanligaste kompilerade frågorna efter frågehash

Att kompilera en frågeplan är en processorintensiv process. Azure SQL Database-cacheplaner i minnet för återanvändning. Vissa frågor kan kompileras ofta om de inte parametriseras eller om RECOMPILE-tips tvingar fram omkompilering.

Query Store spårar hur många gånger frågor kompileras. Kör följande fråga för att identifiera de 20 vanligaste frågorna i Query Store efter kompileringsantal, tillsammans med det genomsnittliga antalet kompileringar per minut:

SELECT TOP (20)
    query_hash,
    MIN(initial_compile_start_time) as initial_compile_start_time,
    MAX(last_compile_start_time) as last_compile_start_time,
    CASE WHEN DATEDIFF(mi,MIN(initial_compile_start_time), MAX(last_compile_start_time)) > 0
        THEN 1.* SUM(count_compiles) / DATEDIFF(mi,MIN(initial_compile_start_time), 
            MAX(last_compile_start_time)) 
        ELSE 0 
        END as avg_compiles_minute,
    SUM(count_compiles) as count_compiles
FROM sys.query_store_query AS q
GROUP BY query_hash
ORDER BY count_compiles DESC;
GO

Om du vill veta mer om en enskild fråga noterar du frågehashen och använder den för att identifiera CPU-användningen och frågeplanen för en viss frågehash.

Identifiera CPU-användning och frågeplan för en viss frågehash

Kör följande fråga för att hitta det enskilda fråge-ID:t, frågetexten och frågekörningsplanerna för en viss query_hash. CPU-tid returneras i millisekunder.

Ersätt värdet för variabeln @query_hash med en giltig query_hash för din arbetsbelastning.

declare @query_hash binary(8);

SET @query_hash = 0x6557BE7936AA2E91;

with query_ids as (
    SELECT
        q.query_hash,
        q.query_id,
        p.query_plan_hash,
        SUM(qrs.count_executions) * AVG(qrs.avg_cpu_time)/1000. as total_cpu_time_ms,
        SUM(qrs.count_executions) AS sum_executions,
        AVG(qrs.avg_cpu_time)/1000. AS avg_cpu_time_ms
    FROM sys.query_store_query q
    JOIN sys.query_store_plan p on q.query_id=p.query_id
    JOIN sys.query_store_runtime_stats qrs on p.plan_id = qrs.plan_id
    WHERE q.query_hash = @query_hash
    GROUP BY q.query_id, q.query_hash, p.query_plan_hash)
SELECT qid.*,
    qt.query_sql_text,
    p.count_compiles,
    TRY_CAST(p.query_plan as XML) as query_plan
FROM query_ids as qid
JOIN sys.query_store_query AS q ON qid.query_id=q.query_id
JOIN sys.query_store_query_text AS qt on q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON qid.query_id=p.query_id and qid.query_plan_hash=p.query_plan_hash
ORDER BY total_cpu_time_ms DESC;
GO

Den här frågan returnerar en rad för varje variant av en körningsplan för query_hash hela historiken för frågearkivet. Resultaten sorteras efter total CPU-tid.

Använda interaktiva Query Store-verktyg för att spåra historisk CPU-användning

Om du föredrar att använda grafiska verktyg följer du de här stegen för att använda interaktiva Query Store-verktyg i SSMS.

  1. Öppna SSMS och anslut till databasen i Object Explorer.
  2. Expandera databasnoden i Object Explorer
  3. Expandera mappen Query Store .
  4. Öppna fönstret Övergripande resursförbrukning .

Den totala CPU-tiden för databasen under den senaste månaden i millisekunder visas i den nedre vänstra delen av fönstret. I standardvyn aggregeras CPU-tiden per dag.

Screenshot shows the Overall Resource Consumption view of Query Store in SSMS.

Välj Konfigurera längst upp till höger i fönstret för att välja en annan tidsperiod. Du kan också ändra aggregeringsenheten. Du kan till exempel välja att se data för ett visst datumintervall och aggregera data per timme.

Använda interaktiva Query Store-verktyg för att identifiera de vanligaste frågorna efter CPU-tid

Välj en stapel i diagrammet för att öka detaljnivån och se frågor som körs under en viss tidsperiod. Fönstret Mest resurskrävande frågor öppnas. Alternativt kan du öppna de vanligaste resurskrävande frågorna från noden Query Store under databasen i Object Explorer direkt.

Screenshot shows the Top Resource Consuming Queries pane for Query Store in S S M S.

I standardvyn visar fönstret Mest resurskrävande frågor frågor efter varaktighet (ms). Varaktigheten kan ibland vara lägre än CPU-tid: frågor med parallellitet kan använda mycket mer CPU-tid än den totala varaktigheten. Varaktigheten kan också vara högre än CPU-tid om väntetiderna är betydande. Om du vill se frågor efter CPU-tid väljer du listrutan Mått längst upp till vänster i fönstret och väljer CPU-tid(ms).

Varje stapel i den övre vänstra kvadranten representerar en fråga. Välj ett fält för att se information om frågan. Den övre högra kvadranten på skärmen visar hur många körningsplaner som finns i Query Store för den frågan och mappar dem enligt när de kördes och hur mycket av det valda måttet som användes. Välj varje plan-ID för att styra vilken frågekörningsplan som visas på den nedre halvan av skärmen.

Kommentar

En guide för att tolka Query Store-vyer och formerna som visas i vyn Främsta resurskonsumenter finns i Metodtips med Query Store

Minska CPU-användningen

En del av felsökningen bör omfatta att lära dig mer om de frågor som identifierades i föregående avsnitt. Du kan minska CPU-användningen genom att justera index, ändra programmönster, justera frågor och justera CPU-relaterade inställningar för databasen.

Överväg följande strategier i det här avsnittet.

Minska CPU-användningen med automatisk indexjustering

Effektiv indexjustering minskar CPU-användningen för många frågor. Optimerade index minskar de logiska och fysiska läsningarna för en fråga, vilket ofta resulterar i att frågan behöver utföra mindre arbete.

Azure SQL Database erbjuder automatisk indexhantering för arbetsbelastningar på primära repliker. Automatisk indexhantering använder maskininlärning för att övervaka din arbetsbelastning och optimera diskbaserade diskbaserade icke-illustrerade index för databasen.

Granska prestandarekommendationer, inklusive indexrekommendationer, i Azure-portalen. Du kan använda dessa rekommendationer manuellt eller aktivera alternativet skapa index för automatisk justering för att skapa och verifiera prestanda för nya index i databasen.

Minska CPU-användningen med automatisk plankorrigering (force plan)

En annan vanlig orsak till höga CPU-incidenter är regression av val av körningsplan. Azure SQL Database erbjuder alternativet för automatisk justering av force plan för att identifiera regressioner i frågekörningsplaner i arbetsbelastningar på primära repliker. Med den här funktionen för automatisk justering aktiverad testar Azure SQL Database om tvingad frågekörningsplan ger tillförlitliga bättre prestanda för frågor med regression av körningsplan.

Om databasen skapades efter mars 2020 aktiverades alternativet automatisk justering av force plan automatiskt. Om databasen skapades före den här tiden kanske du vill aktivera alternativet för automatisk justering av framtvinga plan.

Justera index manuellt

Använd metoderna som beskrivs i Identifiera orsakerna till hög CPU för att identifiera frågeplaner för dina mest cpu-förbrukande frågor. Dessa körningsplaner hjälper dig att identifiera och lägga till icke-illustrerade index för att påskynda dina frågor.

Varje diskbaserat icke-grupperat index i databasen kräver lagringsutrymme och måste underhållas av SQL-motorn. Ändra befintliga index i stället för att lägga till nya index när det är möjligt och se till att nya index minskar CPU-användningen. En översikt över icke-illustrerade index finns i Riktlinjer för icke-indexdesign.

För vissa arbetsbelastningar kan kolumnlagringsindex vara det bästa valet för att minska PROCESSORn för frekventa läsfrågor. Se Columnstore-index – Designvägledning för rekommendationer på hög nivå om scenarier när kolumnlagringsindex kan vara lämpliga.

Justera program,frågor och databasinställningar

När du undersöker dina viktigaste frågor kan du hitta programskyddsmönster som "chattigt" beteende, arbetsbelastningar som skulle dra nytta av horisontell partitionering och underoptimal databasåtkomstdesign. För läsintensiva arbetsbelastningar bör du överväga skrivskyddade repliker för att avlasta skrivskyddade frågearbetsbelastningar och cachelagring på programnivå som långsiktiga strategier för att skala ut data som läses ofta.

Du kan också välja att justera den högsta PROCESSORn manuellt med hjälp av frågor som identifieras i din arbetsbelastning. Manuella justeringsalternativ omfattar att skriva om Transact-SQL-instruktioner, tvinga planer i Query Store och tillämpa frågetips.

Om du identifierar fall där frågor ibland använder en körningsplan som inte är optimal för prestanda kan du granska lösningarna i frågor som har problem med parameterkänslig plan (PSP)

Om du identifierar icke-parametriserade frågor med ett stort antal planer kan du överväga att parameterisera dessa frågor och se till att fullständigt deklarera parameterdatatyper, inklusive längd och precision. Detta kan göras genom att ändra frågorna, skapa en planguide för att framtvinga parameterisering av en specifik fråga eller genom att aktivera tvingad parameterisering på databasnivå.

Om du identifierar frågor med höga kompileringshastigheter kan du identifiera vad som orsakar den frekventa kompilering. Den vanligaste orsaken till frekvent kompilering är RECOMPILE-tips. När det är möjligt kan du identifiera när tipset RECOMPILE lades till och vilket problem det var tänkt att lösa. Undersök om en alternativ lösning för prestandajustering kan implementeras för att ge konsekventa prestanda för frågor som körs ofta utan tips RECOMPILE .

Minska CPU-användningen genom att justera den maximala graden av parallellitet

MaxdOP-inställningen (grad av parallellitet) styr parallelliteten mellan frågor i databasmotorn. Högre MAXDOP-värden resulterar vanligtvis i fler parallella trådar per fråga och snabbare frågekörning.

I vissa fall kan ett stort antal parallella frågor som körs samtidigt sakta ned en arbetsbelastning och orsaka hög CPU-användning. Överdriven parallellitet sker troligen i databaser med ett stort antal virtuella kärnor där MAXDOP är inställt på ett högt tal eller till noll. När MAXDOP är inställt på noll anger databasmotorn antalet schemaläggare som ska användas av parallella trådar till det totala antalet logiska kärnor eller 64, beroende på vilket som är mindre.

Du kan identifiera den maximala graden av parallellitetsinställning för databasen med Transact-SQL. Anslut till databasen med SSMS eller Azure Data Studio och kör följande fråga:

SELECT 
    name, 
    value, 
    value_for_secondary, 
    is_value_default 
FROM sys.database_scoped_configurations
WHERE name=N'MAXDOP';
GO

Överväg att experimentera med små ändringar i MAXDOP-konfigurationen på databasnivå eller ändra enskilda problematiska frågor för att använda en nondefault MAXDOP med hjälp av ett frågetips. Mer information finns i exemplen i konfigurera maximal grad av parallellitet.

När du ska lägga till CPU-resurser

Du kanske upptäcker att arbetsbelastningens frågor och index är korrekt inställda, eller att prestandajustering kräver ändringar som du inte kan göra på kort sikt på grund av interna processer eller andra orsaker. Att lägga till fler CPU-resurser kan vara fördelaktigt för dessa databaser. Du kan skala databasresurser med minimal stilleståndstid.

Du kan lägga till fler CPU-resurser i din Azure SQL Database genom att konfigurera antalet virtuella kärnor eller maskinvarukonfigurationen för databaser med köpmodellen för virtuella kärnor.

Under den DTU-baserade inköpsmodellen kan du höja tjänstnivån och öka antalet databastransaktionsenheter (DTU:er). En DTU representerar ett blandat mått på CPU, minne, läsningar och skrivningar. En fördel med köpmodellen för virtuell kärna är att den ger mer detaljerad kontroll över maskinvaran som används och antalet virtuella kärnor. Du kan migrera Azure SQL Database från den DTU-baserade modellen till den vCore-baserade modellen för att övergå mellan köpmodeller.

Relaterat innehåll

Läs mer om övervakning och prestandajustering av Azure SQL Database i följande artiklar: