Monitorizar a carga de trabalho do conjunto de SQL dedicado do Azure Synapse Analytics com DMVs

Artigo
06/07/2023

Este artigo descreve como utilizar Vistas de Gestão Dinâmica (DMVs) para monitorizar a carga de trabalho, incluindo investigar a execução de consultas num conjunto de SQL dedicado.

Permissões

Para consultar as DMVs neste artigo, precisa da permissão VER ESTADO DA BASE DE DADOS ou CONTROLO . Normalmente, conceder VIEW DATABASE STATE é a permissão preferencial, uma vez que é muito mais restritiva.

GRANT VIEW DATABASE STATE TO myuser;

Monitorizar ligações

Todos os inícios de sessão no seu armazém de dados são registados no sys.dm_pdw_exec_sessions. Esta DMV contém os últimos 10 000 inícios de sessão. É session_id a chave primária e é atribuída sequencialmente para cada novo início de sessão.

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

Monitorizar a execução de consultas

Todas as consultas executadas no conjunto de SQL são registadas no sys.dm_pdw_exec_requests. Esta DMV contém as últimas 10 000 consultas executadas. O request_id identifica exclusivamente cada consulta e é a chave primária para esta DMV. O request_id é atribuído sequencialmente para cada nova consulta e tem o prefixo QID, que significa ID de consulta. Consultar esta DMV para um determinado session_id mostra todas as consultas de um determinado início de sessão.

Nota

Os procedimentos armazenados utilizam vários IDs de Pedido. Os IDs do pedido são atribuídos por ordem sequencial.

Eis os passos a seguir para investigar os planos de execução de consultas e as horas de uma consulta específica.

Passo 1: identificar 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;

Nos resultados da consulta anterior, tenha em atenção o ID do Pedido da consulta que pretende investigar.

As consultas no estado Suspenso podem ser colocadas em fila devido a um grande número de consultas em execução ativas. Estas consultas também aparecem no sys.dm_pdw_waits. Nesse caso, procure esperas como UserConcurrencyResourceType. Para obter informações sobre os limites de simultaneidade, veja Limites de memória e simultaneidade ou Classes de recursos para gestão de cargas de trabalho. As consultas também podem aguardar por outros motivos, como bloqueios de objetos. Se a consulta estiver à espera de um recurso, veja Investigar consultas à espera de recursos mais abaixo neste artigo.

Para simplificar a pesquisa de uma consulta na tabela sys.dm_pdw_exec_requests , utilize LABEL para atribuir um comentário à sua consulta, que pode ser pesquisada na sys.dm_pdw_exec_requests vista.

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

Passo 2: Investigar o plano de consulta

Utilize o ID do Pedido para obter o plano de SQL (DSQL) distribuído 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á a demorar mais tempo do que o esperado, a causa pode ser um plano complexo com muitos passos DSQL ou apenas um passo a demorar muito tempo. Se o plano for um número de passos com várias operações de movimentação, considere otimizar as distribuições de tabelas para reduzir o movimento de dados. O artigo Distribuição de tabelas explica por que motivo os dados têm de ser movidos para resolver uma consulta. O artigo também explica algumas estratégias de distribuição para minimizar o movimento de dados.

Para investigar mais detalhes sobre um único passo, inspecione a operation_type coluna do passo de consulta de execução prolongada e anote o Índice de Passos:

Para operações SQL (OnOperation, RemoteOperation, ReturnOperation), avance para o PASSO 3
Para operações de Movimento de Dados (ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation), avance para o PASSO 4.

Passo 3: Investigar o SQL nas bases de dados distribuídas

Utilize o ID do Pedido e o Índice de Passos para obter detalhes de sys.dm_pdw_sql_requests, que contém informações de execução do passo de consulta em todas as bases de dados distribuídas.

-- 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 o passo de consulta está em execução, a PDW_SHOWEXECUTIONPLAN DBCC pode ser utilizada para obter o plano estimado SQL Server a partir da cache do plano SQL Server para o passo em execução numa 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);

Passo 4: Investigar o movimento de dados nas bases de dados distribuídas

