Monitorare il carico di lavoro del pool SQL dedicato di Azure Synapse Analytics tramite DMV

Questo articolo descrive come usare le DMV (Dynamic Management Views) per monitorare il carico di lavoro, inclusa l'analisi dell'esecuzione di query in un pool SQL dedicato.

Autorizzazioni

Per eseguire una query sulle DMV in questo articolo è necessaria l'autorizzazione VIEW DATABASE STATE o CONTROL. In genere si consiglia di concedere l'autorizzazione VIEW DATABASE STATE poiché è molto più restrittiva.

GRANT VIEW DATABASE STATE TO myuser;

Monitorare le connessioni

Tutti gli accessi al data warehouse vengono registrati in sys.dm_pdw_exec_sessions. Questa DMV contiene gli ultimi 10.000 accessi. 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 nel pool SQL vengono registrate in sys.dm_pdw_exec_requests. Questa DMV contiene le ultime 10.000 query eseguite. 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.

Ecco i passaggi da seguire per analizzare i piani e i tempi di esecuzione delle query per una query specifica.

Passaggio 1: individuare la query da 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 in sys.dm_pdw_waits. In tal caso, cercare attese come UserConcurrencyResourceType. Per informazioni sui limiti di concorrenza, vedere Limiti di concorrenza e memoria oppure Classi di risorse per la gestione del carico 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 is a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

Passaggio 2: esaminare il piano di query

Usare l'ID richiesta per recuperare il piano Distributed SQL (DSQL) della 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 altre informazioni su un singolo passaggio, esaminare la colonna operation_type del passaggio della query con esecuzione prolungata e all'indice dei passaggi:

• Per le operazioni SQL (OnOperation, RemoteOperation, ReturnOperation), procedere al PASSAGGIO 3
• Per le operazioni di spostamento dati (ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation), procedere al PASSAGGIO 4.

Passaggio 3: esaminare SQL nei database distribuiti

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: esaminare lo spostamento dei dati nei database distribuiti

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 distribuzione particolare richiede più tempo per lo spostamento dei dati rispetto alle altre.
• Per la distribuzione con esecuzione prolungata, esaminare la colonna rows_processed e controllare se il numero di righe spostato da tale distribuzione è significativamente 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 specifica.

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

Monitorare le query in attesa

Se si rileva il mancato avanzamento di una query perché in attesa di una risorsa, ecco una query che mostra tutte le risorse attese da una query.

-- Find queries
-- Replace request_id with value from Step 1.

SELECT waits.session_id,
      waits.request_id,
      requests.command,
      requests.status,
      requests.start_time,
      waits.type,
      waits.state,
      waits.object_type,
      waits.object_name
FROM   sys.dm_pdw_waits waits
   JOIN  sys.dm_pdw_exec_requests requests
   ON waits.request_id=requests.request_id
WHERE waits.request_id = 'QID####'
ORDER BY waits.object_name, waits.object_type, waits.state;

Se la query è attivamente in attesa di risorse da un'altra query, lo stato sarà AcquireResources. Se la query dispone di tutte le risorse necessarie, lo stato sarà Granted.

Monitorare tempdb

Il database tempdb viene usato per contenere risultati intermedi durante l'esecuzione della query. L'utilizzo elevato del database tempdb può causare un rallentamento delle prestazioni delle query. Per ogni DW100c configurato, vengono allocati 399 GB di spazio tempdb (DW1000c avrebbe 3,99 TB di spazio tempdb totale). Di seguito sono riportati suggerimenti per il monitoraggio dell'utilizzo di tempdb e per ridurre l'utilizzo di tempdb nelle query.

Monitorare tempdb con le viste

Per monitorare l'utilizzo di tempdb, installare prima di tutto la vista microsoft.vw_sql_requests dal Toolkit Microsoft per pool SQL. È quindi possibile eseguire la query seguente per visualizzare l'utilizzo di tempdb per nodo per tutte le query eseguite:

