Solucionar problemas de uma consulta lenta em um pool de SQL dedicado

  • Artigo

Aplica-se a: Azure Synapse Analytics

Este artigo ajuda você a identificar os motivos e aplicar mitigações para problemas comuns de desempenho com consultas em um pool de SQL dedicado do Azure Synapse Analytics.

Siga as etapas para solucionar o problema ou executar as etapas no notebook por meio do Azure Data Studio. As três primeiras etapas percorrem a coleta de telemetria, que descreve o ciclo de vida de uma consulta. As referências no final do artigo ajudam você a analisar possíveis oportunidades encontradas nos dados coletados.

Observação

Antes de tentar abrir este notebook, verifique se o Azure Data Studio está instalado no computador local. Para instalá-lo, acesse Saiba como instalar o Azure Data Studio.

Abrir Notebook no Azure Data Studio

Importante

A maioria dos problemas de desempenho relatados são causados por:

  • Estatísticas desatualizadas
  • Índices de columnstore clusterizados não íntegros (CCIs)

Para economizar tempo de solução de problemas, verifique se as estatísticas foram criadas e atualizadas e as CCIs foram reconstruídas.

Etapa 1: identificar o request_id (também conhecido como QID)

O request_id da consulta lenta é necessário para pesquisar possíveis razões para uma consulta lenta. Use o script a seguir como ponto de partida para identificar a consulta que você deseja solucionar problemas. Depois que a consulta lenta for identificada, observe o request_id valor.

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [status] NOT IN ('Completed','Failed','Cancelled')
AND session_id <> session_id()
-- AND [label] = '<YourLabel>'
-- AND resource_allocation_percentage is not NULL
ORDER BY submit_time DESC;

-- Find top 10 longest running queries
SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC;

Para direcionar melhor as consultas lentas, use as seguintes dicas ao executar o script:

  • Classifique submit_time DESC por ou total_elapsed_time DESC para ter as consultas de execução mais longas presentes na parte superior do conjunto de resultados.

  • Use OPTION(LABEL='<YourLabel>') em suas consultas e, em seguida, filtre a label coluna para identificá-las.

  • Considere filtrar qualquer QIDs que não tenham um valor para resource_allocation_percentage quando você souber que a instrução de destino está contida em um lote.

    Nota: Tenha cuidado com esse filtro, pois ele também pode filtrar algumas consultas que estão sendo bloqueadas por outras sessões.

Etapa 2: determinar onde a consulta está levando tempo

Execute o script a seguir para encontrar a etapa que pode causar o problema de desempenho da consulta. Atualize as variáveis no script com os valores descritos na tabela a seguir. Altere o @ShowActiveOnly valor para 0 para obter a imagem completa do plano distribuído. Anote os StepIndexvalores , Phasee Description da etapa lenta identificada no conjunto de resultados.

Parâmetro Descrição
@QID O request_id valor obtido na Etapa 1
@ShowActiveOnly 0 – Mostrar todas as etapas para a consulta
1 – Mostrar apenas a etapa ativa no momento		 
DECLARE @QID VARCHAR(16) = '<request_id>', @ShowActiveOnly BIT = 1; 
-- Retrieve session_id of QID
DECLARE @session_id VARCHAR(16) = (SELECT session_id FROM sys.dm_pdw_exec_requests WHERE request_id = @QID);
-- Blocked by Compilation or Resource Allocation (Concurrency)
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
   'Blocked waiting on '
       + MAX(CASE WHEN waiting.type = 'CompilationConcurrencyResourceType' THEN 'Compilation Concurrency'
                  WHEN waiting.type LIKE 'Shared-%' THEN ''
                  ELSE 'Resource Allocation (Concurrency)' END)
       + MAX(CASE WHEN waiting.type LIKE 'Shared-%' THEN ' for ' + REPLACE(waiting.type, 'Shared-', '')
             ELSE '' END) AS [Description],
   MAX(waiting.request_time) AS [StartTime], GETDATE() AS [EndTime],
   DATEDIFF(ms, MAX(waiting.request_time), GETDATE())/1000.0 AS [Duration],
   NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount], NULL AS [TSQL]
FROM sys.dm_pdw_waits waiting
WHERE waiting.session_id = @session_id
   AND ([type] LIKE 'Shared-%' OR
      [type] in ('ConcurrencyResourceType', 'UserConcurrencyResourceType', 'CompilationConcurrencyResourceType'))
   AND [state] = 'Queued'
