Informazioni sui piani di query
Come ottenere un piano di query nel pool SQL dedicato di Azure Synapse Analytics
Spesso gli ingegneri e gli amministratori dei database hanno bisogno di analizzare e indagare sull'esecuzione di query nei pool SQL. Questa analisi viene eseguita usando viste DMV.
Nota
Le autorizzazioni per eseguire query sulle DMV richiedono l'autorizzazione VIEW DATABASE STATE o CONTROL negli scenari tradizionali, dove VIEW DATABASE STATE è il metodo preferito perché è più restrittivo.
GRANT VIEW DATABASE STATE to TestUser;
Avviso
Un utente malintenzionato può usare sys.dm_pdw_exec_requests per recuperare informazioni su oggetti di database specifici semplicemente usando l'autorizzazione VIEW SERVER STATE senza avere l'autorizzazione specifica per il database.
Le query di Azure Synapse che vengono eseguite vengono registrate, sys.dm_pdw_exec_requests contiene le ultime 10.000 query eseguite. è possibile usare la query seguente per determinare le prime 10 query più lunghe in esecuzione
SELECT TOP 10 request_id, status, total_elapsed_time
FROM sys.dm_pdw_exec_requests
WHERE status not in ('completed', 'Failed','Cancelled')
ORDER BY total_elapsed_time DESC
Quando si esaminano i risultati, quelle con stato sospeso possono essere accodate per mancanza di risorse da un numero elevato di query attive in esecuzione. Queste query verranno visualizzate anche in sys.dm_pdw_waits, dove l'interesse si sposta sulle attese tra cui UserConcurrencyResourceType. Esistono altri motivi per cui le query possono trovarsi in una coda di attesa, ad esempio per il blocco di oggetti. Altre informazioni su questo argomento sono disponibili esaminando le query in attesa delle risorse.
Nota
Secondo la procedura consigliata, usare LABEL in modo che gli utenti possano trovare e risolvere facilmente le query con prestazioni non ottimali usando sys.dm_pdw_exec_requests illustrato di seguito:
-- you can use an asterisk to show all attributes returned from the DMV
-- but request_id is what will be used in the next step.
SELECT request_id, status, total_elapsed_time
FROM sys.dm_pdw_exec_requests
WHERE [label] = 'MyQuery';
Come leggere il piano di query
Dopo aver ottenuto il valore di request_id, usarlo con sys.dm_pdw_request_steps per recuperare il piano DSQL (Distributed SQL) come illustrato di seguito:
-- be sure to replace QID#### with the request_id you retrieved above
SELECT * FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID####'
ORDER BY step_index;
Nota
Se il recupero del piano DSQL impiega molto tempo, è possibile che si tratti di un piano complesso che richiede molti passaggi DSQL o un unico passaggio a esecuzione prolungata. Se sono presenti diverse operazioni di spostamento all'interno del piano che prevedono molti passaggi, è consigliabile ottimizzare le distribuzioni di tabelle interessate per ridurre lo spostamento dei dati.
Quando si esamina il piano DSQL recuperato, è possibile ricavare altre informazioni sui singoli passaggi esaminando l'elemento operation_type di qualsiasi passaggio a esecuzione prolungata. Prendere nota del valore di Indice passo che verrà usato per analizzare due tipi di operazioni che influiscono sulle prestazioni, tra cui
Operazioni SQL che includono:
- OnOperation
- Operazione remote
- Operazione return
Operazioni di spostamento dei dati, tra cui:
- ShuffleMoveOperation
- BroadcastMoveOperation
- TrimMoveOperation
- PartitionMoveOperation
- MoveOperation
- CopyOperation
Dal passaggio precedente che usa sys.dm_pdw_request_steps, si vuole usare request_id e step_index per recuperare le informazioni di esecuzione del passaggio di query su tutto il database distribuito usando una query simile a quella seguente.
--Find the distribution run times for a particular SQL Step
--retrieve request_id and step_index with the values from the steps above.
SELECT * FROM sys.dm_pdw_sql_requests
WHERE request_id = 'QID####' AND step_index = 4;
Quando il passaggio della query è ancora in esecuzione, è possibile usare DBCC PDW_SHOWEXECUTIONPLAN per recuperare il piano stimato di SQL Server dalla cache del piano per il passaggio in esecuzione su una distribuzione specifica come illustrato di seguito.
--Retrieve the SQL Server execution plan for a running query on a particular SQL pool or control node
--Replace distribution_id and spid from the results from the previous query
--DBCC PDW_SHOWEXECUTIONPLAN( distribution_id, spid)
DBCC PDW_SHOWEXECUTIONPLAN(7, 31)
Ricerca degli spostamenti nei database distribuiti
L'uso di ID richiesta e di Indice passo così come recuperati in precedenza insieme a sys.dm_pdw_dms_workers consentirà il recupero di informazioni su tutti i passaggi di spostamento dei dati in esecuzione in ciascuna distribuzione come illustrato di seguito.
--Find all workers that are completing a Data Movement Step and their details
--Replace request_id and step_index with the values from the prior steps shown above.
SELECT * FROM sys.dm_pdw_dms_workers
WHERE request_id = 'QID####' AND step_index = 2;
Nota
Il controllo della colonna total_elapsed_time aiuterà a determinare se una distribuzione specifica richiede molto più tempo delle altre per lo spostamento dei dati. per qualsiasi distribuzione a esecuzione prolungata, controllare la colonna rows_processed per determinare se l'operazione è materialmente più complessa delle altre. In caso affermativo, questo potrebbe indicare un'asimmetria dei dati sottostanti.
Un esempio di un'operazione non valida che può causare problemi di prestazioni, ad esempio l'asimmetria dei dati, è la distribuzione su una colonna che contiene molti valori NULL, che causerà l'indirizzamento di tali valori nella stessa distribuzione. Se possibile, eliminare i valori Null o filtrarli completamente dalla query per aumentare le prestazioni.
Per altre informazioni per risolvere i problemi, fare riferimento alle sezioni Creare statistiche per migliorare le prestazioni, Informazioni sui problemi di prestazioni relativi alle tabelle, Informazioni sulla progettazione della distribuzione delle tabelle e Usare gli indici per migliorare le prestazioni