-- Monitor tempdb
SELECT
    sr.request_id,
    ssu.session_id,
    ssu.pdw_node_id,
    sr.command,
    sr.total_elapsed_time,
    exs.login_name AS 'LoginName',
    DB_NAME(ssu.database_id) AS 'DatabaseName',
    (es.memory_usage * 8) AS 'MemoryUsage (in KB)',
    (ssu.user_objects_alloc_page_count * 8) AS 'Space Allocated For User Objects (in KB)',
    (ssu.user_objects_dealloc_page_count * 8) AS 'Space Deallocated For User Objects (in KB)',
    (ssu.internal_objects_alloc_page_count * 8) AS 'Space Allocated For Internal Objects (in KB)',
    (ssu.internal_objects_dealloc_page_count * 8) AS 'Space Deallocated For Internal Objects (in KB)',
    CASE es.is_user_process
    WHEN 1 THEN 'User Session'
    WHEN 0 THEN 'System Session'
    END AS 'SessionType',
    es.row_count AS 'RowCount'
FROM sys.dm_pdw_nodes_db_session_space_usage AS ssu
    INNER JOIN sys.dm_pdw_nodes_exec_sessions AS es ON ssu.session_id = es.session_id AND ssu.pdw_node_id = es.pdw_node_id
    INNER JOIN sys.dm_pdw_nodes_exec_connections AS er ON ssu.session_id = er.session_id AND ssu.pdw_node_id = er.pdw_node_id
    INNER JOIN microsoft.vw_sql_requests AS sr ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id
    LEFT JOIN sys.dm_pdw_exec_requests exr on exr.request_id = sr.request_id
    LEFT JOIN sys.dm_pdw_exec_sessions exs on exr.session_id = exs.session_id
WHERE DB_NAME(ssu.database_id) = 'tempdb'
    AND es.session_id <> @@SPID
    AND es.login_name <> 'sa'
ORDER BY sr.request_id;

Se si dispone di una query che utilizza una grande quantità di memoria o si è ricevuto un messaggio di errore relativo all'allocazione di tempdb, potrebbe essere dovuto all'esecuzione di CREATE TABLE AS SELECT (CTAS) o di un'istruzione INSERT SELECT SELECT molto grande che ha esito negativo nell'operazione di spostamento dati finale. Questa operazione può in genere essere identificata come operazione ShuffleMove nel piano di query distribuite subito prima dell'operazione INSERT SELECT finale. Usare sys.dm_pdw_request_steps per monitorare le operazioni ShuffleMove.

La mitigazione più comune consiste nel suddividere CTAS o l'istruzione INSERT SELECT in varie istruzioni di caricamento, in modo che il volume dei dati non superi il limite di 399 GB per 100DWUc tempdb. È anche possibile ridimensionare il cluster a dimensioni maggiori per aumentare la quantità di spazio tempdb disponibile.

Oltre alle istruzioni CTAS e INSERT SELECT, per le query complesse e di grandi dimensioni in esecuzione con memoria insufficiente potrebbe essere eseguito lo spill in tempdb, causando l'esito negativo delle query. Per evitare lo spill in tempdb, prendere in considerazione l'esecuzione con una classe di risorse di dimensioni maggiori.

Monitorare la memoria

La memoria può essere la causa principale del rallentamento delle prestazioni e dei problemi di memoria insufficiente. Se durante l'esecuzione di query l'uso di memoria di SQL Server raggiunge i limiti previsti, prendere in considerazione il ridimensionamento del data warehouse.

La query seguente restituisce l'uso di memoria di SQL Server e l'eventuale uso elevato in ogni nodo:

-- Memory consumption
SELECT
  pc1.cntr_value as Curr_Mem_KB,
  pc1.cntr_value/1024.0 as Curr_Mem_MB,
  (pc1.cntr_value/1048576.0) as Curr_Mem_GB,
  pc2.cntr_value as Max_Mem_KB,
  pc2.cntr_value/1024.0 as Max_Mem_MB,
  (pc2.cntr_value/1048576.0) as Max_Mem_GB,
  pc1.cntr_value * 100.0/pc2.cntr_value AS Memory_Utilization_Percentage,
  pc1.pdw_node_id
