Monitoraggio del carico di lavoro mediante DMVMonitor your workload using DMVs

In questo articolo viene descritto come utilizzare le viste a gestione dinamica (DMV) per monitorare il carico di lavoro ed esaminare l'esecuzione delle query SQL Data Warehouse di Azure.This article describes how to use Dynamic Management Views (DMVs) to monitor your workload and investigate query execution in Azure SQL Data Warehouse.

AutorizzazioniPermissions

Per eseguire una query sulle DMV in questo articolo è necessaria l'autorizzazione VISUALIZZAZIONE STATO DEL DATABASE o CONTROLLO.To query the DMVs in this article, you need either VIEW DATABASE STATE or CONTROL permission. In genere si consiglia di concedere l'autorizzazione VISUALIZZAZIONE STATO DEL DATABASE poiché è molto più restrittiva.Usually granting VIEW DATABASE STATE is the preferred permission as it is much more restrictive.

GRANT VIEW DATABASE STATE TO myuser;

Monitorare le connessioniMonitor connections

Tutti gli accessi a SQL Data Warehouse vengono registrati in sys.dm_pdw_exec_sessions.All logins to SQL Data Warehouse are logged to sys.dm_pdw_exec_sessions. Questa DMV contiene gli ultimi 10.000 accessi.This DMV contains the last 10,000 logins. L'elemento session_id è la chiave primaria e viene assegnato in sequenza per ogni nuovo accesso.The session_id is the primary key and is assigned sequentially for each new logon.

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

Monitorare l'esecuzione di queryMonitor query execution

Tutte le query eseguite in SQL Data Warehouse vengono registrate in sys.dm_pdw_exec_requests.All queries executed on SQL Data Warehouse are logged to sys.dm_pdw_exec_requests. Questa DMV contiene le ultime 10.000 query eseguite.This DMV contains the last 10,000 queries executed. L'elemento request_id identifica in modo univoco ogni query ed è la chiave primaria per questa DMV.The request_id uniquely identifies each query and is the primary key for this 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.The request_id is assigned sequentially for each new query and is prefixed with QID, which stands for query ID. Se si esegue una query nella DMV per un dato session_id, vengono visualizzate tutte le query per un determinato accesso.Querying this DMV for a given session_id shows all queries for a given logon.

Nota

Le stored procedure usano più ID richiesta.Stored procedures use multiple Request IDs. Gli ID richiesta vengono assegnati in ordine sequenziale.Request IDs are assigned in sequential order.

Ecco i passaggi da seguire per analizzare i piani e i tempi di esecuzione delle query per una query specifica.Here are steps to follow to investigate query execution plans and times for a particular query.

PASSAGGIO 1: individuare la query da analizzareSTEP 1: Identify the query you wish to investigate

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

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

Prendere nota dell'ID richiesta della query che si desidera analizzare dai risultati della query precedente.From the preceding query results, note the Request ID of the query that you would like to investigate.

Le query con stato Sospeso vengono messe in coda a causa dei limiti di concorrenza.Queries in the Suspended state are being queued due to concurrency limits. Queste query vengono visualizzate anche nella query sys.dm_pdw_waits waits con un tipo UserConcurrencyResourceType.These queries also appear in the sys.dm_pdw_waits waits query with a type of UserConcurrencyResourceType. Per altre informazioni sui limiti di concorrenza, vedere Gestione della concorrenza e del carico di lavoro.See Concurrency and workload management for more details on concurrency limits. L'attesa delle query può dipendere anche da altre motivazioni, come i blocchi degli oggetti.Queries can also wait for other reasons such as for object locks. Se la query è in attesa di una risorsa, vedere Analisi delle query in attesa di risorse più avanti in questo articolo.If your query is waiting for a resource, see Investigating queries waiting for resources further down in this article.

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.To simplify the lookup of a query in the sys.dm_pdw_exec_requests table, use LABEL to assign a comment to your query that can be looked up in the sys.dm_pdw_exec_requests view.

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

PASSAGGIO 2: Esaminare il piano di querySTEP 2: Investigate the query plan

Usare l'ID richiesta per recuperare il piano Distributed SQL (DSQL) della query da sys.dm_pdw_request_steps.Use the Request ID to retrieve the query's distributed SQL (DSQL) plan from 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.When a DSQL plan is taking longer than expected, the cause can be a complex plan with many DSQL steps or just one step taking a long time. 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.If the plan is many steps with several move operations, consider optimizing your table distributions to reduce data movement. L'articolo Distribuzione di tabelle spiega perché è necessario spostare i dati per risolvere una query e illustra alcune strategie di distribuzione per ridurre al minimo lo spostamento dei dati.The Table distribution article explains why data must be moved to solve a query and explains some distribution strategies to minimize data movement.

Per altre informazioni su un singolo passaggio, fare riferimento alla colonna operation_type del passaggio della query con esecuzione prolungata e all'indice dei passaggi:To investigate further details about a single step, the operation_type column of the long-running query step and note the Step Index:

  • Procedere al passaggio 3a per le operazioni SQL: OnOperation, RemoteOperation, ReturnOperation.Proceed with Step 3a for SQL operations: OnOperation, RemoteOperation, ReturnOperation.
  • Procedere al passaggio 3b per le operazioni di spostamento dati: ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation.Proceed with Step 3b for Data Movement operations: ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation.