GROUP BY session_id 
-- Blocked by another query
UNION ALL
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
   'Blocked by ' + blocking.session_id + ':' + blocking.request_id + ' when requesting ' + waiting.type + ' on '
   + QUOTENAME(waiting.object_type) + waiting.object_name AS [Description],
   waiting.request_time AS [StartTime], GETDATE() AS [EndTime],
   DATEDIFF(ms, waiting.request_time, GETDATE())/1000.0 AS [Duration],
   NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount],
   COALESCE(blocking_exec_request.command, blocking_exec_request.command2) AS [TSQL]
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
WHERE waiting.session_id = @session_id AND waiting.state = 'Queued'
   AND blocking.state = 'Granted' AND waiting.type != 'Shared' 
-- Request Steps
UNION ALL
SELECT @session_id AS session_id, @QID AS request_id, step_index AS [StepIndex],
       'Execution' AS [Phase], operation_type + ' (' + location_type + ')' AS [Description],
       start_time AS [StartTime], end_time AS [EndTime],
       total_elapsed_time/1000.0 AS [Duration], [status] AS [Status],
       CASE WHEN estimated_rows > -1 THEN estimated_rows END AS [EstimatedRowCount],
       CASE WHEN row_count > -1 THEN row_count END AS [ActualRowCount],
       command AS [TSQL]
FROM sys.dm_pdw_request_steps
WHERE request_id = @QID
   AND [status] = CASE @ShowActiveOnly WHEN 1 THEN 'Running' ELSE [status] END
ORDER BY StepIndex;

Etapa 3: examinar detalhes da etapa

Execute o script a seguir para examinar os detalhes da etapa identificada na etapa anterior. Atualize as variáveis no script com os valores descritos na tabela a seguir. Altere o @ShowActiveOnly valor para 0 para comparar todos os tempos de distribuição. Anote o wait_type valor da distribuição que pode causar o problema de desempenho.

Parâmetro Descrição
@QID O request_id valor obtido na Etapa 1
@StepIndex O StepIndex valor identificado na Etapa 2
@ShowActiveOnly 0 – Mostrar todas as distribuições para o valor fornecido StepIndex
1 – Mostrar apenas as distribuições ativas atualmente para o valor determinado StepIndex		 
DECLARE @QID VARCHAR(16) = '<request_id>', @StepIndex INT = <StepIndex>, @ShowActiveOnly BIT = 1;
WITH dists
AS (SELECT request_id, step_index, 'sys.dm_pdw_sql_requests' AS source_dmv,
       distribution_id, pdw_node_id, spid, 'NativeSQL' AS [type], [status],
       start_time, end_time, total_elapsed_time, row_count
    FROM sys.dm_pdw_sql_requests
    WHERE request_id = @QID AND step_index = @StepIndex
    UNION ALL
    SELECT request_id, step_index, 'sys.dm_pdw_dms_workers' AS source_dmv,
       distribution_id, pdw_node_id, sql_spid AS spid, [type],
       [status], start_time, end_time, total_elapsed_time, rows_processed as row_count
    FROM sys.dm_pdw_dms_workers
    WHERE request_id = @QID AND step_index = @StepIndex
   )
SELECT sr.step_index, sr.distribution_id, sr.pdw_node_id, sr.spid,
       sr.type, sr.status, sr.start_time, sr.end_time,
       sr.total_elapsed_time, sr.row_count, owt.wait_type, owt.wait_time
FROM dists sr
   LEFT JOIN sys.dm_pdw_nodes_exec_requests owt
      ON sr.pdw_node_id = owt.pdw_node_id
         AND sr.spid = owt.session_id
         AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests'
                 AND sr.status = 'Running') -- sys.dm_pdw_sql_requests status
              OR (sr.source_dmv = 'sys.dm_pdw_dms_requests'
                     AND sr.status not LIKE 'Step[CE]%')) -- sys.dm_pdw_dms_workers final statuses
WHERE sr.request_id = @QID
      AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests' AND sr.status =
               CASE WHEN @ShowActiveOnly = 1 THEN 'Running' ELSE sr.status END)
           OR (sr.source_dmv = 'sys.dm_pdw_dms_workers' AND sr.status NOT LIKE
                  CASE WHEN @ShowActiveOnly = 1 THEN 'Step[CE]%' ELSE '' END))
      AND sr.step_index = @StepIndex
ORDER BY distribution_id

Etapa 4: Diagnosticar e atenuar

Problemas de fase de compilação

