Partilhar via

Tipos detetáveis de gargalos de desempenho de consultas no SQL Server e Azure SQL Managed Instance

Aplica-se a:SQL ServerAzure SQL Managed Instance

Ao tentar resolver um estrangulamento no desempenho, comece por determinar se o estrangulamento ocorre enquanto a consulta está no estado de execução ou no estado de espera. Diferentes resoluções aplicam-se consoante esta determinação. Problemas e soluções relacionados com cada tipo de problema são discutidos neste artigo.

Pode usar os DMVs do SQL Server para detetar este tipo de estrangulamentos de desempenho.

Problemas relacionados com a execução: Os problemas relacionados com a execução estão geralmente relacionados com problemas de compilação que resultam num plano de consulta subótimo ou problemas de execução relacionados com recursos insuficientes ou sobreutilizados. Problemas relacionados com a espera: Os problemas relacionados com a espera estão geralmente relacionados com:

  • Fechaduras (bloqueio)
  • I/O
  • Contenção relacionada ao tempdb uso
  • Espera pela atribuição de memória

Este artigo é sobre SQL Server e Azure SQL Managed Instance, veja também Tipos detetáveis de gargalos de desempenho de consultas na Azure SQL Database.

Problemas de compilação que resultam num plano de consulta subótimo

Um plano subótimo gerado pelo SQL Query Optimizer pode ser a causa de um desempenho lento nas consultas. O Otimizador de Consultas SQL pode produzir um plano subótimo devido a um índice em falta, estatísticas obsoletas, uma estimativa incorreta do número de linhas a processar ou uma estimativa imprecisa da memória necessária. Se souber que a consulta foi executada mais rapidamente no passado ou noutra instância, compare os planos de execução reais para ver se são diferentes.

O exemplo de ajuste e dicas de consultas no artigo Ajustar aplicações e bases de dados para desempenho mostra o impacto de um plano de consulta subótimo devido a uma consulta parametrizada, como detetar esta condição e como usar uma dica de consulta para resolver.

Resolução de consultas com planos de execução de consultas subótimos

As secções seguintes discutem como resolver consultas com um plano de execução de consultas subótimo.

Consultas que apresentam problemas de plano sensível a parâmetros (PSP)

Um problema de plano sensível a parâmetros (PSP) ocorre quando o otimizador de consultas gera um plano de execução de consulta que é ótimo apenas para um valor de parâmetro específico (ou conjunto de valores) e o plano em cache não é então ótimo para valores de parâmetros usados em execuções consecutivas. Planos que não são ótimos podem então causar problemas de desempenho nas consultas e degradar o rendimento total da carga de trabalho.

Para mais informações sobre deteção de parâmetros e processamento de consultas, consulte o guia de arquitetura de processamento de consultas.

Várias soluções alternativas podem mitigar os problemas da PSP. Cada solução alternativa tem compromissos e desvantagens associadas.

  • Uma nova funcionalidade introduzida com o SQL Server 2022 (16.x) é a otimização de Planos Sensíveis a Parâmetros, que tenta mitigar a maioria dos planos de consulta subótimos causados pela sensibilidade aos parâmetros. Isto está ativado com o nível de compatibilidade de base de dados 160.
  • Use a sugestão de consulta RECOMPILE em cada execução de consulta. Esta solução sacrifica tempo de compilação e aumento no uso da CPU para conseguir uma melhor qualidade do plano. A RECOMPILE opção muitas vezes não é possível para cargas de trabalho que exigem um alto rendimento.
  • Use a dica de consulta OPTION (OPTIMIZE FOR...) para substituir o valor real do parâmetro por um valor típico que produza um plano que seja bom o suficiente para a maioria das possibilidades de valores de parâmetro. Esta opção requer uma boa compreensão dos valores ótimos dos parâmetros e das características associadas do plano.
  • Use a dica de consulta OPTION (OPTIMIZE FOR UNKNOWN) para sobrescrever o valor real do parâmetro e usar, em vez disso, a média do vetor de densidade. Também pode fazer isto capturando os valores dos parâmetros de entrada em variáveis locais e depois usando as variáveis locais dentro dos predicados em vez de usar os próprios parâmetros. Para esta correção, a densidade média deve ser suficientemente boa.
  • Desative completamente o sniffing de parâmetros, utilizando a pista de consulta DISABLE_PARAMETER_SNIFFING.
  • Use a dica de consulta KEEPFIXEDPLAN para evitar recompilações na cache. Esta solução pressupõe que o plano comum suficientemente bom já é o que está na cache. Também pode desativar as atualizações automáticas de estatísticas para reduzir as hipóteses de o plano bom ser despejado e de um novo plano mau ser compilado.
  • Forçar o plano usando explicitamente a dica de consulta USE PLAN, reescrevendo a consulta e adicionando a dica no comando da consulta. Ou definir um plano específico usando a Loja de Consultas ou ativando a afinação automática.
  • Substitua o procedimento único por um conjunto aninhado de procedimentos que possam ser usados com base na lógica condicional e nos valores dos parâmetros associados.
  • Criar alternativas para a execução dinâmica de cadeias de texto em substituição a uma definição estática de procedimento.

