Dela via

Felsöka en långsam fråga i en dedikerad SQL-pool

  • Artikel

Gäller för: Azure Synapse Analytics

Den här artikeln hjälper dig att identifiera orsakerna och tillämpa åtgärder för vanliga prestandaproblem med frågor i en dedikerad SQL-pool i Azure Synapse Analytics.

Följ stegen för att felsöka problemet eller köra stegen i notebook-filen via Azure Data Studio. De första tre stegen beskriver hur du samlar in telemetri, som beskriver livscykeln för en fråga. Referenserna i slutet av artikeln hjälper dig att analysera potentiella affärsmöjligheter som finns i de data som samlas in.

Kommentar

Innan du försöker öppna den här notebook-filen kontrollerar du att Azure Data Studio är installerat på den lokala datorn. Om du vill installera det går du till Lär dig hur du installerar Azure Data Studio.

Öppna notebook-fil i Azure Data Studio

Viktigt!

De flesta rapporterade prestandaproblemen orsakas av:

  • Inaktuell statistik
  • Felfritt klustrade kolumnlagringsindex (CCIs)

Om du vill spara felsökningstid kontrollerar du att statistiken skapas och är uppdaterad och återskapar klustrade kolumnlagringsindex i den dedikerade SQL-poolen.

Steg 1: Identifiera request_id (även kallat QID)

Den request_id långsamma frågan krävs för att undersöka potentiella orsaker till en långsam fråga. Använd följande skript som utgångspunkt för att identifiera den fråga som du vill felsöka. När den långsamma frågan har identifierats noterar du request_id värdet.

Övervaka först de aktiva frågorna. Den här frågan sorteras efter de senaste raderna först.

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [status] NOT IN ('Completed', 'Failed', 'Cancelled')
      AND session_id <> session_id()
-- AND [label] = '<YourLabel>'
-- AND resource_allocation_percentage is not NULL
ORDER BY submit_time DESC;

Leta sedan reda på de aktiva frågor som har den längsta körningstiden och börja med de frågor som körs längst.

-- Find top 10 longest running queries
SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC;

Använd följande tips när du kör skriptet för att bättre rikta in dig på långsamma frågor:

  • Sortera efter antingen submit_time DESC eller total_elapsed_time DESC för att ha de frågor som körs längst upp i resultatuppsättningen.
  • Använd OPTION(LABEL='<YourLabel>') i dina frågor och filtrera label sedan kolumnen för att identifiera dem.
  • Överväg att filtrera bort eventuella QID:er som inte har något värde för resource_allocation_percentage när du vet att målsatsen finns i en batch. Var försiktig med det här filtret eftersom det också kan filtrera bort vissa frågor som blockeras av andra sessioner.

Steg 2: Fastställa var frågan tar tid

Kör följande skript för att hitta det steg som kan orsaka frågans prestandaproblem. Uppdatera variablerna i skriptet med de värden som beskrivs i följande tabell. Ändra värdet @ShowActiveOnly till 0 för att få en fullständig bild av den distribuerade planen. Anteckna StepIndexvärdena för , Phaseoch Description för det långsamma steg som identifieras från resultatuppsättningen.

Parameter Description
@QID Värdet request_id hämtas i steg 1.
@ShowActiveOnly Om du anger värdet till 0 visas alla steg för frågan.
Om du anger värdet till 1 visas endast det aktiva steget.		 
DECLARE @QID AS VARCHAR (16) = '<request_id>', @ShowActiveOnly AS BIT = 1;
-- Retrieve session_id of QID

DECLARE @session_id AS VARCHAR (16) = (SELECT session_id
                                       FROM sys.dm_pdw_exec_requests
                                       WHERE request_id = @QID);
-- Blocked by Compilation or Resource Allocation (Concurrency)
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
   'Blocked waiting on '
       + MAX(CASE WHEN waiting.type = 'CompilationConcurrencyResourceType' THEN 'Compilation Concurrency'
                  WHEN waiting.type LIKE 'Shared-%' THEN ''
                  ELSE 'Resource Allocation (Concurrency)' END)
       + MAX(CASE WHEN waiting.type LIKE 'Shared-%' THEN ' for ' + REPLACE(waiting.type, 'Shared-', '')
             ELSE '' END) AS [Description],
   MAX(waiting.request_time) AS [StartTime], GETDATE() AS [EndTime],
   DATEDIFF(ms, MAX(waiting.request_time), GETDATE())/1000.0 AS [Duration],
   NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount], NULL AS [TSQL]
