Monitore sua carga de trabalho usando DMVsMonitor your workload using DMVs

Este artigo descreve como usar DMVs (Exibições de Gerenciamento Dinâmico) para monitorar a carga de trabalho.This article describes how to use Dynamic Management Views (DMVs) to monitor your workload. Isso inclui a investigação de execução da consulta no SQL Data Warehouse do Azure.This includes investigating query execution in Azure SQL Data Warehouse.

PermissõesPermissions

Para consultar as DMVs deste artigo, você precisa de permissão VIEW DATABASE STATE ou CONTROL.To query the DMVs in this article, you need either VIEW DATABASE STATE or CONTROL permission. Geralmente, é preferível conceder a permissão VIEW DATABASE STATE, por ser muito mais restritiva.Usually granting VIEW DATABASE STATE is the preferred permission as it is much more restrictive.

GRANT VIEW DATABASE STATE TO myuser;

Conexões do monitorMonitor connections

Todos os logons no SQL Data Warehouse são registrados em sys.dm_pdw_exec_sessions.All logins to SQL Data Warehouse are logged to sys.dm_pdw_exec_sessions. Essa DMV contém os últimos 10.000 logons.This DMV contains the last 10,000 logins. A session_id é a chave primária e é atribuída em sequência para cada novo logon.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();

Monitorar a execução de consultasMonitor query execution

Todas as consultas executadas no SQL Data Warehouse são registradas em sys.dm_pdw_exec_requests.All queries executed on SQL Data Warehouse are logged to sys.dm_pdw_exec_requests. Essa DMV contém as últimas 10.000 consultas executadas.This DMV contains the last 10,000 queries executed. A request_id identifica cada consulta exclusivamente e é a chave primária para essa DMV.The request_id uniquely identifies each query and is the primary key for this DMV. A request_id é atribuída em sequência para cada nova consulta e é prefixada com QID, que representa a ID da consulta.The request_id is assigned sequentially for each new query and is prefixed with QID, which stands for query ID. A consulta a esta DMV para uma determinada session_id mostra todas as consultas para um logon específico.Querying this DMV for a given session_id shows all queries for a given logon.

Observação

Os procedimentos armazenados usam vários request_ids.Stored procedures use multiple Request IDs. As IDs de solicitação são atribuídas em ordem sequencial.Request IDs are assigned in sequential order.

Estas são as etapas para investigar os planos de execução da consulta e as horas para uma consulta específica.Here are steps to follow to investigate query execution plans and times for a particular query.

ETAPA 1: Identificar a consulta que você deseja investigarSTEP 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;

Nos resultados da consulta anterior, observe a ID da Solicitação da consulta que você deseja investigar.From the preceding query results, note the Request ID of the query that you would like to investigate.

As consultas no estado suspenso podem ser enfileiradas devido a um grande número de consultas ativas em execução.Queries in the Suspended state can be queued due to a large number of active running queries. Essas consultas também aparecem na consulta Sys. dm_pdw_waits esperas com um tipo de UserConcurrencyResourceType.These queries also appear in the sys.dm_pdw_waits waits query with a type of UserConcurrencyResourceType. Para obter informações sobre limites de simultaneidade, consulte limites de memória e de simultaneidade para o Azure SQL data warehouse ou classes de recursos para gerenciamento de carga de trabalho.For information on concurrency limits, see Memory and concurrency limits for Azure SQL Data Warehouse or Resource classes for workload management. As consultas também podem esperar por motivos, como bloqueios.Queries can also wait for other reasons such as for object locks. Se sua consulta estiver aguardando um recurso, confira Investigar consultas aguardando recursos mais adiante neste artigo.If your query is waiting for a resource, see Investigating queries waiting for resources further down in this article.

Para simplificar a pesquisa de uma consulta na tabela Sys. dm_pdw_exec_requests , use o rótulo para atribuir um comentário à consulta que pode ser pesquisada na exibição 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')
;

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

ETAPA 2: investigar o plano de consultaSTEP 2: Investigate the query plan

