Condividi tramite

Risolvere i problemi relativi a una query lenta in un pool SQL dedicato

  • Articolo

Si applica a: Azure Synapse Analytics

Questo articolo illustra come identificare i motivi e applicare le mitigazioni per i problemi di prestazioni comuni relativi alle query in un pool SQL dedicato di Azure Synapse Analytics.

Seguire la procedura per risolvere il problema o eseguire i passaggi nel notebook tramite Azure Data Studio. I primi tre passaggi illustrano la raccolta dei dati di telemetria, che descrive il ciclo di vita di una query. I riferimenti alla fine dell'articolo consentono di analizzare le potenziali opportunità trovate nei dati raccolti.

Note

Prima di tentare di aprire questo notebook, assicurarsi che Azure Data Studio sia installato nel computer locale. Per installarlo, vedere Informazioni su come installare Azure Data Studio.

Aprire il notebook in Azure Data Studio

Importante

La maggior parte dei problemi di prestazioni segnalati è causata da:

  • Statistiche obsolete
  • Indici columnstore cluster non integri (CCI)

Per risparmiare tempo per la risoluzione dei problemi, assicurarsi che le statistiche vengano create e aggiornate e che le interfacce CC SIANO state ricompilate.

Passaggio 1: Identificare il request_id (noto anche come QID)

L'oggetto request_id della query lenta è necessario per cercare potenziali motivi per una query lenta. Usare lo script seguente come punto di partenza per identificare la query da risolvere. Dopo aver identificato la query lenta, annotare il request_id valore.

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

Per una migliore destinazione delle query lente, usare i suggerimenti seguenti quando si esegue lo script:

  • Ordinare in base submit_time DESC a o total_elapsed_time DESC per avere le query con esecuzione più lunga presenti all'inizio del set di risultati.

  • Usare OPTION(LABEL='<YourLabel>') nelle query e quindi filtrare la label colonna per identificarle.

  • Valutare la possibilità di filtrare tutti gli id di ottimizzazione quantistica che non hanno un valore per resource_allocation_percentage quando si sa che l'istruzione di destinazione è contenuta in un batch.

    Nota: prestare attenzione a questo filtro perché può anche escludere alcune query bloccate da altre sessioni.

Passaggio 2: Determinare dove la query richiede tempo

Eseguire lo script seguente per trovare il passaggio che potrebbe causare il problema di prestazioni della query. Aggiornare le variabili nello script con i valori descritti nella tabella seguente. Modificare il @ShowActiveOnly valore su 0 per ottenere l'immagine completa del piano distribuito. Prendere nota dei StepIndexvalori , Phasee Description del passaggio lento identificato dal set di risultati.

Parametro Descrizione
@QID Valore request_id ottenuto nel passaggio 1
@ShowActiveOnly 0 - Mostra tutti i passaggi per la query
1 - Mostra solo il passaggio attualmente attivo		 
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;

Passaggio 3: Esaminare i dettagli del passaggio

Eseguire lo script seguente per esaminare i dettagli del passaggio identificato nel passaggio precedente. Aggiornare le variabili nello script con i valori descritti nella tabella seguente. Impostare il @ShowActiveOnly valore su 0 per confrontare tutti i tempi di distribuzione. Prendere nota del wait_type valore per la distribuzione che può causare il problema di prestazioni.

Parametro Descrizione
@QID Valore request_id ottenuto nel passaggio 1
@StepIndex Valore StepIndex identificato nel passaggio 2
@ShowActiveOnly 0 - Mostra tutte le distribuzioni per il valore specificato StepIndex
1 - Mostra solo le distribuzioni attualmente attive per il valore specificato StepIndex		 
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

Passaggio 4: Diagnosticare e attenuare

Problemi relativi alla fase di compilazione

Bloccato: concorrenza di compilazione

I blocchi di compilazione della concorrenza si verificano raramente. Tuttavia, se si verifica questo tipo di blocco, significa che un volume elevato di query è stato inviato in breve tempo e che è stato accodato per iniziare la compilazione.

Procedure di mitigazione

Ridurre il numero di query inviate simultaneamente.


Bloccato: allocazione delle risorse

Essere bloccati per l'allocazione delle risorse significa che la query è in attesa di essere eseguita in base a:

  • Quantità di memoria concessa in base all'assegnazione della classe di risorse o del gruppo di carico di lavoro associata all'utente.
  • Quantità di memoria disponibile nel sistema o nel gruppo di carico di lavoro.
  • (Facoltativo) Importanza del gruppo/classificatore del carico di lavoro.