FROM sys.dm_pdw_waits waiting
WHERE waiting.session_id = @session_id
      AND ([type] LIKE 'Shared-%'
           OR [type] IN ('ConcurrencyResourceType', 'UserConcurrencyResourceType', 'CompilationConcurrencyResourceType'))
      AND [state] = 'Queued'
GROUP BY session_id
-- Blocked by another query
UNION ALL
SELECT @session_id AS session_id,
       @QID AS request_id,
       -1 AS [StepIndex],
       'Compilation' AS [Phase],
       'Blocked by ' + blocking.session_id + ':' + blocking.request_id + ' when requesting ' + waiting.type + ' on ' + QUOTENAME(waiting.object_type) + waiting.object_name AS [Description],
       waiting.request_time AS [StartTime],
       GETDATE() AS [EndTime],
       DATEDIFF(ms, waiting.request_time, GETDATE()) / 1000.0 AS [Duration],
       NULL AS [Status],
       NULL AS [EstimatedRowCount],
       NULL AS [ActualRowCount],
       COALESCE (blocking_exec_request.command, blocking_exec_request.command2) AS [TSQL]
FROM sys.dm_pdw_waits AS waiting
     INNER JOIN
     sys.dm_pdw_waits AS blocking
     ON waiting.object_type = blocking.object_type
        AND waiting.object_name = blocking.object_name
     INNER JOIN
     sys.dm_pdw_exec_requests AS blocking_exec_request
     ON blocking.request_id = blocking_exec_request.request_id
WHERE waiting.session_id = @session_id
      AND waiting.state = 'Queued'
      AND blocking.state = 'Granted'
      AND waiting.type != 'Shared'
-- Request Steps
UNION ALL
SELECT @session_id AS session_id,
       @QID AS request_id,
       step_index AS [StepIndex],
       'Execution' AS [Phase],
       operation_type + ' (' + location_type + ')' AS [Description],
       start_time AS [StartTime],
       end_time AS [EndTime],
       total_elapsed_time / 1000.0 AS [Duration],
       [status] AS [Status],
       CASE WHEN estimated_rows > -1 THEN estimated_rows END AS [EstimatedRowCount],
       CASE WHEN row_count > -1 THEN row_count END AS [ActualRowCount],
       command AS [TSQL]
FROM sys.dm_pdw_request_steps
WHERE request_id = @QID
      AND [status] = CASE @ShowActiveOnly WHEN 1 THEN 'Running' ELSE [status] END
ORDER BY StepIndex;

Steg 3: Granska steginformation

Kör följande skript för att granska informationen om steget som identifierades i föregående steg. Uppdatera variablerna i skriptet med de värden som beskrivs i följande tabell. Ändra värdet @ShowActiveOnly till 0 för att jämföra alla distributionstidpunkter. Anteckna värdet wait_type för fördelningen som kan orsaka prestandaproblemet.

Parameter Description
@QID Värdet request_id hämtas i steg 1.
@StepIndex Värdet StepIndex identifieras i steg 2.
@ShowActiveOnly Om du anger värdet till 0 visas alla distributioner för det angivna StepIndex värdet.
Om du anger värdet till 1 visas endast de aktuella aktiva distributionerna för det angivna StepIndex värdet.		 
DECLARE @QID VARCHAR(16) = '<request_id>', @StepIndex INT = <StepIndex>, @ShowActiveOnly BIT = 1;
WITH dists
AS (SELECT request_id, step_index, 'sys.dm_pdw_sql_requests' AS source_dmv,
       distribution_id, pdw_node_id, spid, 'NativeSQL' AS [type], [status],
       start_time, end_time, total_elapsed_time, row_count
    FROM sys.dm_pdw_sql_requests
    WHERE request_id = @QID AND step_index = @StepIndex
    UNION ALL
    SELECT request_id, step_index, 'sys.dm_pdw_dms_workers' AS source_dmv,
       distribution_id, pdw_node_id, sql_spid AS spid, [type],
       [status], start_time, end_time, total_elapsed_time, rows_processed as row_count
    FROM sys.dm_pdw_dms_workers
    WHERE request_id = @QID AND step_index = @StepIndex
   )
