Monitore sua carga de trabalho dedicada do pool SQL do Azure Synapse Analytics usando DMVs

Este artigo descreve como usar DMVs (Exibições de Gerenciamento Dinâmico) para monitorar sua carga de trabalho, incluindo a investigação da execução de consultas em um pool SQL dedicado.

Permissões

Para consultar os DMVs neste artigo, você precisa da permissão VIEW DATABASE STATE ou CONTROL . Normalmente, conceder VIEW DATABASE STATE é a permissão preferida, pois é muito mais restritiva.

GRANT VIEW DATABASE STATE TO myuser;

Monitorar conexões

Todos os logins no seu armazém de dados são registados no sys.dm_pdw_exec_sessions. Este Detran contém os últimos 10.000 logins. A session_id é a chave primária e é atribuída sequencialmente para cada novo login.

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

Monitorar a execução de consultas

Todas as consultas executadas no pool SQL são registradas em sys.dm_pdw_exec_requests. Este Detran contém as últimas 10.000 consultas executadas. O request_id identifica exclusivamente cada consulta e é a chave primária para este Detran. O request_id é atribuído sequencialmente para cada nova consulta e é prefixado com QID, que significa ID de consulta. Consultar este Detran para um determinado session_id mostra todas as consultas para um determinado login.

Nota

Os procedimentos armazenados usam várias IDs de solicitação. Os IDs de solicitação são atribuídos em ordem sequencial.

Seguem-se os passos a seguir para investigar os planos de execução da consulta e os horários de uma consulta específica.

Passo 1: Identifique a consulta que pretende investigar

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

A partir dos resultados da consulta anterior, anote a ID da solicitação da consulta que você gostaria de investigar.

As consultas no estado Suspenso podem ser enfileiradas devido a um grande número de consultas ativas em execução. Essas consultas também aparecem no sys.dm_pdw_waits. Nesse caso, procure por esperas como UserConcurrencyResourceType. Para obter informações sobre limites de simultaneidade, consulte Limites de memória e simultaneidade ou Classes de recursos para gerenciamento de carga de trabalho. As consultas também podem aguardar por outros motivos, como bloqueios de objetos. Se sua consulta estiver aguardando um recurso, consulte Investigando consultas aguardando recursos mais abaixo neste artigo.

Para simplificar a pesquisa de uma consulta na tabela sys.dm_pdw_exec_requests , use LABEL para atribuir um comentário à sua consulta, que pode ser pesquisado sys.dm_pdw_exec_requests na exibição.

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

Etapa 2: Investigar o plano de consulta

Use a ID da solicitação para recuperar o plano SQL distribuído (DSQL) da consulta do 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 está demorando mais do que o esperado, a causa pode ser um plano complexo com muitas etapas DSQL ou apenas uma etapa demorando muito tempo. 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. O artigo Distribuição de tabela explica por que os dados devem ser movidos para resolver uma consulta. O artigo também explica algumas estratégias de distribuição para minimizar a movimentação de dados.

Para investigar mais detalhes sobre uma única etapa, inspecione a operation_type coluna da etapa de consulta de longa duração e anote o Índice da etapa:

• Para operações SQL (OnOperation, RemoteOperation, ReturnOperation), prossiga com a ETAPA 3
• Para operações de Movimentação de Dados (ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation), prossiga com o PASSO 4.

Etapa 3: Investigar o SQL nos bancos de dados distribuídos

Use a ID da Solicitação e o Índice da Etapa para recuperar detalhes do sys.dm_pdw_sql_requests, que contém informações de execução da etapa de consulta em todos os bancos de dados distribuídos.

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

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

Etapa 4: Investigar a movimentação de dados nos bancos de dados distribuídos

Use a ID da solicitação e o índice da etapa para recuperar informações sobre uma etapa de movimentação de dados em execução em cada distribuição do 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;

• Verifique a total_elapsed_time coluna para ver se uma determinada distribuição está demorando significativamente mais do que outras para a movimentação de dados.
• Para a distribuição de longa duração, verifique a rows_processed coluna para ver se o número de linhas que estão sendo movidas dessa distribuição é significativamente maior do que outras. Em caso afirmativo, essa descoberta pode indicar distorção dos dados subjacentes. Uma causa para a distorção de dados é a distribuição em uma coluna com muitos valores NULL (cujas linhas serão todas colocadas na mesma distribuição). Evite consultas lentas evitando a distribuição nesses tipos de colunas ou filtrando sua consulta para eliminar NULLs quando possível.