PASSAGGIO 3a: Esaminare SQL nei database distribuitiSTEP 3a: Investigate SQL on the distributed databases

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.Use the Request ID and the Step Index to retrieve details from sys.dm_pdw_sql_requests, which contains execution information of the query step on all of the distributed databases.

-- 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.When the query step is running, DBCC PDW_SHOWEXECUTIONPLAN can be used to retrieve the SQL Server estimated plan from the SQL Server plan cache for the step running on a particular distribution.

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

DBCC PDW_SHOWEXECUTIONPLAN(1, 78);

PASSAGGIO 3b: Esaminare lo spostamento dei dati nei database distribuitiSTEP 3b: Investigate data movement on the distributed databases

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.Use the Request ID and the Step Index to retrieve information about a data movement step running on each distribution from sys.dm_pdw_dms_workers.

-- Find the 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.Check the total_elapsed_time column to see if a particular distribution is taking significantly longer than others for data movement.
  • 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.For the long-running distribution, check the rows_processed column to see if the number of rows being moved from that distribution is significantly larger than others. In caso affermativo, questo potrebbe indicare asimmetria dei dati sottostanti.If so, this may indicate skew of your underlying data.

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.If the query is running, DBCC PDW_SHOWEXECUTIONPLAN can be used to retrieve the SQL Server estimated plan from the SQL Server plan cache for the currently running SQL Step within a particular distribution.

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

DBCC PDW_SHOWEXECUTIONPLAN(55, 238);

Monitorare le query in attesaMonitor waiting queries

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.If you discover that your query is not making progress because it is waiting for a resource, here is a query that shows all the resources a query is waiting for.

-- 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.If the query is actively waiting on resources from another query, then the state will be AcquireResources. Se la query dispone di tutte le risorse necessarie, lo stato sarà Granted.If the query has all the required resources, then the state will be Granted.

Monitorare tempdbMonitor tempdb

Un uso intensivo di tempdb può essere la causa principale del rallentamento delle prestazioni e dei problemi di memoria insufficiente.High tempdb utilization can be the root cause for slow performance and out of memory issues. Se durante l'esecuzione di query tempdb raggiunge i limiti previsti, prendere in considerazione il ridimensionamento del data warehouse.Consider scaling your data warehouse if you find tempdb reaching its limits during query execution. Di seguito viene descritto come identificare l'uso di tempdb per ogni query in ogni nodo.The following describes how to identify tempdb usage per query on each node.

Creare la vista seguente per associare l'ID nodo appropriato per sys.dm_pdw_sql_requests.Create the following view to associate the appropriate node id for sys.dm_pdw_sql_requests. In questo modo sarà possibile sfruttare altre DMV pass-through e unire le tabelle con sys.dm_pdw_sql_requests.This will enable you to leverage other pass-through DMVs and join those tables with sys.dm_pdw_sql_requests.

-- sys.dm_pdw_sql_requests with the correct node id
CREATE VIEW sql_requests AS
(SELECT
       sr.request_id,
       sr.step_index,
       (CASE 
              WHEN (sr.distribution_id = -1 ) THEN 
              (SELECT pdw_node_id FROM sys.dm_pdw_nodes WHERE type = 'CONTROL') 
              ELSE d.pdw_node_id END) AS pdw_node_id,
       sr.distribution_id,
       sr.status,
       sr.error_id,
       sr.start_time,
       sr.end_time,
       sr.total_elapsed_time,
       sr.row_count,
       sr.spid,
       sr.command
FROM sys.pdw_distributions AS d
RIGHT JOIN sys.dm_pdw_sql_requests AS sr ON d.distribution_id = sr.distribution_id)

Per monitorare tempdb eseguire la query seguente:Run the following query to monitor tempdb:

-- Monitor tempdb
SELECT
    sr.request_id,
    ssu.session_id,
    ssu.pdw_node_id,
    sr.command,
    sr.total_elapsed_time,
    es.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 sql_requests AS sr ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id
WHERE DB_NAME(ssu.database_id) = 'tempdb'
    AND es.session_id <> @@SPID
    AND es.login_name <> 'sa' 
ORDER BY sr.request_id;

Monitorare la memoriaMonitor memory

La memoria può essere la causa principale del rallentamento delle prestazioni e dei problemi di memoria insufficiente.Memory can be the root cause for slow performance and out of memory issues. 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.Consider scaling your data warehouse if you find SQL Server memory usage reaching its limits during query execution.

La query seguente restituisce l'uso di memoria di SQL Server e l'eventuale uso elevato in ogni nodo:The following query returns SQL Server memory usage and memory pressure per node:

-- 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 transazioniMonitor transaction log size

La query seguente restituisce le dimensioni del log delle transazioni per ogni distribuzione.The following query returns the transaction log size on each distribution. 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.If one of the log files is reaching 160GB, you should consider scaling up your instance or limiting your transaction size.

-- 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)'
AND counter_name = 'Target Server Memory (KB)'

Monitorare il ripristino dello stato precedente del log delle transazioniMonitor transaction log rollback

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.If your queries are failing or taking a long time to proceed, you can check and monitor if you have any transactions rolling back.

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

Passaggi successiviNext steps

Per altre informazioni sulle DMV, vedere Viste di sistema.See System views for more information on DMVs. Per altre informazioni sulle procedure consigliate, vedere Procedure consigliate per Azure SQL Data WarehouseSee SQL Data Warehouse best practices for more information about best practices