Risolvere i problemi di una query lenta in un pool SQL dedicato

  • Articolo

Si applica a: Azure Synapse Analytics

Questo articolo consente di identificare i motivi e applicare mitigazioni per problemi di prestazioni comuni con le 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à rilevate nei dati raccolti.

Nota

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 Notebook in Azure Data Studio

Importante

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

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

Per risparmiare tempo per la risoluzione dei problemi, assicurarsi che le statistiche vengano create e aggiornate e che le CCC siano state ricompilati.

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

L'oggetto request_id della query lenta è necessario per ricercare i potenziali motivi di 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 indirizzare meglio le 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 nella parte superiore del set di risultati.

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

  • Valutare la possibilità di filtrare eventuali QID 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é potrebbe anche filtrare alcune query bloccate da altre sessioni.

Passaggio 2: Determinare dove la query richiede tempo

Eseguire lo script seguente per trovare il passaggio che può 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. Modificare il @ShowActiveOnly valore su 0 per confrontare tutti gli intervalli 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 mitigare

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 sono state inviate in breve tempo e sono state accodate per iniziare la compilazione.

Mitigazioni

Ridurre il numero di query inviate contemporaneamente.


Bloccato: allocazione delle risorse

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

  • Quantità di memoria concessa in base alla classe di risorse o all'assegnazione 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.

Mitigazioni

Query complessa o sintassi JOIN precedente

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

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

Anche se questi scenari sono atipici, sono disponibili opzioni per tentare di ignorare il comportamento predefinito per ridurre il tempo necessario per la scelta di un piano da parte di Query Optimizer.

Mitigazioni

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

Per un'efficienza del tempo di esecuzione, le istruzioni e TRUNCATE TABLE rinvieranno la pulizia dell'archiviazione DROP TABLE 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 vengano affollati e che le istruzioni successive DROP/TRUNCATE TABLE vengano eseguite lentamente.

Mitigazioni

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.


CBI non integri (in genere)

L'integrità dell'indice columnstore cluster (CCI) insufficiente richiede metadati aggiuntivi, che possono richiedere più tempo per determinare un piano ottimale. Per evitare questa situazione, assicurarsi che tutte le CCL siano in buona salute.

Mitigazioni

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 consente ON per impostazione predefinita di garantire che Query Optimizer possa prendere decisioni corrette sui piani distribuiti. Tuttavia, il processo di creazione automatica stesso può rendere una query iniziale più lunga rispetto alle esecuzioni successive dello stesso.

Mitigazioni

Se la prima esecuzione della query richiede in modo coerente la creazione di statistiche, è necessario creare manualmente le statistiche prima dell'esecuzione della query.


Creazione automatica di timeout delle statistiche

L'opzione AUTO_CREATE_STATISTICSdi creazione automatica delle statistiche consente ON per impostazione predefinita di garantire che Query Optimizer possa prendere decisioni corrette sui piani distribuiti. La creazione automatica di statistiche avviene 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.

Mitigazioni

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

Problemi della 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 non accurate
    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 del joiner non sono identici tra le tabelle.    		 Tipo/dimensione di dati non corrispondenti
    1. Il Description valore indica HadoopBroadcastOperationo HadoopRoundRobinOperationHadoopShuffleOperation.
    2. Il total_elapsed_time valore di un dato è step_index incoerente tra le esecuzioni.    		 Query di tabella esterna 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 associata 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 Asimmetria dati (archiviata).If <minimum rows value>/<maximum rows value>> 0.1, go to Data skew (stored).

    3. In caso contrario, passare all'asimmetria dei dati in anteprima.

Stime non accurate

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

Mitigazioni

Creare/aggiornare le statistiche.


Tabelle replicate non memorizzate nella cache

Se sono state create tabelle replicate e non si riesce a riscaldare correttamente la cache delle tabelle replicate, si verificheranno prestazioni insufficienti impreviste a causa di movimenti di dati aggiuntivi o della creazione di un piano distribuito non ottimale.

Mitigazioni

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

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

Mitigazioni

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


Query di tabella esterna 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 le attività simultanee dei contenitori di archiviazione.

Mitigazioni

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


Asimmetria dei dati (archiviata)

L'asimmetria dei dati significa che i dati non vengono distribuiti in modo uniforme 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 sfasati, non è possibile ottenere il potenziale completo delle risorse di elaborazione, ad esempio CPU e I/O, con conseguente rallentamento dei tempi di esecuzione.

Mitigazioni

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


Asimmetria 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 a essere asimmetrica. La natura del piano distribuito per filtri specifici o dati raggruppati causa un'operazione di ShuffleMoveOperation tipo. Questa operazione produce un output asimmetrico da utilizzare a valle.

Mitigazioni

  • Assicurarsi che le statistiche vengano create e aggiornate.
  • Modificare l'ordine delle GROUP BY colonne in modo che conduca con una colonna con cardinalità superiore.
  • Creare statistiche a più colonne se i join coprono più colonne.
  • Aggiungere un hint OPTION(FORCE_ORDER) di query alla 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 il nel wait_typepassaggio 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 in cui è incluso.
  3. Espandere la sezione relativa alla categoria di attesa dall'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, seguire questa procedura:

  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 avrà in genere registrato 60 piani di esecuzione a livello di distribuzione. Assicurarsi di preparare e confrontare i piani di esecuzione dello stesso passaggio del piano distribuito.

  4. La query passaggio 3 mostra 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 rapida per analizzare le possibili cause delle differenze.

Blocco, thread di lavoro
  • È consigliabile modificare le tabelle che subiscono modifiche frequenti e di piccole dimensioni per usare un indice dell'archivio righe anziché CCI.
  • Creare in batch le 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 del log tran

CBI non integri

Le CBI non integro 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 uno spostamento dei dati maggiore del 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'impatto sulle prestazioni può essere percepito dall'intero carico di lavoro.

Per risolvere questa situazione, assicurarsi che tutte le statistiche siano aggiornate e che sia in atto 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 ridimensionano le risorse in base alla DWU. Per ottenere prestazioni migliori, considerare uno o entrambi:

CPU, parallelismo
Scenario Mitigazione
Scarsa integrità CCI Valutare e correggere l'integrità dell'indice columnstore cluster in un pool SQL dedicato
Le query utente contengono trasformazioni Spostare tutta la formattazione e altre logica di trasformazione nei processi ETL in modo che vengano archiviate le versioni formattate
Carico di lavoro con priorità non corretta 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 in un altro client.

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

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

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