Se a consulta estiver em execução, você poderá usar o DBCC PDW_SHOWEXECUTIONPLAN para recuperar o plano estimado do SQL Server do cache de plano do SQL Server para a Etapa SQL em execução no momento dentro de uma distribuição específica.

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

Monitorar consultas em espera

Se você descobrir que sua consulta não está progredindo porque está aguardando um recurso, aqui está uma consulta que mostra todos os recursos que uma consulta está esperando.

-- 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 aguardando ativamente recursos de outra consulta, o estado será AcquireResources. Se a consulta tiver todos os recursos necessários, o estado será concedido.

Monitorizar tempdb

O tempdb banco de dados é usado para armazenar resultados intermediários durante a execução da consulta. A alta utilização do tempdb banco de dados pode levar a um desempenho lento da consulta. Para cada DW100c configurado, 399 GB de espaço é alocado tempdb (DW1000c teria 3,99 TB de espaço total tempdb ). Abaixo estão dicas para monitorar tempdb o uso e diminuir tempdb o uso em suas consultas.

Monitore tempdb com modos de exibição

Para monitorar tempdb o uso, primeiro instale a exibição de microsoft.vw_sql_requests do pool Microsoft Toolkit for SQL. Em seguida, você pode executar a seguinte consulta para ver o tempdb uso por nó para todas as consultas executadas:

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

Nota

O Data Movement usa o tempdb. Para reduzir o uso de dados durante a movimentação, verifique se a tabela está usando uma estratégia de tempdb distribuição que distribui os dados uniformemente. Use o Azure Synapse SQL Distribution Advisor para obter recomendações sobre o método de distribuição adequado para suas cargas de trabalho. Use o Kit de Ferramentas do Azure Synapse para monitorar tempdb o uso de consultas T-SQL.

Se você tiver uma consulta que está consumindo uma grande quantidade de memória ou recebeu uma mensagem de erro relacionada à alocação do , isso pode ser devido a uma instrução CREATE TABLE AS SELECT (CTAS) ou INSERT SELECT muito grande em execução que está falhando na operação final de movimentação de tempdbdados. Isso geralmente pode ser identificado como uma operação ShuffleMove no plano de consulta distribuído antes do INSERT SELECT final. Use sys.dm_pdw_request_steps para monitorar operações ShuffleMove.

A atenuação mais comum é dividir sua instrução CTAS ou INSERT SELECT em várias instruções de carga para que o volume de dados não exceda o limite de 399 GB por 100DWUc tempdb . Você também pode dimensionar seu cluster para um tamanho maior para aumentar a quantidade de tempdb espaço que você tem.

Além das instruções CTAS e INSERT SELECT, consultas grandes e complexas em execução com memória insuficiente podem se espalhar para tempdb fazer com que as consultas falhem. Considere executar com uma classe de recursos maior para evitar o derramamento no tempdb.

Monitorizar memória

A memória pode ser a causa raiz para o desempenho lento e problemas de falta de memória. Considere dimensionar seu data warehouse se você achar que o uso de memória do SQL Server atingiu seus limites durante a execução da consulta.

A consulta a seguir retorna o uso de memória do SQL Server e a pressão de memória por nó:

-- 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ções

A consulta a seguir retorna o tamanho do log de transações em cada distribuição. Se um dos arquivos de log estiver atingindo 160 GB, considere aumentar a escala da instância ou limitar o tamanho da transação.

-- 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ções

Se suas consultas estiverem falhando ou demorando muito tempo para prosseguir, você poderá verificar e monitorar se há alguma reversão de transações.

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

Monitore a carga do PolyBase

A consulta a seguir fornece uma estimativa aproximada do progresso da sua carga. A consulta mostra apenas os ficheiros que estão a ser processados.

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

Monitorar bloqueios de consultas

A consulta a seguir fornece as 500 principais consultas bloqueadas no 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;

Recuperar texto de consulta de consultas de espera e bloqueio

A consulta a seguir fornece o texto e o identificador da consulta para que as consultas em espera e de bloqueio solucionem problemas facilmente.

-- 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,
       waiting.object_name AS Waiting_Object_Name,
       waiting.object_type AS Waiting_Object_Type,
       blocking.session_id AS BlockingSessionId,
       blocking.request_id AS BlockingRequestId,
       COALESCE(blocking_exec_request.command,blocking_exec_request.command2) AS BlockingExecRequestText,
       blocking.object_name AS Blocking_Object_Name,
       blocking.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;

Próximos passos

• Para obter mais informações sobre DMVs, consulte Exibições do sistema.