Use a ID de solicitação para recuperar o DSQL (plano de SQL distribuído) da consulta de 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 um plano DSQL estiver demorando mais do que o esperado, a causa pode ser um plano complexo com muitas etapas de DSQL ou apenas uma etapa demorando muito 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 o plano tiver muitas etapas com várias operações de movimentação, considere otimizar suas distribuições de tabela para reduzir a movimentação de dados.If the plan is many steps with several move operations, consider optimizing your table distributions to reduce data movement. O artigo Distribuição da tabela explica por que os dados devem ser movidos para resolver uma consulta e explica algumas estratégias de distribuição para minimizar a movimentação de dados.The Table distribution article explains why data must be moved to solve a query and explains some distribution strategies to minimize data movement.

Para investigar mais detalhes sobre uma única etapa, verifique a coluna operation_type da etapa de consulta de execução longa de consulta e observe o Índice da etapa:To investigate further details about a single step, the operation_type column of the long-running query step and note the Step Index:

  • Continue com a Etapa 3a para Operações SQL: OnOperation, RemoteOperation, ReturnOperation.Proceed with Step 3a for SQL operations: OnOperation, RemoteOperation, ReturnOperation.
  • Continue com a Etapa 3b para Operações de movimentação de dados: ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation.Proceed with Step 3b for Data Movement operations: ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation.

ETAPA 3a: investigar o SQL nos bancos de dados distribuídosSTEP 3a: Investigate SQL on the distributed databases

Use a ID da Solicitação e o Índice de Etapas para recuperar os detalhes de sys.dm_pdw_sql_requests, que contém informações sobre a execução da consulta em todos os bancos de dados distribuídos.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;

Se a consulta estiver em execução, DBCC PDW_SHOWEXECUTIONPLAN poderá ser usado para recuperar o plano estimado do SQL Server do cache do plano do SQL Server para a etapa em execução em uma distribuição específica.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);

ETAPA 3b: investigar a movimentação de dados em bancos de dados distribuídosSTEP 3b: Investigate data movement on the distributed databases

Use a ID da Solicitação e o Índice da Etapa para recuperar as informações sobre a etapa de movimentação dos dados em execução em cada distribuição em 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;
  • Verifique a coluna total_elapsed_time para ver se uma distribuição específica está demorando muito mais do que outras para movimentar dados.Check the total_elapsed_time column to see if a particular distribution is taking significantly longer than others for data movement.
  • Para a distribuição de longa execução, verifique a coluna rows_processed para verificar se o número de linhas sendo movidas dessa distribuição é significativamente maior do que outros.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. Nesse caso, essa localização pode indicar uma distorção dos dados subjacentes.If so, this finding might indicate skew of your underlying data.

Se a consulta estiver em execução, DBCC PDW_SHOWEXECUTIONPLAN poderá ser usado para recuperar o plano estimado do SQL Server do cache do plano do SQL Server para a Etapa de SQL em execução no momento em uma distribuição específica.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);

Monitorar as consultas em esperaMonitor waiting queries

Caso você descubra que sua consulta não está fazendo progresso porque está aguardando um recurso, veja a seguir uma consulta que mostra todos os recursos que uma consulta está aguardando.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 a consulta estiver ativamente aguardando recursos de outra consulta, o estado será AcquireResources.If the query is actively waiting on resources from another query, then the state will be AcquireResources. Se a consulta tiver todos os recursos necessários, o estado será Concedido.If the query has all the required resources, then the state will be Granted.

Monitorar o tempdbMonitor tempdb

O tempdb é usado para manter resultados intermediários durante a execução da consulta.Tempdb is used to hold intermediate results during query execution. A alta utilização do banco de dados tempdb pode levar a um desempenho de consulta lento.High utilization of the tempdb database can lead to slow query performance. Cada nó no Azure SQL Data Warehouse tem aproximadamente 1 TB de espaço bruto para tempdb.Each node in Azure SQL Data Warehouse has approximately 1 TB of raw space for tempdb. Abaixo estão as dicas para monitorar o uso do tempdb e para diminuir o uso do tempdb em suas consultas.Below are tips for monitoring tempdb usage and for decreasing tempdb usage in your queries.