Utilize o ID do Pedido e o Índice de Passos para obter informações sobre um passo de movimento de dados em execução em cada distribuição a partir de 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 distribuição específica está a demorar significativamente mais tempo do que outras para movimento de dados.
Para a distribuição de execução prolongada, verifique a rows_processed coluna para ver se o número de linhas que estão a ser movidas dessa distribuição é significativamente maior do que outras. Se for o caso, esta descoberta poderá indicar distorção dos dados subjacentes. Uma das causas da distorção de dados é a distribuição numa coluna com muitos valores NULL (cujas linhas irão ser todas distribuídas pela mesma distribuição). Evite consultas lentas ao evitar a distribuição nestes tipos de colunas ou filtrar a consulta para eliminar NULLs sempre que possível.

Se a consulta estiver em execução, pode utilizar o PDW_SHOWEXECUTIONPLAN DBCC para obter o SQL Server plano estimado a partir da cache do plano de SQL Server para o Passo sqL atualmente em execução numa 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);

Monitorizar consultas em espera

Se descobrir que a consulta não está a progredir porque está à espera de um recurso, eis uma consulta que mostra todos os recursos que uma consulta está à espera.

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

Monitorizar tempdb

A tempdb base de dados é utilizada para conter resultados intermédios durante a execução da consulta. A elevada utilização da tempdb base de dados pode levar a um desempenho de consulta lento. Para cada DW100c configurado, é alocado 399 GB de tempdb espaço (DW1000c teria 3,99 TB de espaço total tempdb ). Seguem-se sugestões para monitorizar a utilização tempdb e diminuir tempdb a utilização nas consultas.

Monitorizar a tempdb com vistas

Para monitorizar tempdb a utilização, instale primeiro a vista microsoft.vw_sql_requests a partir do Conjunto de ferramentas do Microsoft Toolkit para SQL. Em seguida, pode executar a seguinte consulta para ver a tempdb utilização por nó de 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 Movimento de Dados utiliza o tempdb. Para reduzir a utilização de durante o movimento de tempdb dados, certifique-se de que a tabela está a utilizar uma estratégia de distribuição que distribui os dados uniformemente. Utilize o Assistente de Distribuição do SQL do Azure Synapse para obter recomendações sobre o método de distrbution adequado para as suas cargas de trabalho. Utilize o Azure Synapse Toolkit para monitorizar tempdb com consultas T-SQL.

Se tiver uma consulta que esteja a consumir uma grande quantidade de memória ou tiver recebido uma mensagem de erro relacionada com a alocação de tempdb, pode dever-se a uma grande instrução CREATE TABLE AS SELECT (CTAS) ou INSERT SELECT em execução que está a falhar na operação de movimento de dados final. Geralmente, isto pode ser identificado como uma operação ShuffleMove no plano de consulta distribuído imediatamente antes do INSERT SELECT final. Utilize sys.dm_pdw_request_steps para monitorizar as operações shuffleMove.

A mitigação mais comum é dividir a 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 . Também pode dimensionar o cluster para um tamanho maior para aumentar a quantidade tempdb de espaço que tem.

Além das instruções CTAS e INSERT SELECT, as consultas grandes e complexas em execução com memória insuficiente podem transbordar tempdb para causar a falha das consultas. Considere executar com uma classe de recursos maior para evitar transpor para tempdb.

Monitorizar memória

A memória pode ser a causa principal para problemas de desempenho lento e sem memória. Considere dimensionar o armazém de dados se encontrar SQL Server utilização da memória a atingir os limites durante a execução da consulta.

A consulta seguinte devolve SQL Server utilização da memória e a pressão da 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)'

Monitorizar o tamanho do registo de transações

A consulta seguinte devolve o tamanho do registo de transações em cada distribuição. Se um dos ficheiros de registo estiver a atingir os 160 GB, deve considerar aumentar verticalmente a 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)'

Monitorizar a reversão do registo de transações

Se as consultas estiverem a falhar ou a demorar muito tempo a prosseguir, pode verificar e monitorizar se existem transações a reverter.

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

Monitorizar a carga do PolyBase

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

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

Monitorizar bloqueios de consultas

A consulta seguinte 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;

Obter texto da consulta a partir de consultas de espera e bloqueio

A consulta seguinte fornece o texto e o identificador da consulta para que as consultas de espera e bloqueio sejam facilmente resolução de problemas.

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

Passos seguintes

Para obter mais informações sobre DMVs, veja Vistas do sistema.