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 har skapats och uppdaterats 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.

    Obs! 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 som erhölls 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 distributionstidpunkter. Anteckna värdet wait_type för den distribution som kan orsaka prestandaproblemet.

Parameter Description
@QID Värdet request_id som erhölls 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 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å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-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 effektivitetsvinster vid körningstid skjuts lagringsrensningen DROP TABLE TRUNCATE TABLE upp 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. 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 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

Skapa/uppdatera statistik.


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

  • Kontrollera att statistik har skapats och uppdaterats.
  • Ä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_type 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.
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.

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.

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