Bloqueado: Simultaneidade de Compilação

Os blocos de compilação de simultaneidade raramente ocorrem. No entanto, se você encontrar esse tipo de bloco, ele significa que um grande volume de consultas foram enviadas em pouco tempo e foram enfileiradas para iniciar a compilação.

Mitigações

Reduza o número de consultas enviadas simultaneamente.


Bloqueado: alocação de recursos

Ser bloqueado para alocação de recursos significa que sua consulta está aguardando para ser executada com base em:

  • A quantidade de memória concedida com base na classe de recurso ou atribuição do grupo de carga de trabalho associada ao usuário.
  • A quantidade de memória disponível no sistema ou no grupo de carga de trabalho.
  • (Opcional) A importância do grupo de carga de trabalho/classificador.

Mitigações

Consulta complexa ou sintaxe JOIN mais antiga

Você pode encontrar uma situação em que os métodos de otimizador de consulta padrão se mostram ineficazes, pois a fase de compilação leva muito tempo. Pode ocorrer se a consulta:

  • Envolve um alto número de junções e/ou subconsultas (consulta complexa).
  • Utiliza juntores na FROM cláusula (não junções de estilo ANSI-92).

Embora esses cenários sejam atípicos, você tem opções para tentar substituir o comportamento padrão para reduzir o tempo necessário para que o otimizador de consulta escolha um plano.