Para aplicar dicas de consulta, modificar a consulta ou usar dicas da Loja de Consultas para aplicar a dica sem fazer alterações no código. Nas versões do SQL Server anteriores ao SQL Server 2022, utilize guias de plano.

Para mais informações sobre como resolver problemas na PSP, consulte estes artigos no blogue:

Atividade de compilação causada por parametrização incorreta

Quando uma consulta tem literais, o motor da base de dados parametriza automaticamente a instrução ou um utilizador parametriza explicitamente a declaração para reduzir o número de compilações. Um elevado número de compilações para uma consulta usando o mesmo padrão mas valores literais diferentes pode resultar numa elevada utilização da CPU. De forma semelhante, se parametrizar apenas parcialmente uma consulta que continua a ter literais, o motor da base de dados não a parametriza mais.

Aqui está um exemplo de uma consulta parcialmente parametrizada:

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 = @p1 AND t2.c2 = '961C3970-0E54-4E8E-82B6-5545BE897F8F';

Neste exemplo, t1.c1 toma @p1, mas t2.c2 continua a tomar GUID como literal. Neste caso, se alterar o valor para c2, a consulta é tratada como uma consulta diferente, e uma nova compilação ocorre. Para reduzir compilações neste exemplo, também parametrizarias o GUID.

A consulta seguinte mostra a contagem de consultas por hash de consulta para determinar se uma consulta está devidamente parametrizada:

SELECT TOP 10
  q.query_hash
  , count (distinct p.query_id ) AS number_of_distinct_query_ids
  , min(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
  JOIN sys.query_store_query AS q
     ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan AS p
     ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats AS rs
     ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
  rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
  AND query_parameterization_type_desc IN ('User', 'None')
GROUP BY q.query_hash
ORDER BY count (distinct p.query_id) DESC;

Fatores que afetam as alterações no plano de consulta

Uma recompilação de plano de execução de consulta pode resultar num plano de consulta gerado que difere do plano original em cache. Um plano original existente pode ser automaticamente recompilado por várias razões:

  • As alterações no esquema são referenciadas pela consulta
  • As alterações de dados nas tabelas são referenciadas pela consulta
  • As opções de contexto da consulta foram alteradas

Um plano compilado pode ser ejetado da cache por várias razões, tais como:

  • Reinício das instâncias
  • Alterações de configuração relativas à base de dados
  • Pressão da memória
  • Pedidos explícitos para limpar a cache

Se usar uma pista RECOMPILE, um plano não será armazenado em cache.

Uma recompilação (ou nova compilação após a eliminação da cache) pode ainda resultar na geração de um plano de execução de consulta idêntico ao original. Quando o plano muda em relação ao plano anterior ou original, estas explicações são prováveis:

  • Design físico alterado: Por exemplo, índices recém-criados cobrem de forma mais eficaz os requisitos de uma consulta. Os novos índices podem ser usados numa nova compilação se o otimizador de consultas decidir que usar esse novo índice é mais ótimo do que usar a estrutura de dados originalmente selecionada para a primeira versão da execução da consulta. Quaisquer alterações físicas aos objetos referenciados podem resultar numa nova escolha de plano no momento da compilação.

  • Diferenças de recursos de servidor: Quando um plano num sistema difere do plano noutro sistema, a disponibilidade de recursos, como o número de processadores disponíveis, pode influenciar qual o plano gerado. Por exemplo, se um sistema tiver mais processadores, pode ser escolhido um plano paralelo. Para mais informações sobre paralelismo, veja Configurar o grau máximo de paralelismo Opção de Configuração do Servidor.

  • Estatísticas diferentes: As estatísticas associadas aos objetos referenciados podem ter mudado ou ser materialmente diferentes das estatísticas do sistema original. Se as estatísticas mudarem e ocorrer uma recompilação, o otimizador de consultas usa as estatísticas a partir do momento em que mudaram. As distribuições e frequências dos dados das estatísticas revistas podem diferir das da compilação original. Estas alterações são usadas para criar estimativas de cardinalidade. (As estimativas de cardinalidade são o número de linhas que se espera que fluam através da árvore lógica de consultas.) Alterações nas estimativas de cardinalidade podem levá-lo a escolher diferentes operadores físicos e ordens de operações associadas. Mesmo pequenas alterações às estatísticas podem resultar numa alteração do plano de execução da consulta.

  • Alteração do nível de compatibilidade da base de dados ou da versão do estimador de cardinalidade: Alterações ao nível de compatibilidade da base de dados podem permitir novas estratégias e funcionalidades que podem resultar num plano de execução diferente da consulta. Para além do nível de compatibilidade da base de dados, um sinalizador de rastreio 4199, desativado ou ativado, ou um estado alterado da configuração em escopo de base de dados QUERY_OPTIMIZER_HOTFIXES também pode influenciar as escolhas do plano de execução da consulta em tempo de compilação. As flags de rastreamento 9481 (forçar CE legado) e 2312 (forçar CE padrão) também afetam o plano.

Problemas de limites de recursos no Azure SQL Managed Instance

O desempenho lento das consultas, não relacionado com planos de consulta subótimos, e índices em falta estão geralmente relacionados com recursos insuficientes ou sobreutilizados. Se o plano de consulta for ótimo, a consulta (e a base de dados) podem estar a atingir os limites de recursos para a instância gerida. Um exemplo pode ser o excesso de taxa de escrita de logs para o nível de serviço.

Se identificar o problema como recurso insuficiente, pode atualizar recursos para aumentar a capacidade da sua base de dados de absorver os requisitos da CPU. Para informações sobre a escalabilidade de uma instância gerida, consulte limites de recursos por nível de serviço

Problemas de desempenho causados pelo aumento do volume de carga de trabalho

Um aumento do tráfego de aplicações e do volume de carga de trabalho pode causar um aumento do uso da CPU. Mas deve ter cuidado para diagnosticar corretamente este problema. Quando vir um problema de CPU elevado, responda a estas perguntas para determinar se o aumento é causado por alterações no volume de carga de trabalho:

  • As consultas da aplicação são a causa do problema de uso elevado da CPU?

  • Para as consultas que consomem mais CPU e que consegues identificar:

    • Vários planos de execução estavam associados à mesma consulta? Se sim, porquê?
    • Para consultas com o mesmo plano de execução, os tempos de execução eram consistentes? O número de execuções aumentou? Se sim, o aumento da carga de trabalho provavelmente está a causar problemas de desempenho.

Em resumo, se o plano de execução da consulta não foi executado de forma diferente mas o uso da CPU aumentou juntamente com o número de execuções, o problema de desempenho provavelmente está relacionado com um aumento da carga de trabalho.

Nem sempre é fácil identificar uma alteração no volume da carga de trabalho que está a causar um problema de CPU. Considere estes fatores:

  • Uso alterado de recursos: Por exemplo, considere um cenário em que o uso da CPU aumentou para 80 por cento durante um período prolongado. O uso do CPU por si só não significa que o volume de trabalho tenha mudado. Regressões no plano de execução da consulta e alterações na distribuição dos dados também podem contribuir para um maior uso de recursos, mesmo que a aplicação execute a mesma carga de trabalho.

  • O aparecimento de uma nova consulta: Uma aplicação pode gerar um novo conjunto de consultas em momentos diferentes.

  • Um aumento ou diminuição no número de pedidos: Este cenário é a medida mais óbvia de uma carga de trabalho. O número de consultas nem sempre corresponde a uma maior utilização de recursos. No entanto, esta métrica continua a ser um sinal significativo, assumindo que outros fatores permanecem inalterados.

  • Paralelismo: O paralelismo excessivo pode piorar o desempenho de outras cargas de trabalho concorrentes ao privar outras consultas de recursos de CPU e de segmentos de trabalho. Para mais informações sobre paralelismo, veja Configurar o grau máximo de paralelismo Opção de Configuração do Servidor.

Depois de eliminar um plano subótimo e problemas de espera relacionados a problemas de execução, geralmente, o problema de desempenho está no facto de que as consultas provavelmente estão à espera de algum recurso. Problemas relacionados com a espera podem ser causados por:

  • Bloqueio:

    Uma consulta pode manter o bloqueio em objetos na base de dados enquanto outros utilizadores tentam aceder aos mesmos objetos. Pode identificar consultas de bloqueio com DMVs. Para obter mais informações, consulte Compreender e resolver problemas de bloqueio.

  • Problemas de IO

    As consultas podem estar à espera que as páginas sejam escritas nos ficheiros de registo ou de dados. Neste caso, verifique as estatísticas de espera INSTANCE_LOG_RATE_GOVERNOR, WRITE_LOGou PAGEIOLATCH_* no DMV. Veja como utilizar DMVs para Identificar problemas de desempenho de E/S.

  • Problemas de tempdb

    Se a carga de trabalho usar tabelas temporárias ou houver tempdb derrames nos planos, as consultas podem ter problemas de tempdb capacidade de processamento. Para investigar mais, veja Identificar problemas da tempdb.

  • Problemas relacionados com a memória

    Se a carga de trabalho não tiver memória suficiente, a esperança de vida da página pode baixar ou as consultas poderão ter menos memória do que a necessária. Em certos casos, a inteligência incorporada no Otimizador de Consultas corrigirá os problemas relacionados com a memória. Veja como utilizar DMVs para Identificar problemas de concessão de memória. Se encontrar erros de memória esgotada, veja sys.dm_os_out_of_memory_events. Considere também o escalão Otimizada para memória série Premium do hardware do Azure SQL Managed Instance com maiores rácios de memória para vCores.

Métodos para mostrar as principais categorias de espera

Estes métodos são frequentemente usados para mostrar as principais categorias de tipos de espera:

  • Use a Loja de Consultas para encontrar estatísticas de espera para cada consulta ao longo do tempo. No Query Store, os tipos de espera são combinados em categorias de espera. Podes encontrar o mapeamento das categorias de espera para os tipos de espera em sys.query_store_wait_stats.
  • Use sys.dm_os_wait_stats para devolver informações sobre todas as esperas encontradas por threads que foram executados durante uma operação de consulta. Pode usar esta vista agregada para diagnosticar problemas de desempenho com a Instância Gerida Azure SQL ou a instância SQL Server. As consultas podem ser à espera de recursos, esperas em fila ou esperas externas.
  • Use sys.dm_os_waiting_tasks para devolver informações sobre a fila de tarefas que estão à espera de algum recurso.

Em cenários de CPU elevada, as estatísticas de Loja de Consultas e de espera podem não refletir o uso da CPU se:

  • Consultas que consomem muito CPU continuam a ser executadas.
  • As consultas que consomem muitos recursos de CPU estavam em execução quando ocorreu um failover.

As DMVs que monitorizam o Query Store e estatísticas de espera mostram resultados apenas para consultas concluídas com sucesso e transações expiradas. Eles não mostram dados das instruções em execução até que as instruções terminem. Empregue a vista de gestão dinâmica sys.dm_exec_requests para acompanhar as consultas atualmente em execução e o tempo de execução associado.

Sugestão

Ferramentas adicionais:

Próximos passos

  • Last updated on