Usare le viste DMV per identificare e risolvere i problemi relativi alle prestazioni delle query

8 minuti

Le viste DMV offrono un'esperienza programmatica per il monitoraggio dell'attività del pool SQL di Azure Synapse Analytics usando il linguaggio Transact-SQL. Le viste fornite, oltre a consentire la risoluzione dei problemi e identificare i colli di bottiglia delle prestazioni con i carichi di lavoro in esecuzione nel sistema, vengono usati anche da altri servizi, ad esempio Azure Advisor, per fornire raccomandazioni su Azure Synapse Analytics.

Sono disponibili più di 90 viste DMV che possono essere analizzate tramite una query sui pool SQL dedicati per recuperare informazioni sulle aree seguenti del servizio:

Informazioni di connessione e attività
Richieste e query di esecuzione SQL
Informazioni sulle statistiche e sugli indici
Attività di blocco delle risorse
Attività del servizio di spostamento dati
Errors

Di seguito è riportato un esempio di monitoraggio dell'esecuzione di una query dei pool SQL di Azure Synapse Analytics. Il primo passaggio prevede il controllo delle connessioni al server, prima di controllare l'attività di esecuzione delle query.

Monitoraggio delle connessioni

Tutti gli account di accesso al data warehouse vengono registrati in sys.dm_pdw_exec_sessions. L'elemento session_id è la chiave primaria e viene assegnato in sequenza per ogni nuovo accesso.

-- Other Active Connections
SELECT * FROM sys.dm_pdw_exec_sessions where status <> 'Closed' and session_id <> session_id();

Monitorare l'esecuzione di query

Tutte le query eseguite sul pool SQL vengono registrate in sys.dm_pdw_exec_requests. L'elemento request_id identifica in modo univoco ogni query ed è la chiave primaria per questa DMV. L'elemento request_id viene assegnato in sequenza per ogni nuova query ed è preceduto da un prefisso con QID, che indica l'ID query. Se si esegue una query nella DMV per un dato session_id, vengono visualizzate tutte le query per un determinato accesso.

Passaggio 1

Il primo passaggio prevede l'identificazione della query che si vuole analizzare

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE status not in ('Completed','Failed','Cancelled')
  AND session_id <> session_id()
ORDER BY submit_time DESC;

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

Prendere nota dell'ID richiesta della query che si desidera analizzare dai risultati della query precedente.

È possibile che le query con stato Suspended vengano accodate a causa dell'elevato numero di query attive in esecuzione. Queste query vengono visualizzate anche nella query sys.dm_pdw_waits waits con un tipo UserConcurrencyResourceType. Per informazioni sui limiti di concorrenza, vedere i limiti di memoria e concorrenza o le classi di risorse per la gestione dei carichi di lavoro. L'attesa delle query può dipendere anche da altre motivazioni, come i blocchi degli oggetti. Se la query è in attesa di una risorsa, vedere Analisi delle query in attesa di risorse più avanti in questo articolo.

Per semplificare la ricerca di una query nella tabella sys.dm_pdw_exec_requests, usare LABEL per assegnare alla query un commento che possa essere cercato nella visualizzazione sys.dm_pdw_exec_requests.

-- Query with Label
SELECT *
FROM sys.tables
OPTION (LABEL = 'My Query')
;

-- Find a query with the Label 'My Query'
-- Use brackets when querying the label column, as it it a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

Passaggio 2

Usare l'ID richiesta per recuperare il piano Distributed SQL (DSQL) delle query da sys.dm_pdw_request_steps

-- Find the distributed query plan steps for a specific query.
-- Replace request_id with value from Step 1.

SELECT * FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID####'
ORDER BY step_index;

Quando un piano di DSQL impiega più tempo del previsto, la causa può essere un la complessità del piano, dovuta a molti passaggi di DSQL o a un solo passaggio che richiede molto tempo. Se il piano prevede molti passaggi con numerose operazioni di spostamento, prendere in considerazione di ottimizzare le distribuzioni di tabelle per ridurre lo spostamento dei dati.

L'articolo Distribuzione delle tabelle illustra perché è necessario spostare i dati per risolvere una query. Questo articolo illustra anche alcune strategie di distribuzione per ridurre al minimo lo spostamento dati.

Per esaminare altri dettagli su un singolo passaggio, la colonna operation_type del passaggio di query a esecuzione prolungata e prendere nota dell'indice dei passaggi:

Procedere con il passaggio 3 per le operazioni SQL: OnOperation, RemoteOperation, ReturnOperation.
Procedere con il passaggio 4 per le operazioni di spostamento dati: ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation.

Passaggio 3

Usare l'ID richiesta e l'indice dei passaggi per recuperare informazioni da sys.dm_pdw_sql_requests, che contiene informazioni sull'esecuzione del passaggio della query in tutti i database distribuiti.

-- Find the distribution run times for a SQL step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_sql_requests
WHERE request_id = 'QID####' AND step_index = 2;

Quando è in esecuzione il passaggio della query, è possibile usare DBCC PDW_SHOWEXECUTIONPLAN per recuperare il piano stimato di SQL Server dalla cache dei piani di SQL Server per il passaggio di esecuzione in una distribuzione specifica.

-- Find the SQL Server execution plan for a query running on a specific SQL pool or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(1, 78);

Passaggio 4

Usare l'ID richiesta e l'indice dei passaggi per recuperare informazioni sul passaggio di spostamento dei dati in esecuzione in ogni distribuzione da sys.dm_pdw_dms_workers.

-- Find information about all the workers completing a Data Movement Step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_dms_workers
WHERE request_id = 'QID####' AND step_index = 2;

Controllare la colonna total_elapsed_time per verificare se una particolare distribuzione stia impiegando più tempo delle altre per lo spostamento dati.
Per la distribuzione con esecuzione prolungata, esaminare la colonna rows_processed e controllare se il numero di righe spostato da tale distribuzione è più grande rispetto alle altre. In caso affermativo, questo potrebbe indicare asimmetria dei dati sottostanti. Una delle cause dell'asimmetria dei dati è la distribuzione in una colonna con molti valori NULL, le cui righe verranno inserite tutte nella stessa distribuzione. Prevenire le query lente evitando la distribuzione in questi tipi di colonne o filtrando la query per eliminare i valori NULL, se possibile.

Se la query è in esecuzione, è possibile usare DBCC PDW_SHOWEXECUTIONPLAN per recuperare il piano stimato di SQL Server dalla cache dei piani di SQL Server per il passaggio SQL in esecuzione per una distribuzione particolare.

-- Find the SQL Server estimated plan for a query running on a specific SQL pool Compute or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(55, 238);

Le viste DMV contengono solo 10.000 righe di dati. Nei sistemi utilizzati in modo intensivo i dati contenuti in questa tabella potrebbero quindi andare persi in poche ore o persino minuti perché i dati vengono gestiti in un sistema FIFO (First In First Out). Di conseguenza, è possibile perdere informazioni significative che consentono di diagnosticare i problemi di prestazioni delle query nel sistema. In questa situazione, è consigliabile usare Query Store.

È anche possibile monitorare altri aspetti dei pool SQL di Azure Synapse, tra cui:

Monitoraggio delle attese
Monitoraggio di tempdb
Monitoraggio della memoria
Monitoraggio del log delle transazioni
Monitoraggio di PolyBase

È possibile visualizzare informazioni sul monitoraggio di queste aree qui

Continua