Solucionar problemas de uma consulta lenta em um pool de SQL dedicado
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.
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 outotal_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 alabel
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 StepIndex
valores , Phase
e 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
De acordo com os
Description
valores obtidos na Etapa 2, marcar a seção relevante para obter mais informações da tabela a seguir.Descrição Causa Comum Compilation Concurrency
Bloqueado: Simultaneidade de Compilação Resource Allocation (Concurrency)
Bloqueado: alocação de recursos Se a consulta estiver em "Execução" status identificada na Etapa 1, mas não houver informações de etapa na Etapa 2, marcar a causa que melhor se encaixa em seu cenário para obter mais informações da tabela a seguir.
Cenário Causa Comum A instrução contém lógica complexa de filtro de junção ou executa junções na WHERE
cláusulaConsulta complexa ou sintaxe JOIN mais antiga Instrução é uma instrução ou TRUNCATE TABLE
de execução longaDROP TABLE
TABELA DROP de execução longa ou TRUNCATE Os CCIs têm um alto percentual de linhas excluídas ou abertas (consulte Otimizando índices columnstore clusterizados) CCIs não íntegros (geralmente) Analise o conjunto de resultados na Etapa 1 para uma ou mais
CREATE STATISTICS
instruções executadas imediatamente após o envio lento da consulta. Verifique a causa que melhor se encaixa no cenário da tabela a seguir.Cenário Causa Comum Estatísticas criadas inesperadamente Atraso das estatísticas de criação automática Falha na criação de estatísticas após 5 minutos Tempo limite de estatísticas de criação automática
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
- Aguarde a conclusão da sessão de bloqueio.
- Avalie a escolha da classe de recurso. Para obter mais informações, confira Limites de simultaneidade.
- Avalie se é preferível matar a sessão de bloqueio.
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 indicaBroadcastMoveOperation
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 indicaHadoopBroadcastOperation
,HadoopRoundRobinOperation
ouHadoopShuffleOperation
.
2. Ototal_elapsed_time
valor de um determinadostep_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:Verifique a distribuição de dados de cada tabela referenciada no
TSQL
campo associadastep_id
executando o seguinte comando em cada:DBCC PDW_SHOWSPACEUSED(<table>);
Se <o valor> mínimo das linhas/<o valor>> máximo das linhas for 0,1, vá para Distorção de dados (armazenado).
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
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:
- Identifique o
wait_type
na Etapa 3 que está levando mais tempo. - 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.
- 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:
- Recompilar índices para todos os objetos envolvidos na consulta problemática.
- Atualize as estatísticas sobre todos os objetos envolvidos na consulta problemática.
- Teste novamente a consulta problemática para validar se o problema persiste.
Se o problema persistir, então:
Criar um arquivo .sql com:
SET QUERY_DIAGNOSTICS ON; <Your_SQL>; SET QUERY_DIAGNOSTICS OFF;
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
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.
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:
- Avalie e corrija a integridade do índice columnstore clusterizado em um pool de SQL dedicado.
- Execute e examine a saída da consulta listada em Otimizando índices columnstore clusterizados para obter uma linha de base.
- Siga as etapas para recompilar índices para melhorar a qualidade do segmento, visando as tabelas envolvidas na consulta de problema de exemplo.
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:
- Utilizando uma classe de recurso maior para suas consultas.
- Aumente os recursos de computação.
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:
- Dimensionar seu pool de SQL dedicado para DW100c
- 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).