SELECT sr.step_index, sr.distribution_id, sr.pdw_node_id, sr.spid,
       sr.type, sr.status, sr.start_time, sr.end_time,
       sr.total_elapsed_time, sr.row_count, owt.wait_type, owt.wait_time
FROM dists sr
   LEFT JOIN sys.dm_pdw_nodes_exec_requests owt
      ON sr.pdw_node_id = owt.pdw_node_id
         AND sr.spid = owt.session_id
         AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests'
                 AND sr.status = 'Running') -- sys.dm_pdw_sql_requests status
              OR (sr.source_dmv = 'sys.dm_pdw_dms_requests'
                     AND sr.status not LIKE 'Step[CE]%')) -- sys.dm_pdw_dms_workers final statuses
WHERE sr.request_id = @QID
      AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests' AND sr.status =
               CASE WHEN @ShowActiveOnly = 1 THEN 'Running' ELSE sr.status END)
           OR (sr.source_dmv = 'sys.dm_pdw_dms_workers' AND sr.status NOT LIKE
                  CASE WHEN @ShowActiveOnly = 1 THEN 'Step[CE]%' ELSE '' END))
      AND sr.step_index = @StepIndex
ORDER BY distribution_id

Steg 4: Diagnostisera och minimera

Problem med kompileringsfasen

Blockerad: Samtidighet för kompilering

Kompileringsblock för samtidighet förekommer sällan. Men om du stöter på den här typen av block betyder det att en stor mängd frågor skickades på kort tid och har köats för att påbörja kompilering.

Åtgärder

Minska antalet frågor som skickas samtidigt.


Blockerad: resursallokering

Om du blockeras för resursallokering innebär det att frågan väntar på att köras baserat på:

  • Mängden minne som beviljas baserat på resursklassen eller tilldelningen av arbetsbelastningsgruppen som är associerad med användaren.
  • Mängden tillgängligt minne i system- eller arbetsbelastningsgruppen.
  • (Valfritt) Arbetsbelastningsgruppens/klassificerarens prioritet.

Åtgärder

Komplex fråga eller äldre JOIN-syntax

Du kan stöta på en situation där standardmetoderna för frågeoptimeraren är ineffektiva eftersom kompileringsfasen tar lång tid. Det kan inträffa om frågan:

  • Omfattar ett stort antal kopplingar och/eller underfrågor (komplex fråga).
  • Använder kopplingar i FROM -satsen (inte ANSI-92-formatkopplingar).

Även om dessa scenarier är atypiska har du alternativ för att försöka åsidosätta standardbeteendet för att minska den tid det tar för frågeoptimeraren att välja en plan.

