Compartilhar via

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

  • Artigo

Aplica-se ao: 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 execute as etapas no notebook por meio do Azure Data Studio. As três primeiras etapas orientam você na coleta de telemetria, que descreve o ciclo de vida de uma consulta. As referências no final do artigo ajudam 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 em seu computador local. Para instalá-lo, acesse Saiba como instalar o Azure Data Studio.

Abrir o notebook no Azure Data Studio

Importante

A maioria dos problemas de desempenho relatados é causada por:

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

Para economizar tempo de solução de problemas, certifique-se de que as estatísticas sejam criadas e atualizadas e que as CCIs tenham sido recriadas.

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

O request_id da consulta lenta é necessário para pesquisar possíveis motivos para uma consulta lenta. Use o script a seguir como ponto de partida para identificar a consulta que você deseja solucionar. Depois que a consulta lenta for identificada, anote 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 por um ou submit_time DESC total_elapsed_time DESC para ter as consultas de execução mais longa presentes na parte superior do conjunto de resultados.

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

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

    Observação: 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á demorando

Execute o script a seguir para localizar 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 da Description 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 da 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: revisar os 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 atualmente ativas para o valor fornecido 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 mitigar

Problemas da fase de compilação

Bloqueado: Simultaneidade de compilação

Simultaneidade Os blocos de compilação raramente ocorrem. No entanto, se você encontrar esse tipo de bloco, isso significa que um grande volume de consultas foi enviado em um curto espaço de tempo e foi enfileirado 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 na atribuição de grupo de carga de trabalho associada ao usuário.
  • A quantidade de memória disponível no sistema ou grupo de carga de trabalho.
  • (Opcional) A importância do grupo/classificador de carga de trabalho.

Mitigações

  • Aguarde a sessão de bloqueio ser concluída.
  • Avalie a escolha da classe de recurso. Para obter mais informações, consulte limites de simultaneidade.
  • Avalie se é preferível encerrar a sessão de bloqueio.
Consulta complexa ou sintaxe JOIN mais antiga

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

  • Envolve um alto número de junções e/ou subconsultas (consulta complexa).
  • Utiliza junções na FROM cláusula (não junções no 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 o otimizador de consulta escolher um plano.

Mitigações

  • Use junções de estilo ANSI-92.
  • Adicione dicas de consulta: OPTION(FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')). Para obter mais informações, consulte FORCE ORDER e estimativa de cardinalidade (SQL Server).
  • Divida a consulta em várias etapas menos complexas.
DROP TABLE ou TRUNCATE TABLE de longa duração

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

Mitigações

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


CCIs insalubres (geralmente)

A integridade ruim do CCI (índice columnstore clusterizado) 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, certifique-se de que todos os seus CCIs estejam em boas condições de saúde.

Mitigações

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


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

A opção de criação automática de estatísticas é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 próprio processo de criação automática pode fazer com que uma consulta inicial demore mais do que as execuções subsequentes da mesma.

Mitigações

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


Criar automaticamente tempos limite de estatísticas

A opção de criação automática de estatísticas é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ída. Se o tamanho dos dados e/ou o número de estatísticas a serem criadas exigirem 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 ao criar as estatísticas pode afetar negativamente a capacidade do otimizador de consulta de gerar um plano de execução distribuído eficiente, resultando em baixo desempenho de consulta.

Mitigações

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

Problemas da fase de execução

  • Use a tabela a seguir para analisar o conjunto de resultados na Etapa 2. Determine seu cenário e verifique a causa comum para obter 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 armazenadas em cache
    1. @ShowActiveOnly = 0
    2. Observa-se um número alto ou inesperado de passos (step_index).
    3. Os tipos de dados das colunas de junção não são idênticos entre as tabelas.    		 Tipo/tamanho de dados incompatível
    1. O Description valor indica HadoopBroadcastOperation, HadoopRoundRobinOperation ou HadoopShuffleOperation.
    2. O total_elapsed_time valor de um dado step_index é inconsistente entre as 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 para cada tabela referenciada TSQL no campo para associado step_id executando o seguinte comando em cada uma:

      DBCC PDW_SHOWSPACEUSED(<table>);

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

    3. Caso contrário, vá para Distorção de dados em trânsito.

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 armazenadas em cache

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

Mitigações

  • Aqueça o cache replicado após 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 associação correspondem. Caso contrário, isso resultará em movimentos de dados desnecessários que diminuirão a disponibilidade de CPU, E/S e tráfego de rede para o restante da carga de trabalho.

Mitigações

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


Consultas de tabela externa ad hoc

As consultas em 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 E/S, não pode ser alcançado, resultando em tempos de execução mais lentos.

Mitigações

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


Distorção de dados em voo

A distorção de dados em trânsito é 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 operação de ShuffleMoveOperation tipo. Essa operação produz uma saída distorcida a ser consumida downstream.

Mitigações

  • Certifique-se de que as estatísticas sejam criadas e atualizadas.
  • Altere a ordem das colunas GROUP BY para iniciar com uma coluna de cardinalidade mais alta.
  • Crie estatísticas de várias colunas se as junções abrangerem várias colunas.
  • Adicione uma dica OPTION(FORCE_ORDER) de consulta à sua consulta.
  • Refatorar a consulta.

Problemas de tipo de espera

Se nenhum dos problemas comuns acima se aplicar à sua consulta, os dados da Etapa 3 oferecem a oportunidade de determinar quais tipos de espera (in wait_type e wait_time) estão interferindo no processamento da 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 que está demorando mais na Etapa 3 .
  2. Localize o tipo de espera na tabela de mapeamento de categorias de espera e identifique a categoria de espera na qual ele está incluído.
  3. Expanda a seção relacionada à categoria de espera na lista a seguir para obter as mitigações recomendadas.
Compilação

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

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

Se o problema persistir, então:

  1. Crie um arquivo .sql com:

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

  2. Abra uma janela do prompt de comando e execute o seguinte 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 e cole 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 .

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

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

Bloqueio, Thread de Trabalho
  • Considere alterar tabelas que passam por alterações pequenas e frequentes para utilizar um índice de repositório de linhas em vez de CCI.
  • Agrupe suas alterações e atualize o destino com mais linhas com menos frequência.
E/S de buffer, E/S de outro disco, E/S de log de transferência

CCIs insalubres

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

Estatísticas desatualizadas

Estatísticas desatualizadas podem causar a geração de um plano distribuído não otimizado, o 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 no desempenho podem ser sentidos por toda a carga de trabalho.

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

Cargas de trabalho pesadas de E/S

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

CPU, paralelismo
Cenário Atenuação
Integridade insatisfatória do CCI Avalie e corrija a integridade do índice columnstore clusterizado em um pool de SQL dedicado
As consultas de usuário contêm transformações Mova todas as formatações e outras lógicas de transformação para processos de ETL para que as versões formatadas sejam armazenadas
Carga de trabalho priorizada incorretamente Implementar o isolamento da carga de trabalho
DWU insuficiente para a carga de trabalho Considere aumentar os recursos de computação

E/S 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 ao pool de SQL dedicado. Para tentar atenuar rapidamente esse problema, siga estas etapas:

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

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