Mitigações

  • Use junções de estilo ANSI-92.
  • Adicionar dicas de consulta: OPTION(FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')). Para obter mais informações, consulte FORCE ORDER e Cardinality Estimation (SQL Server).
  • Quebre a consulta em várias etapas menos complexas.
TABELA DROP de execução longa ou TRUNCATE

Para eficiências de tempo de execução, as DROP TABLE instruções e TRUNCATE TABLE adiarão a limpeza do armazenamento para um processo em segundo plano. No entanto, se sua carga de trabalho executar um alto número de DROP/TRUNCATE TABLE instruções em um curto período de tempo, é possível que os metadados fiquem lotados e faça com que as instruções subsequentes DROP/TRUNCATE TABLE sejam executadas lentamente.

Mitigações

Identifique uma janela de manutenção, pare todas as cargas de trabalho e execute o DBCC SHRINKDATABASE para forçar uma limpeza imediata de tabelas descartadas ou truncadas anteriormente.


CCIs não íntegros (geralmente)

A integridade de CCI (índice CCI) de clusterizado ruim requer metadados extras, o que pode fazer com que o otimizador de consulta leve mais tempo para determinar um plano ideal. Para evitar essa situação, verifique se todas as suas CCIs estão em boa saúde.

Mitigações

Avalie e corrija a integridade do índice columnstore clusterizado em um pool de SQL dedicado.


Atraso das estatísticas de criação automática

A opção criar estatísticas automáticas éON, por padrão, AUTO_CREATE_STATISTICS para ajudar a garantir que o otimizador de consulta possa tomar boas decisões de plano distribuído. No entanto, o processo de criação automática em si pode fazer com que uma consulta inicial leve mais tempo do que as execuções subsequentes do mesmo.

Mitigações

Se a primeira execução da consulta exigir consistentemente que as estatísticas sejam criadas, você precisará criar estatísticas manualmente antes da execução da consulta.


Tempo limite de estatísticas de criação automática

A opção criar estatísticas automáticas éON, por padrão, AUTO_CREATE_STATISTICS para ajudar a garantir que o otimizador de consulta possa tomar boas decisões de plano distribuído. A criação automática de estatísticas ocorre em resposta a uma instrução SELECT e tem um limite de 5 minutos para ser concluído. Se o tamanho dos dados e/ou o número de estatísticas a serem criadas exigir mais do que o limite de 5 minutos, a criação automática de estatísticas será abandonada para que a consulta possa continuar a execução. A falha na criação das estatísticas pode afetar negativamente a capacidade do otimizador de consulta de gerar um plano de execução distribuído eficiente, resultando em um desempenho de consulta ruim.

Mitigações

Crie manualmente as estatísticas em vez de confiar no recurso de criação automática para as tabelas/colunas identificadas.

Problemas de fase de execução

  • Use a tabela a seguir para analisar o conjunto de resultados na Etapa 2. Determine seu cenário e marcar a causa comum para informações detalhadas e as possíveis etapas de mitigação.

    Cenário Causa Comum
    EstimatedRowCount/ActualRowCount< 25% Estimativas imprecisas
    O Description valor indica BroadcastMoveOperation e a consulta faz referência a uma tabela replicada. Tabelas replicadas não exibidas
    1. @ShowActiveOnly = 0
    2. O número alto ou inesperado de etapas (step_index) é observado.
    3. Os tipos de dados de colunas de junção não são idênticos entre tabelas.    		 Tipo/tamanho de dados incompatível
    1. O Description valor indica HadoopBroadcastOperation, HadoopRoundRobinOperation ou HadoopShuffleOperation.
    2. O total_elapsed_time valor de um determinado step_index é inconsistente entre execuções.    		 Consultas de tabela externa ad hoc

  • Verifique o total_elapsed_time valor obtido na Etapa 3. Se for significativamente maior em algumas distribuições em uma determinada etapa, siga estas etapas:

    1. Verifique a distribuição de dados de cada tabela referenciada no TSQL campo associada step_id executando o seguinte comando em cada:

      DBCC PDW_SHOWSPACEUSED(<table>);

    2. Se <o valor> mínimo das linhas/<o valor>> máximo das linhas for 0,1, vá para Distorção de dados (armazenado).

    3. Caso contrário, vá para a distorção de dados em voo.

Estimativas imprecisas

Tenha suas estatísticas atualizadas para garantir que o otimizador de consulta gere um plano ideal. Quando a contagem de linhas estimada é significativamente menor do que as contagens reais, as estatísticas precisam ser mantidas.

Mitigações

Criar/atualizar estatísticas.


Tabelas replicadas não exibidas

Se você tiver criado tabelas replicadas e não conseguir aquecer o cache de tabela replicado corretamente, o fraco desempenho inesperado resultará devido a movimentações extras de dados ou à criação de um plano distribuído suboptimal.

Mitigações

  • Aqueque o cache replicado após as operações DML.
  • Se houver operações DML frequentes, altere a distribuição da tabela para ROUND_ROBIN.
Tipo/tamanho de dados incompatível

Ao unir tabelas, verifique se o tipo de dados e o tamanho das colunas de junção correspondem. Caso contrário, isso resultará em movimentações de dados desnecessárias que diminuirão a disponibilidade de CPU, IO e tráfego de rede para o restante da carga de trabalho.

Mitigações

Recompile as tabelas para corrigir as colunas de tabela relacionadas que não têm tipo e tamanho de dados idênticos.


Consultas de tabela externa ad hoc

As consultas em relação a tabelas externas são projetadas com a intenção de carregar dados em massa no pool de SQL dedicado. Consultas ad hoc em tabelas externas podem sofrer durações variáveis devido a fatores externos, como atividades simultâneas de contêiner de armazenamento.

Mitigações

Carregue os dados no pool de SQL dedicado primeiro e, em seguida, consulte os dados carregados.


Distorção de dados (armazenada)

A distorção de dados significa que os dados não são distribuídos uniformemente entre as distribuições. Cada etapa do plano distribuído requer que todas as distribuições sejam concluídas antes de passar para a próxima etapa. Quando seus dados são distorcidos, todo o potencial dos recursos de processamento, como CPU e IO, não pode ser alcançado, resultando em tempos de execução mais lentos.

Mitigações

Examine nossas diretrizes para tabelas distribuídas para ajudar na escolha de uma coluna de distribuição mais apropriada.


Distorção de dados a bordo

A distorção de dados a bordo é uma variante do problema de distorção de dados (armazenado ). Mas não é a distribuição de dados no disco que é distorcida. A natureza do plano distribuído para filtros específicos ou dados agrupados causa uma ShuffleMoveOperation operação de tipo. Essa operação produz uma saída distorcida a ser consumida downstream.

Mitigações

  • Verifique se as estatísticas são criadas e atualizadas.
  • Altere a ordem de suas GROUP BY colunas para liderar com uma coluna de maior cardinalidade.
  • Crie estatísticas de várias colunas se as junções cobrirem várias colunas.
  • Adicione uma dica OPTION(FORCE_ORDER) de consulta à consulta.
  • Refatore a consulta.

Problemas de tipo de espera

Se nenhum dos problemas comuns acima se aplicar à consulta, os dados da Etapa 3 oferecerão a oportunidade de determinar quais tipos de espera (em e wait_time) estão interferindo no wait_type processamento de consulta para a etapa de execução mais longa. Há um grande número de tipos de espera e eles são agrupados em categorias relacionadas devido a mitigações semelhantes. Siga estas etapas para localizar a categoria de espera da etapa de consulta:

  1. Identifique o wait_type na Etapa 3 que está levando mais tempo.
  2. Localize o tipo de espera na tabela de mapeamento de categorias de espera e identifique a categoria de espera na qual ela foi incluída.
  3. Expanda a seção relacionada à categoria de espera da lista a seguir para mitigações recomendadas.
Compilação

Siga estas etapas para mitigar problemas de tipo de espera da categoria Compilação:

  1. Recompilar índices para todos os objetos envolvidos na consulta problemática.
  2. Atualize as estatísticas sobre todos os objetos envolvidos na consulta problemática.
  3. Teste novamente a consulta problemática para validar se o problema persiste.

Se o problema persistir, então:

  1. Criar um arquivo .sql com:

    SET QUERY_DIAGNOSTICS ON; <Your_SQL>; SET QUERY_DIAGNOSTICS OFF;

  2. Abra uma janela de Prompt de Comando e execute este comando:

    sqlcmd −S <servername>.database.windows.net −d <databasename> −U <username> −G −I −i .\<sql_file_name>.sql −y0 −o .\<output_file_name>.txt

  3. Abra <output_file_name>.txt em um editor de texto. Localize e copie os planos de execução no nível da distribuição (linhas que começam com <ShowPlanXML>) da etapa de execução mais longa identificada na Etapa 2 em arquivos de texto separados com uma extensão .sqlplan .

    Nota: Cada etapa do plano distribuído normalmente terá registrado 60 planos de execução em nível de distribuição. Verifique se você está preparando e comparando planos de execução da mesma etapa de plano distribuído.

  4. A consulta etapa 3 frequentemente revela algumas distribuições que demoram muito mais do que outras. Em SQL Server Management Studio, compare os planos de execução no nível de distribuição (dos arquivos .sqlplan criados) de uma distribuição de execução longa a uma distribuição em execução rápida para analisar possíveis causas de diferenças.

Lock, Worker Thread
  • Considere alterar tabelas que sofrem alterações frequentes e pequenas para utilizar um índice de repositório de linhas em vez de CCI.
  • Coloque em lote suas alterações e atualize o destino com mais linhas com menos frequência.
IO do buffer, outra IO de disco, IO de log de tran

CCIs não íntegros

CCIs não íntegros contribuem para o aumento da alocação de IO, CPU e memória, o que, por sua vez, afeta negativamente o desempenho da consulta. Para atenuar esse problema, experimente um dos seguintes métodos:

Estatísticas desatualizadas

Estatísticas desatualizadas podem causar a geração de um plano distribuído não otimizado, que envolve mais movimentação de dados do que o necessário. A movimentação desnecessária de dados aumenta a carga de trabalho não apenas em seus dados em repouso, mas também no tempdb. Como a E/S é um recurso compartilhado em todas as consultas, os impactos de desempenho podem ser sentidos por toda a carga de trabalho.

Para solucionar essa situação, verifique se todas as estatísticas estão atualizadas e um plano de manutenção está em vigor para mantê-las atualizadas para cargas de trabalho do usuário.

Cargas de trabalho de E/S pesadas

Sua carga de trabalho geral pode estar lendo grandes quantidades de dados. Os pools de SQL dedicados do Synapse dimensionam recursos de acordo com o DWU. Para obter um melhor desempenho, considere ou ambos:

CPU, Paralelismo
Cenário Atenuação
Saúde de CCI pobre Avaliar e corrigir a integridade do índice columnstore clusterizado em um pool de SQL dedicado
As consultas de usuário contêm transformações Mover toda a formatação e outra lógica de transformação em processos ETL para que as versões formatadas sejam armazenadas
Carga de trabalho priorizada incorretamente Implementar o isolamento da carga de trabalho
DWU insuficiente para carga de trabalho Considere aumentar os recursos de computação

IO de rede

Se o problema ocorrer durante uma RETURN operação na Etapa 2,

  • Reduza o número de processos paralelos simultâneos.
  • Dimensione o processo mais impactado para outro cliente.

Para todas as outras operações de movimentação de dados, é provável que os problemas de rede pareçam ser internos para o pool de SQL dedicado. Para tentar reduzir rapidamente esse problema, siga estas etapas:

  1. Dimensionar seu pool de SQL dedicado para DW100c
  2. Redimensionar para o nível de DWU desejado
SQL CLR

Evite o uso frequente da FORMAT() função implementando uma maneira alternativa de transformar os dados (por exemplo, CONVERT() com estilo).