Åtgärder

  • Använd ANSI-92-formatkopplingar.
  • Lägg till frågetips: OPTION(FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')). Mer information finns i FORCE ORDER and Cardinality Estimation (SQL Server).
  • Dela upp frågan i flera, mindre komplexa steg.
TIDSKRÄVANDE DROP TABLE ELLER TRUNKERA TABELL

För körningstidseffektivitet skjuter and-uttrycken DROP TABLETRUNCATE TABLE upp lagringsrensningen till en bakgrundsprocess. Men om din arbetsbelastning utför ett stort antal DROP/TRUNCATE TABLE instruktioner inom en kort tidsram är det möjligt att metadata blir trångt och gör att efterföljande DROP/TRUNCATE TABLE instruktioner körs långsamt.

Åtgärder

Identifiera ett underhållsfönster, stoppa alla arbetsbelastningar och kör DBCC SHRINKDATABASE för att tvinga fram en omedelbar rensning av tidigare borttagna eller trunkerade tabeller.


Ej felfria CCI:er (vanligtvis)

Dålig CCI-hälsa (Clustered ColumnStore Index) kräver extra metadata, vilket kan göra att frågeoptimeraren tar mer tid att fastställa en optimal plan. Undvik den här situationen genom att se till att alla dina CCI:er är vid god hälsa.

Åtgärder

Utvärdera och korrigera klustrad kolumnlagringsindexhälsa i en dedikerad SQL-pool.


Fördröjning från statistik för automatisk skapande

Alternativet AUTO_CREATE_STATISTICS för automatisk statistikskapande är ON som standard för att säkerställa att frågeoptimeraren kan fatta bra beslut om distribuerad plan. Själva processen för automatisk skapande kan dock göra att en inledande fråga tar längre tid än efterföljande körningar av samma.

Åtgärder

Om den första körningen av frågan konsekvent kräver att statistik skapas måste du manuellt skapa statistik innan frågan körs.


Tidsgränser för automatisk skapande av statistik

Alternativet AUTO_CREATE_STATISTICS för automatisk statistikskapande är ON som standard för att säkerställa att frågeoptimeraren kan fatta bra beslut om distribuerad plan. Automatisk skapande av statistik sker som svar på en SELECT-instruktion och har ett tröskelvärde på 5 minuter att slutföra. Om storleken på data och/eller antalet statistik som ska skapas kräver längre tid än tröskelvärdet på 5 minuter avbryts automatiskt skapandet av statistik så att frågan kan fortsätta att köras. Misslyckandet med att skapa statistiken kan påverka frågeoptimerarens förmåga att generera en effektiv distribuerad körningsplan, vilket resulterar i dåliga frågeprestanda.

Åtgärder

Skapa statistiken manuellt i stället för att förlita dig på funktionen för automatisk skapande för de identifierade tabellerna/kolumnerna.

Problem med körningsfasen

  • Använd följande tabell för att analysera resultatuppsättningen i steg 2. Fastställ ditt scenario och kontrollera den gemensamma orsaken till detaljerad information och möjliga åtgärdssteg.

    Scenario Vanlig orsak
    EstimatedRowCount / ActualRowCount < 25% Felaktiga uppskattningar
    Värdet Description anger BroadcastMoveOperation och frågan refererar till en replikerad tabell. Ej replikerade tabeller
    1. @ShowActiveOnly = 0
    2. Högt eller oväntat antal steg (step_index) observeras.
    3. Datatyper av kopplingskolumner är inte identiska mellan tabeller.    		 Felmatchad datatyp/storlek
    1. Värdet Description anger HadoopBroadcastOperation, HadoopRoundRobinOperation eller HadoopShuffleOperation.
    2. Värdet total_elapsed_time för en given step_index är inkonsekvent mellan körningar.    		 Frågor om ad hoc-extern tabell

  • Kontrollera värdet total_elapsed_time som erhölls i steg 3. Om den är betydligt högre i några distributioner i ett visst steg följer du dessa steg:

    1. Kontrollera datafördelningen för varje tabell som refereras i fältet TSQL för associerad step_id genom att köra följande kommando mot var och en:

      DBCC PDW_SHOWSPACEUSED(<table>);

    2. Om <minsta radvärde>/<maximalt radvärde>> 0,1 går du till Datasnedställning (lagrad).

    3. Annars går du till Snedställning av data under flygning.

Felaktiga uppskattningar

Ha din statistik uppdaterad för att säkerställa att frågeoptimeraren genererar en optimal plan. När det uppskattade radantalet är betydligt mindre än det faktiska antalet måste statistiken bibehållas.

Åtgärder

Kontrollera först riktigheten i statistiken i en dedikerad SQL-pool. Skapa eller uppdatera statistiken om det behövs.


Ej replikerade tabeller

Om du har skapat replikerade tabeller och du inte kan värma den replikerade tabellcachen korrekt resulterar oväntade dåliga prestanda på grund av extra dataförflyttningar eller skapandet av en suboptimal distribuerad plan.

Åtgärder

  • Värm den replikerade cachen efter DML-åtgärder.
  • Om det finns frekventa DML-åtgärder ändrar du fördelningen av tabellen till ROUND_ROBIN.
Felmatchad datatyp/storlek

När du ansluter tabeller kontrollerar du att datatypen och storleken på de sammanfogade kolumnerna matchar. Annars resulterar det i onödiga dataförflyttningar som minskar tillgängligheten för CPU-, I/O- och nätverkstrafik till resten av arbetsbelastningen.

Åtgärder

Återskapa tabellerna för att korrigera relaterade tabellkolumner som inte har identisk datatyp och storlek.


Frågor om ad hoc-extern tabell

Frågor mot externa tabeller är utformade med avsikten att massinläsning av data i den dedikerade SQL-poolen. Ad hoc-frågor mot externa tabeller kan drabbas av variabelvaraktighet på grund av externa faktorer, till exempel samtidiga lagringscontaineraktiviteter.

Åtgärder

Läs in data i den dedikerade SQL-poolen först och fråga sedan inlästa data.


Dataförskjutning (lagrad)

Dataförskjutning innebär att data inte fördelas jämnt över distributionerna. Varje steg i den distribuerade planen kräver att alla distributioner slutförs innan du går vidare till nästa steg. När dina data är skeva kan inte den fulla potentialen för bearbetningsresurserna, till exempel CPU och I/O, uppnås, vilket resulterar i långsammare körningstider.

Åtgärder

Läs vår vägledning för distribuerade tabeller för att hjälpa dig att välja en lämpligare distributionskolumn.


Förskjutning av data under flygning

Datasnedvridning under flygning är en variant av problemet med datasnedvridning (lagrad). Men det är inte distributionen av data på disken som är skev. Typen av distribuerad plan för specifika filter eller grupperade data orsakar en ShuffleMoveOperation typåtgärd. Den här åtgärden genererar skeva utdata som ska förbrukas nedströms.

Åtgärder

  • Se till att statistik skapas och är uppdaterad. Du kan kontrollera att de är korrekta genom att följa stegen som beskrivs i Kontrollera statistiknoggrannheten i en dedikerad SQL-pool.
  • Ändra ordningen på dina GROUP BY kolumner så att de leder med en kolumn med högre kardinalitet.
  • Skapa statistik för flera kolumner om kopplingar omfattar flera kolumner.
  • Lägg till frågetips OPTION(FORCE_ORDER) i din fråga.
  • Omstrukturera frågan.

Problem med väntetyp

Om inget av ovanstående vanliga problem gäller för din fråga ger steg 3-data möjlighet att avgöra vilka väntetyper (i wait_type och wait_time) som stör frågebearbetningen för det längsta steget. Det finns ett stort antal väntetyper och de grupperas i relaterade kategorier på grund av liknande åtgärder. Följ dessa steg för att hitta väntekategorin för frågesteget:

  1. Identifiera i wait_typesteg 3 som tar mest tid.
  2. Leta upp väntetypen i mappningstabellen för väntekategorier och identifiera den väntekategori som den ingår i.
  3. Expandera avsnittet som är relaterat till väntekategorin från följande lista för rekommenderade åtgärder.
Kompilering

Följ de här stegen för att minska problem med väntetyp i kategorin Kompilering:

  1. Återskapa index för alla objekt som ingår i den problematiska frågan.
  2. Uppdatera statistik för alla objekt som ingår i den problematiska frågan.
  3. Testa den problematiska frågan igen för att kontrollera om problemet kvarstår.

Om problemet kvarstår:

  1. Skapa en .sql fil med:

    SET QUERY_DIAGNOSTICS ON; <Your_SQL>; SET QUERY_DIAGNOSTICS OFF;

  2. Öppna ett kommandotolkfönster och kör följande kommando:

    sqlcmd −S <servername>.database.windows.net −d <databasename> −U <username> −G −I −i .\<sql_file_name>.sql −y0 −o .\<output_file_name>.txt

  3. Öppna <output_file_name>.txt i en textredigerare. Leta upp och kopiera klistra in körningsplanerna på distributionsnivå (rader som börjar med <ShowPlanXML>) från det längsta steget som identifierades i steg 2 i separata textfiler med ett .sqlplan-tillägg .

    Obs! Varje steg i den distribuerade planen har vanligtvis registrerat 60 körningsplaner på distributionsnivå. Se till att du förbereder och jämför körningsplaner från samma distribuerade plansteg.

  4. Steg 3-frågan visar ofta några distributioner som tar mycket längre tid än andra. I SQL Server Management Studio jämför du körningsplanerna på distributionsnivå (från de .sqlplan-filer som skapats) för en långvarig distribution till en snabb distribution för att analysera potentiella orsaker till skillnader.

Lås, arbetstråd
  • Överväg att ändra tabeller som genomgår frekventa, små ändringar för att använda ett radlagringsindex i stället för CCI.
  • Batch-upp dina ändringar och uppdatera målet med fler rader på en mindre frekvent basis.
Buffert-I/O, annan disk-I/O, Tran-logg-I/O

Ej felfria CCIs

Felaktiga CCI:er bidrar till ökad I/O, CPU och minnesallokering, vilket i sin tur påverkar frågeprestandan negativt. Försök med någon av följande metoder för att åtgärda problemet:

Inaktuell statistik

Inaktuell statistik kan orsaka generering av en ooptimerad distribuerad plan, vilket innebär mer dataflytt än nödvändigt. Onödig dataförflyttning ökar arbetsbelastningen inte bara på dina vilande data utan även på tempdb. Eftersom I/O är en delad resurs för alla frågor kan prestandapåverkan drabba hela arbetsbelastningen.

Optimeraren förlitar sig på statistik för att uppskatta antalet rader som returneras av en fråga. Statistik gör att frågeoptimeraren kan välja den mest effektiva planen eller utföra den bästa flyttåtgärden (till exempel en Shuffle Move Operation eller Broad Cast Move Operation) för att justera data under kopplingsvillkoret. Det bästa kopplingsvillkoret beror på tabelldistributionstypen.

Om det faktiska antalet rader för en viss tabell till exempel är 60 miljoner och det uppskattade antalet rader är 1 000 (på kontrollnodsnivå) kan optimeraren välja en sändningsflyttåtgärd. Det här beteendet beror på att kostnaden uppfattas som lägre jämfört med en Shuffle Move, med tanke på optimerarens antagande att tabellen bara innehåller 1 000 rader. När den faktiska körningen börjar flyttas dock 60 miljoner rader som en del av körningen med hjälp av en Broadcast-flytt, vilket kan vara en dyr åtgärd med tanke på både datastorleken och radantalet. Om datastorleken är betydande kan det därför leda till prestandaproblem för själva frågan och andra frågor, vilket resulterar i hög CPU-användning.

Du kan åtgärda den här situationen genom att se till att all statistik är uppdaterad och att en underhållsplan finns på plats för att hålla dem uppdaterade för användararbetsbelastningar. Du kan kontrollera riktigheten i statistiken genom att följa stegen som beskrivs i Kontrollera statistikprecisionen i en dedikerad SQL-pool.

Tunga I/O-arbetsbelastningar

Din övergripande arbetsbelastning kanske läser stora mängder data. Synapse-dedikerade SQL-pooler skalar resurser i enlighet med DWU. För att uppnå bättre prestanda bör du överväga antingen eller båda:

CPU, parallellitet
Scenario Riskreducering
Dålig CCI-hälsa Utvärdera och korrigera hälsa för klustrat kolumnlagringsindex i en dedikerad SQL-pool
Användarfrågor innehåller transformeringar Flytta all formatering och annan transformeringslogik till ETL-processer så att formaterade versioner lagras
Felaktig prioritet för arbetsbelastning Implementera arbetsbelastningsisolering
Otillräcklig DWU för arbetsbelastning Överväg att öka beräkningsresurserna

Nätverks-I/O

Om problemet uppstår under en RETURN åtgärd i steg 2,

  • Minska antalet samtidiga parallella processer.
  • Skala ut den mest påverkade processen till en annan klient.

För alla andra dataförflyttningsåtgärder är det troligt att nätverksproblemen verkar vara interna för den dedikerade SQL-poolen. Följ dessa steg för att snabbt åtgärda problemet:

  1. Skala din dedikerade SQL-pool till DW100c
  2. Skala tillbaka till önskad DWU-nivå
SQL CLR

Undvik frekvent användning av FORMAT() funktionen genom att implementera ett alternativt sätt att transformera data (till exempel CONVERT() med stil).