Procedure di mitigazione

  • Attendere il completamento della sessione di blocco.
  • Valutare la scelta della classe di risorse. Per altre informazioni, vedere Limiti di concorrenza.
  • Valutare se è preferibile terminare la sessione di blocco.
Query complessa o sintassi JOIN precedente

È possibile che si verifichi una situazione in cui i metodi di Query Optimizer predefiniti sono dimostrati inefficaci perché la fase di compilazione richiede molto tempo. Può verificarsi se la query:

  • Implica un numero elevato di join e/o sottoquery (query complessa).
  • Utilizza joiner nella FROM clausola (non join di stile ANSI-92).

Anche se questi scenari sono atipici, è possibile provare a eseguire l'override del comportamento predefinito per ridurre il tempo necessario per Query Optimizer per scegliere un piano.

Procedure di mitigazione

  • Usare join di stile ANSI-92.
  • Aggiungere hint per la query: OPTION(FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')). Per altre informazioni, vedere FORCE ORDER and Cardinality Estimation (SQL Server).For more information, see FORCE ORDER and Cardinality Estimation (SQL Server).
  • Suddividere la query in più passaggi meno complessi.
DROP TABLE a esecuzione prolungata o TRUNCATE TABLE

Per l'efficienza del tempo di esecuzione, le istruzioni e TRUNCATE TABLE rinvieranno la DROP TABLE pulizia dell'archiviazione a un processo in background. Tuttavia, se il carico di lavoro esegue un numero elevato di istruzioni in un breve intervallo di DROP/TRUNCATE TABLE tempo, è possibile che i metadati diventino affollati e causino un rallentamento dell'esecuzione delle istruzioni successive.DROP/TRUNCATE TABLE

Procedure di mitigazione

Identificare una finestra di manutenzione, arrestare tutti i carichi di lavoro ed eseguire DBCC SHRINKDATABASE per forzare una pulizia immediata delle tabelle eliminate o troncate in precedenza.


CCIs non integri (in genere)

L'integrità dell'indice columnstore cluster (CCI) scadente richiede metadati aggiuntivi, il che può richiedere più tempo per determinare un piano ottimale. Per evitare questa situazione, assicurarsi che tutti i CCI siano in buona salute.

Procedure di mitigazione

Valutare e correggere l'integrità dell'indice columnstore cluster in un pool SQL dedicato.


Ritardo dalla creazione automatica delle statistiche

L'opzione AUTO_CREATE_STATISTICSdi creazione automatica delle statistiche è ON per impostazione predefinita per garantire che Query Optimizer possa prendere decisioni valide per il piano distribuito. Tuttavia, il processo di creazione automatica può richiedere più tempo rispetto alle esecuzioni successive della stessa query.

Procedure di mitigazione

Se la prima esecuzione della query richiede la creazione coerente delle statistiche, sarà necessario creare manualmente le statistiche prima dell'esecuzione della query.


Timeout di creazione automatica delle statistiche

L'opzione AUTO_CREATE_STATISTICSdi creazione automatica delle statistiche è ON per impostazione predefinita per garantire che Query Optimizer possa prendere decisioni valide per il piano distribuito. La creazione automatica delle statistiche viene eseguita in risposta a un'istruzione SELECT e ha una soglia di 5 minuti da completare. Se le dimensioni dei dati e/o il numero di statistiche da creare richiedono più tempo della soglia di 5 minuti, la creazione automatica delle statistiche verrà abbandonata in modo che la query possa continuare l'esecuzione. L'errore di creazione delle statistiche può influire negativamente sulla capacità di Query Optimizer di generare un piano di esecuzione distribuito efficiente, con conseguente riduzione delle prestazioni delle query.

Procedure di mitigazione

Creare manualmente le statistiche anziché basarsi sulla funzionalità di creazione automatica per le tabelle/colonne identificate.

Problemi relativi alla fase di esecuzione

  • Usare la tabella seguente per analizzare il set di risultati nel passaggio 2. Determinare lo scenario e verificare la causa comune per informazioni dettagliate e i possibili passaggi di mitigazione.

    Scenario Causa comune
    EstimatedRowCount/ActualRowCount< 25% Stime imprecise
    Il Description valore indica BroadcastMoveOperation e la query fa riferimento a una tabella replicata. Tabelle replicate non memorizzate nella cache
    1. @ShowActiveOnly = 0
    2. Si osserva un numero elevato o imprevisto di passaggi (step_index).
    3. I tipi di dati delle colonne joiner non sono identici tra le tabelle.    		 Tipo di dati/dimensioni non corrispondenti
    1. Il Description valore indica HadoopBroadcastOperation, HadoopRoundRobinOperation o HadoopShuffleOperation.
    2. Il total_elapsed_time valore di un dato step_index è incoerente tra le esecuzioni.    		 Query di tabelle esterne ad hoc

  • Controllare il total_elapsed_time valore ottenuto nel passaggio 3. Se è significativamente superiore in alcune distribuzioni in un determinato passaggio, seguire questa procedura:

    1. Controllare la distribuzione dei dati per ogni tabella a cui si fa riferimento nel TSQL campo per associato step_id eseguendo il comando seguente su ogni tabella:

      DBCC PDW_SHOWSPACEUSED(<table>);

    2. Se il valore minimo delle righe/il valore massimo delle righe 0,1, passare a Differenza dati (archiviato).If <minimum rows value/<maximum rows value>>> 0.1, go to Data skew (stored).

    3. In caso contrario, passare a In-flight data skew.

Stime imprecise

Disporre delle statistiche aggiornate per garantire che Query Optimizer generi un piano ottimale. Quando il conteggio delle righe stimato è significativamente inferiore ai conteggi effettivi, è necessario mantenere le statistiche.

Procedure di mitigazione

Creare/aggiornare le statistiche.


Tabelle replicate non memorizzate nella cache

Se sono state create tabelle replicate e non è possibile scaldare correttamente la cache delle tabelle replicate, le prestazioni impreviste potrebbero risultare scarse a causa di spostamenti di dati aggiuntivi o della creazione di un piano distribuito non ottimale.

Procedure di mitigazione

  • Scaldare la cache replicata dopo le operazioni DML.
  • Se sono presenti frequenti operazioni DML, modificare la distribuzione della tabella in ROUND_ROBIN.
Tipo di dati/dimensioni non corrispondenti

Quando si uniscono tabelle, assicurarsi che il tipo di dati e le dimensioni delle colonne di join corrispondano. In caso contrario, comporterà spostamenti di dati non necessari che ridurranno la disponibilità di CPU, I/O e traffico di rete al resto del carico di lavoro.

Procedure di mitigazione

Ricompilare le tabelle per correggere le colonne della tabella correlate che non hanno un tipo di dati e dimensioni identiche.


Query di tabelle esterne ad hoc

Le query su tabelle esterne sono progettate con l'intenzione di caricare in blocco i dati nel pool SQL dedicato. Le query ad hoc su tabelle esterne possono subire durate variabili a causa di fattori esterni, ad esempio attività simultanee del contenitore di archiviazione.

Procedure di mitigazione

Caricare prima i dati nel pool SQL dedicato e quindi eseguire query sui dati caricati.


Asimmetria dei dati (archiviata)

L'asimmetria dei dati indica che i dati non vengono distribuiti uniformemente tra le distribuzioni. Ogni passaggio del piano distribuito richiede il completamento di tutte le distribuzioni prima di passare al passaggio successivo. Quando i dati sono asimmetrici, il potenziale completo delle risorse di elaborazione, ad esempio CPU e I/O, non può essere raggiunto, causando tempi di esecuzione più lenti.

Procedure di mitigazione

Esaminare le linee guida per le tabelle distribuite per facilitare la scelta di una colonna di distribuzione più appropriata.


Sfasamento dei dati in anteprima

L'asimmetria dei dati in anteprima è una variante del problema di asimmetria dei dati (archiviato). Ma non è la distribuzione dei dati su disco asimmetrica. La natura del piano distribuito per determinati filtri o dati raggruppati causa un'operazione ShuffleMoveOperation di tipo. Questa operazione produce un output asimmetrico da utilizzare a valle.

Procedure di mitigazione

  • Assicurarsi che le statistiche vengano create e aggiornate.
  • Modificare l'ordine delle GROUP BY colonne in modo da generare una colonna con cardinalità superiore.
  • Creare statistiche su più colonne se i join coprono più colonne.
  • Aggiungere hint OPTION(FORCE_ORDER) per la query.
  • Effettuare il refactoring della query.

Problemi relativi al tipo di attesa

Se nessuno dei problemi comuni precedenti si applica alla query, i dati del passaggio 3 offrono la possibilità di determinare quali tipi di attesa (in wait_type e wait_time) interferiscono con l'elaborazione delle query per il passaggio con esecuzione più lunga. Esistono un numero elevato di tipi di attesa e sono raggruppati in categorie correlate a causa di mitigazioni simili. Seguire questa procedura per individuare la categoria di attesa del passaggio della query:

  1. Identificare l'oggetto wait_type nel passaggio 3 che richiede più tempo.
  2. Individuare il tipo di attesa nella tabella di mapping delle categorie di attesa e identificare la categoria di attesa inclusa in .
  3. Espandere la sezione relativa alla categoria di attesa nell'elenco seguente per le mitigazioni consigliate.
Compilazione

Seguire questa procedura per attenuare i problemi relativi al tipo di attesa della categoria Compilazione:

  1. Ricompilare gli indici per tutti gli oggetti coinvolti nella query problematica.
  2. Aggiornare le statistiche su tutti gli oggetti coinvolti nella query problematica.
  3. Testare di nuovo la query problematica per verificare se il problema persiste.

Se il problema persiste, procedere come illustrato di seguito:

  1. Creare un file .sql con:

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

  2. Aprire una finestra del prompt dei comandi ed eseguire il comando seguente:

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

  3. Aprire <output_file_name>.txt in un editor di testo. Individuare e copiare incollare i piani di esecuzione a livello di distribuzione (righe che iniziano con <ShowPlanXML>) dal passaggio con esecuzione più lunga identificato nel passaggio 2 in file di testo separati con estensione sqlplan .

    Nota: ogni passaggio del piano distribuito in genere ha registrato 60 piani di esecuzione a livello di distribuzione. Assicurarsi di preparare e confrontare i piani di esecuzione dallo stesso passaggio del piano distribuito.

  4. La query passaggio 3 rivela spesso alcune distribuzioni che richiedono molto più tempo rispetto ad altre. In SQL Server Management Studio confrontare i piani di esecuzione a livello di distribuzione (dai file con estensione sqlplan creati) di una distribuzione a esecuzione prolungata a una distribuzione a esecuzione prolungata per analizzare le potenziali cause delle differenze.

Blocco, thread di lavoro
  • Valutare la possibilità di modificare le tabelle che subiscono modifiche frequenti e piccole per utilizzare un indice dell'archivio righe anziché CCI.
  • Eseguire il batch delle modifiche e aggiornare la destinazione con più righe in modo meno frequente.
I/O del buffer, altre operazioni di I/O su disco, I/O log tran

CCI non integri

Le interfacce CCI non integre contribuiscono a un aumento dell'allocazione di I/O, CPU e memoria, che, a sua volta, influisce negativamente sulle prestazioni delle query. Per attenuare questo problema, provare uno dei metodi seguenti:

Statistiche obsolete

Le statistiche obsolete possono causare la generazione di un piano distribuito non ottimizzato, che comporta più spostamento dei dati rispetto al necessario. Lo spostamento dei dati non necessario aumenta il carico di lavoro non solo sui dati inattivi, ma anche su tempdb. Poiché le operazioni di I/O sono una risorsa condivisa in tutte le query, l'intero carico di lavoro può influire sulle prestazioni.

Per risolvere questa situazione, assicurarsi che tutte le statistiche siano aggiornate e che sia stato eseguito un piano di manutenzione per mantenerle aggiornate per i carichi di lavoro degli utenti.

Carichi di lavoro di I/O pesanti

Il carico di lavoro complessivo potrebbe leggere grandi quantità di dati. I pool SQL dedicati di Synapse dimensionano le risorse in base alla DWU. Per ottenere prestazioni migliori, considerare o entrambi:

CPU, parallelismo
Scenario Strategia di riduzione del rischio
Integrità CCI ridotta Valutare e correggere l'integrità dell'indice columnstore in cluster in un pool SQL dedicato
Le query utente contengono trasformazioni Spostare tutta la formattazione e altra logica di trasformazione nei processi ETL in modo da archiviare le versioni formattate
Priorità non corretta del carico di lavoro Implementare l'isolamento del carico di lavoro
DWU insufficiente per il carico di lavoro Prendere in considerazione l'aumento delle risorse di calcolo

I/O di rete

Se il problema si verifica durante un'operazione RETURN nel passaggio 2,

  • Ridurre il numero di processi paralleli simultanei.
  • Aumentare il numero di istanze del processo più interessato a un altro client.

Per tutte le altre operazioni di spostamento dei dati, è probabile che i problemi di rete siano interni al pool SQL dedicato. Per tentare di attenuare rapidamente questo problema, seguire questa procedura:

  1. Ridimensionare il pool SQL dedicato in DW100c
  2. Ridimensionare al livello DWU desiderato
SQL CLR

Evitare l'uso frequente della FORMAT() funzione implementando un modo alternativo di trasformare i dati , CONVERT() ad esempio con stile.