Dela via

Felsöka en långsam fråga på 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 på 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 vägleder dig genom insamling av 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.

Obs!

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 prestandaproblem orsakas av:

  • Inaktuell statistik
  • Grupperade kolumnlagringsindex (CCIs) med feltillstånd

Om du vill spara felsökningstid kontrollerar du att statistiken har skapats och att den är uppdaterad och att CCI:er har återskapats.

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.

-- 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;

-- 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.

    Observera: Var försiktig med det här filtret eftersom det också kan filtrera bort vissa frågor som blockeras av andra sessioner.

Steg 2: Avgöra 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 , Phaseoch Description för det långsamma steg som identifieras från resultatuppsättningen.

Parameter Beskrivning
@QID Värdet request_id som hämtades i steg 1
@ShowActiveOnly 0 – Visa alla steg för frågan
1 – Visa endast det aktiva steget		 
DECLARE @QID VARCHAR(16) = '<request_id>', @ShowActiveOnly BIT = 1; 
-- Retrieve session_id of QID
DECLARE @session_id 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 waiting
   INNER JOIN sys.dm_pdw_waits blocking
      ON waiting.object_type = blocking.object_type
      AND waiting.object_name = blocking.object_name
   INNER JOIN sys.dm_pdw_exec_requests 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 distributionstider. Anteckna värdet wait_type för den distribution som kan orsaka prestandaproblemet.

Parameter Beskrivning
@QID Värdet request_id som hämtades i steg 1
@StepIndex Värdet StepIndex som identifieras i steg 2
@ShowActiveOnly 0 – Visa alla distributioner för det angivna StepIndex värdet
1 – Visa 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 åtgärda

Problem med kompileringsfasen

Blockerad: Samtidig kompilering

Kompileringsblock för samtidighet inträffar 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 placerats i kö för att påbörja kompilering.

Mitigations

Minska antalet frågor som skickas samtidigt.


Blockerad: resursallokering

Att blockeras för resursallokering innebär 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 systemet eller arbetsbelastningsgruppen.
  • (Valfritt) Arbetsbelastningsgruppens/klassificerarens betydelse.

Mitigations

Komplex fråga eller äldre JOIN-syntax

Du kan stöta på en situation där standardmetoderna för frågeoptimerare ä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-formatanslutningar).

Ä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.

Mitigations

  • 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 TRUNCATE TABLE

För effektivitetseffektivitet för körningstid skjuter instruktionerna och TRUNCATE TABLE upp lagringsrensningen DROP TABLE till en bakgrundsprocess. Men om din arbetsbelastning utför ett stort antal DROP/TRUNCATE TABLE instruktioner inom en kort tidsperiod är det möjligt att metadata blir överbelastade och gör att efterföljande DROP/TRUNCATE TABLE instruktioner körs långsamt.

Mitigations

Identifiera en underhållsperiod, stoppa alla arbetsbelastningar och kör DBCC SHRINKDATABASE för att framtvinga en omedelbar rensning av tidigare borttagna eller trunkerade tabeller.


Ej felfria CCI:er (vanligtvis)

Dåligt CCI-hälsotillstånd (Clustered ColumnStore Index) kräver extra metadata, vilket kan göra att frågeoptimeraren tar mer tid på sig 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.

Mitigations

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_STATISTICSför automatisk skapandestatistik ä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.

Mitigations

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_STATISTICSför automatisk skapandestatistik är ON som standard för att säkerställa att frågeoptimeraren kan fatta bra beslut om distribuerad plan. Den automatiska genereringen 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 den automatiska skapandet av statistik så att frågan kan fortsätta köras. Om du inte skapar statistiken kan det påverka frågeoptimerarens förmåga att generera en effektiv distribuerad körningsplan negativt, vilket resulterar i sämre frågeprestanda.

Mitigations

Skapa statistiken manuellt i stället för att förlita dig på funktionen skapa automatiskt 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 vanliga 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 anslutna replikerade tabeller
    1. @ShowActiveOnly = 0
    2. Högt eller oväntat antal steg (step_index) observeras.
    3. Datatyper för kopplingskolumner är inte identiska mellan tabeller.    		 Datatyp/storlek som inte matchar
    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.    		 Ad hoc-frågor för extern tabell

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

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

      DBCC PDW_SHOWSPACEUSED(<table>);

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

    3. Annars går du till Förskjutning 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.

Mitigations

Skapa/uppdatera statistik.


Ej anslutna 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 icke-optimal distribuerad plan.

Mitigations

  • 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.
Datatyp/storlek som inte matchar

När du ansluter tabeller kontrollerar du att datatypen och storleken på de sammanfogningskolumnerna 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.

Mitigations

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


Ad hoc-frågor för 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 varierande varaktighet på grund av externa faktorer, till exempel samtidiga lagringscontaineraktiviteter.

Mitigations

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


Datasnedställning (lagrad)

Dataförskjutning innebär att data inte fördelas jämnt mellan 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 hos bearbetningsresurserna, till exempel CPU och I/O, uppnås, vilket resulterar i långsammare körningstider.

Mitigations

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

Förskjutning av data under flygning är en variant av problemet med datasnedställning (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 användas nedströms.

Mitigations

  • Kontrollera att statistik skapas och är uppdaterad.
  • Ä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 frågan.
  • 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. wait_type Identifiera i steg 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.
Sammanställning

Följ de här stegen för att åtgärda 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 kommandotolksfö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 filnamnstillägget .sqlplan .

    Observera: 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 .sqlplan-filerna 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, tranlogg-I/O

CcI:er med feltillstånd

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

Inaktuell statistik

Inaktuell statistik kan orsaka generering av en icke-optimerad distribuerad plan, vilket innebär mer dataförflyttning ä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 påverka hela arbetsbelastningen.

Du kan åtgärda den här situationen genom att se till att all statistik är uppdaterad och att det finns en underhållsplan för att hålla dem uppdaterade för användararbetsbelastningar.

Tunga I/O-arbetsbelastningar

Din övergripande arbetsbelastning kan läsa 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 klustrad kolumnlagringsindexhälsa i en dedikerad SQL-pool
Användarfrågor innehåller transformeringar Flytta all formatering och annan transformeringslogik till ETL-processer så att de formaterade versionerna lagras
Felaktigt prioriterad 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 om du vill försöka åtgärda problemet snabbt:

  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).