FROM
-- pc1: current memory
sys.dm_pdw_nodes_os_performance_counters AS pc1
-- pc2: total memory allowed for this SQL instance
JOIN sys.dm_pdw_nodes_os_performance_counters AS pc2
ON pc1.object_name = pc2.object_name AND pc1.pdw_node_id = pc2.pdw_node_id
WHERE
pc1.counter_name = 'Total Server Memory (KB)'
AND pc2.counter_name = 'Target Server Memory (KB)'

Monitorare le dimensioni del log delle transazioni

La query seguente restituisce le dimensioni del log delle transazioni per ogni distribuzione. Se uno dei file di log sta per raggiungere i 160 GB, considerare l'espansione dell'istanza del programma o la limitazione delle dimensioni delle transazioni.

-- Transaction log size
SELECT
  instance_name as distribution_db,
  cntr_value*1.0/1048576 as log_file_size_used_GB,
  pdw_node_id
FROM sys.dm_pdw_nodes_os_performance_counters
WHERE
instance_name like 'Distribution_%'
AND counter_name = 'Log File(s) Used Size (KB)'

Monitorare il ripristino dello stato precedente del log delle transazioni

Se le query non vanno a buon fine o richiedono molto tempo, verificare se è in corso il ripristino dello stato precedente delle transazioni e monitorare questa operazione.

-- Monitor rollback
SELECT
    SUM(CASE WHEN t.database_transaction_next_undo_lsn IS NOT NULL THEN 1 ELSE 0 END),
    t.pdw_node_id,
    nod.[type]
FROM sys.dm_pdw_nodes_tran_database_transactions t
JOIN sys.dm_pdw_nodes nod ON t.pdw_node_id = nod.pdw_node_id
GROUP BY t.pdw_node_id, nod.[type]

Monitorare il caricamento di PolyBase

La query seguente fornisce una stima approssimativa dello stato di avanzamento del carico. La query mostra solo i file attualmente in fase di elaborazione.

-- To track bytes and files
SELECT
    r.command,
    s.request_id,
    r.status,
    count(distinct input_name) as nbr_files,
    sum(s.bytes_processed)/1024/1024/1024 as gb_processed
FROM
    sys.dm_pdw_exec_requests r
    inner join sys.dm_pdw_dms_external_work s
        on r.request_id = s.request_id
GROUP BY
    r.command,
    s.request_id,
    r.status
ORDER BY
    nbr_files desc,
    gb_processed desc;

Monitorare i blocchi delle query

La query seguente fornisce le prime 500 query bloccate nell'ambiente.

--Collect the top blocking
SELECT
    TOP 500 waiting.request_id AS WaitingRequestId,
    waiting.object_type AS LockRequestType,
    waiting.object_name AS ObjectLockRequestName,
    waiting.request_time AS ObjectLockRequestTime,
    blocking.session_id AS BlockingSessionId,
    blocking.request_id AS BlockingRequestId
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
WHERE
    waiting.state = 'Queued'
    AND blocking.state = 'Granted'
ORDER BY
    ObjectLockRequestTime ASC;

Recuperare il testo della query dalle query in attesa e di blocco

La query seguente fornisce il testo e l'identificatore della query per le query in attesa e di blocco per risolvere facilmente i problemi.

-- To retrieve query text from waiting and blocking queries

SELECT waiting.session_id AS WaitingSessionId,
       waiting.request_id AS WaitingRequestId,
       COALESCE(waiting_exec_request.command,waiting_exec_request.command2) AS WaitingExecRequestText,
       blocking.session_id AS BlockingSessionId,
       blocking.request_id AS BlockingRequestId,
       COALESCE(blocking_exec_request.command,blocking_exec_request.command2) AS BlockingExecRequestText,
       waiting.object_name AS Blocking_Object_Name,
       waiting.object_type AS Blocking_Object_Type,
       waiting.type AS Lock_Type,
       waiting.request_time AS Lock_Request_Time,
       datediff(ms, waiting.request_time, getdate())/1000.0 AS Blocking_Time_sec
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
       INNER JOIN sys.dm_pdw_exec_requests waiting_exec_request
       ON waiting.request_id = waiting_exec_request.request_id
WHERE waiting.state = 'Queued'
       AND blocking.state = 'Granted'
ORDER BY Lock_Request_Time DESC;

Passaggi successivi

• Per altre informazioni sulle DMV, vedere Viste di sistema.