Monitorando tempdb com exibiçõesMonitoring tempdb with views

Para monitorar o uso do tempdb, primeiro instale o modo de exibição Microsoft. vw_sql_requests do microsoft Toolkit para SQL data warehouse.To monitor tempdb usage, first install the microsoft.vw_sql_requests view from the Microsoft Toolkit for SQL Data Warehouse. Em seguida, você pode executar a consulta a seguir para ver o uso de tempdb por nó para todas as consultas executadas:You can then execute the following query to see the tempdb usage per node for all executed queries:

-- 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 microsoft.vw_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;

Se você tiver uma consulta que esteja consumindo uma grande quantidade de memória ou tenha recebido uma mensagem de erro relacionada à alocação de tempdb, isso pode ser devido a uma CREATE TABLE muito grande, como SELECT (CTAS) ou a instrução Insert Select em execução que está falhando na operação de movimentação de dados final.If you have a query that is consuming a large amount of memory or have received an error message related to allocation of tempdb, it could be due to a very large CREATE TABLE AS SELECT (CTAS) or INSERT SELECT statement running that is failing in the final data movement operation. Normalmente, isso pode ser identificado como uma operação ShuffleMove no plano de consulta distribuída logo antes da seleção de inserção final.This can usually be identified as a ShuffleMove operation in the distributed query plan right before the final INSERT SELECT. Use Sys. dm_pdw_request_steps para monitorar operações ShuffleMove.Use sys.dm_pdw_request_steps to monitor ShuffleMove operations.

A mitigação mais comum é quebrar sua CTAS ou inserir instrução SELECT em várias instruções Load para que o volume de dados não exceda o limite de 1 TB por nó de tempdb.The most common mitigation is to break your CTAS or INSERT SELECT statement into multiple load statements so the data volume will not exceed the 1TB per node tempdb limit. Você também pode dimensionar o cluster para um tamanho maior que espalhará o tamanho do tempdb em mais nós, reduzindo o tempdb em cada nó individual.You can also scale your cluster to a larger size which will spread the tempdb size across more nodes reducing the tempdb on each individual node.

Além das instruções CTAS e INSERT SELECT, as consultas grandes e complexas em execução com memória insuficiente podem ser despejadas no tempdb, causando a falha das consultas.In addition to CTAS and INSERT SELECT statements, large, complex queries running with insufficient memory can spill into tempdb causing queries to fail. Considere a execução com uma classe de recursos maior para evitar o despejo em tempdb.Consider running with a larger resource class to avoid spilling into tempdb.

Monitorar a memóriaMonitor memory

A memória pode ser a causa raiz de problemas de desempenho lento e memória insuficiente.Memory can be the root cause for slow performance and out of memory issues. Considere a possibilidade de dimensionar o data warehouse se descobrir que o uso de memória do SQL Server está atingindo seus limites durante a execução de consulta.Consider scaling your data warehouse if you find SQL Server memory usage reaching its limits during query execution.

A seguinte consulta retorna o uso e a demanda de memória do SQL Server por nó: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)'

Monitorar o tamanho do log de transaçõesMonitor transaction log size

A consulta a seguir retorna o tamanho do log de transações em cada distribuição.The following query returns the transaction log size on each distribution. Se um dos arquivos de log estiver alcançando 160 GB, você deverá considerar a possibilidade de escalar verticalmente a instância ou limitar o tamanho da transação.If one of the log files is reaching 160 GB, 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)'

Monitorar a reversão do log de transaçõesMonitor transaction log rollback

Se as consultas estiverem falhando ou demorando muito para continuar, verifique e monitore se há transações sendo revertidas.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]

Monitorar o carregamento do polybaseMonitor PolyBase load

A consulta a seguir fornece uma estimativa aproximada do progresso da carga.The following query provides a ballpark estimate of the progress of your load. A consulta mostra apenas os arquivos que estão sendo processados no momento.The query only shows files currently being processed.


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

Próximos passosNext steps

Para obter mais informações sobre DMVs, consulte exibições do sistema.For more information about DMVs, see System views.