Compartilhar via

Tipos detectáveis de gargalos de desempenho de consulta no SQL Server e na Instância Gerenciada de SQL do Azure

  • Artigo

Aplica-se a: SQL Server Instância Gerenciada de SQL do Azure

Ao tentar resolver um gargalo de desempenho, comece determinando se o gargalo está ocorrendo enquanto a consulta está em estado de execução ou de espera. Resoluções diferentes são aplicáveis dependendo dessa determinação. Problemas e resoluções relacionadas a cada tipo de problema são discutidos neste artigo.

É possível usar DMVs do SQL Server para detectar esses tipos de gargalos de desempenho.

Problemas relacionados à execução: problemas relacionados à execução geralmente estão relacionados a problemas de compilação, resultando em um plano de consulta de qualidade inferior ou problemas de execução relacionados a recursos insuficientes ou usados em excesso. Problemas relacionados à espera: problemas relacionados à espera geralmente estão relacionados a:

  • Bloqueios (bloqueio)
  • E/S
  • Contenção relacionada ao uso do tempdb
  • Esperas de concessão de memória

Este artigo trata do SQL Server e da Instância Gerenciada de SQL do Azure, mas também é possível conferir Tipos detectáveis de gargalos de desempenho de consulta no Banco de Dados SQL do Azure.

Problemas de compilação resultando em um plano de consulta de qualidade inferior

Um plano de qualidade inferior gerado pelo otimizador de consulta SQL pode ser a causa do desempenho de consulta lento. O otimizador de consulta SQL pode produzir um plano com qualidade inferior devido a um índice ausente, estatísticas obsoletas, uma estimativa incorreta do número de linhas a serem processadas ou uma estimativa imprecisa da memória necessária. Se você sabe que a consulta foi executada mais rapidamente no passado ou em outra instância, compare os planos de execução atuais para ver se eles são diferentes.

O exemplo de ajuste e dica de consulta no artigo Ajustar aplicativos e bancos de dados para alto desempenho mostra o impacto de um plano de consulta abaixo do ideal devido a uma consulta parametrizada, como detectar essa condição e como usar uma dica de consulta para resolvê-la.

Resolvendo consultas com planos de execução de consulta de qualidade inferior

As seções a seguir discutem como resolver consultas com plano de execução de consulta de qualidade inferior.

Consultas que têm problemas de PSP (plano sensível ao parâmetro)

O problema de PSP acontece quando o otimizador de consulta gera um plano de execução de consulta ideal apenas para um valor de parâmetro específico (ou conjunto de valores) e o plano armazenado em cache não é ideal para os valores de parâmetro usados em execuções consecutivas. Os planos que não são ideais podem causar problemas de desempenho de consulta e degradar toda a taxa de transferência da carga de trabalho.

Para obter mais informações sobre detecção e processamento de consulta, confira o Guia da Arquitetura de Processamento de Consultas.

Várias soluções alternativas podem reduzir os problemas de PSP. Cada solução alternativa tem implicações e desvantagens associadas:

  • Um novo recurso introduzido no SQL Server 2022 (16.x) é a otimização de plano com reconhecimento de parâmetros, que tenta minimizar a maioria dos planos de consulta abaixo do ideal causados pela sensibilidade aos parâmetros. Isso é habilitado com o nível de compatibilidade de banco de dados 160.
  • Use a dica de consulta RECOMPILE em cada execução da consulta. Essa solução negocia o tempo de compilação e aumenta a CPU para melhorar a qualidade do plano. Muitas vezes não é possível usar a opção RECOMPILE para cargas de trabalho que exigem uma alta taxa de transferência.
  • Use a dica de consulta OPTION (OPTIMIZE FOR…) para substituir o valor de parâmetro real por um valor de parâmetro típico que produz um plano bom o suficiente para a maioria das possibilidades de valor de parâmetro. Essa opção requer uma boa compreensão dos valores de parâmetro ideais e características de plano associadas.
  • Use a dica de consulta OPTION (OPTIMIZE FOR UNKNOWN) para substituir o valor do parâmetro real pela média do vetor de densidade. Você também pode fazer isso ao capturar os valores de parâmetro de entrada em variáveis locais e em vez de usar os próprios parâmetros, usar as variáveis locais dentro de predicados. Para essa correção, a densidade média deve ser boa o suficiente.
  • Desabilite totalmente a detecção de parâmetro usando a dica de consulta DISABLE_PARAMETER_SNIFFING.
  • Use a dica de consulta KEEPFIXEDPLAN para evitar recompilações no cache. Esta solução alternativa pressupõe que o plano comum “bom o suficiente” já está no cache. Você também pode desabilitar as atualizações automáticas de estatísticas para reduzir as chances de o plano bom ser removido e um novo plano incorreto ser compilado.
  • Force o plano usando explicitamente a dica de consulta USE PLAN, reescrevendo a consulta e adicionando a dica no texto da consulta. Ou defina um plano específico usando Repositório de Consultas ou habilitando o ajuste automático.
  • Substitua o procedimento único por um conjunto aninhado de procedimentos que podem, cada um, ser usado com base em lógica condicional e nos valores de parâmetro associados.
  • Crie alternativas de execução de cadeia de caracteres dinâmica para uma definição de procedimento estático.

Para aplicar dicas de consulta, modifique a consulta ou use as dicas do Repositório de Dados de Consultas para aplicar a dica sem fazer alterações no código. Nas versões do SQL Server anteriores ao SQL Server 2022, use guias de plano.

Para obter mais informações sobre como resolver problemas de PSP, consulte estas postagens no blog:

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

Quando uma consulta tem literais, o mecanismo de banco de dados parametriza automaticamente a instrução ou um usuário pode parametrizado explicitamente para reduzir o número de compilações. Um grande número de compilações para uma consulta usando o mesmo padrão, mas valores literais diferentes, pode resultar em alto uso da CPU. Da mesma forma, se você parametrizar apenas parcialmente uma consulta que continua a ter literais, o mecanismo de banco de dados não parametriza ainda mais a consulta.

Aqui está um exemplo de uma consulta parametrizada parcialmente:

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

No exemplo anterior, t1.c1 usa @p1, mas t2.c2 continua a usar a GUID como literal. Nesse caso, se você alterar o valor para c2, a consulta será tratada como uma consulta diferente e haverá uma nova compilação. Para reduzir as compilações neste exemplo, você também parametrizaria a GUID.

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

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 do plano de consulta

Uma recompilação do plano de execução de consulta pode resultar em um plano de consulta gerado que diferente do plano original em cache. Um plano original existente pode ser recompilado automaticamente por vários motivos:

  • 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 de consulta foram alteradas

Um plano compilado pode ser ejetado do cache por vários motivos, como:

  • Reinicializações de instância
  • Alterações de configuração no escopo do banco de dados
  • Demanda de memória
  • Solicitações explícitas para limpar o cache

Se você usar uma dica de RECOMPILAÇÃO, um plano não será armazenado em cache.

Uma recompilação (ou uma nova compilação após a remoção do cache) ainda pode resultar na geração de um plano de execução de consulta que seja idêntico ao original. Quando o plano é diferente do plano anterior ou original, é provável que as explicações sejam:

  • Design físico alterado: por exemplo, índices criados recentemente abordam com mais eficiência os requisitos de uma consulta. Os novos índices podem ser usados em uma nova compilação se o otimizador de consulta decidir que usar esse novo índice é mais adequado do que usar a estrutura de dados que foi originalmente selecionada para a primeira versão da execução da consulta. Qualquer alteração física nos objetos referenciados pode resultar em uma nova opção de plano no momento da compilação.

  • Diferenças de recursos de servidor: quando um plano em um sistema difere do plano em outro sistema, a disponibilidade de recursos, como o número de processadores disponíveis, pode influenciar qual plano é gerado. Por exemplo, se um sistema tiver mais processadores, um plano paralelo poderá ser escolhido. Para saber mais sobre o paralelismo, confira Configurar a opção de configuração do servidor de grau máximo de paralelismo.

  • Estatísticas diferentes: as estatísticas associadas aos objetos referenciados podem ter sido alteradas ou podem ser de materiais diferentes das estatísticas do sistema original. Se as estatísticas forem alteradas e ocorrer uma recompilação, o otimizador de consulta usará as estatísticas a partir de quando elas forem alteradas. As frequências e as distribuições de dados das estatísticas revisadas podem ser diferentes das da compilação original. Essas alterações são usadas para criar estimativas de cardinalidade. (Estimativas de cardinalidade são o número de linhas que devem fluir por meio da árvore de consulta lógica.) As alterações nas estimativas de cardinalidade podem levá-lo a escolher operadores físicos e ordens associadas de operações diferentes. Até as menores alterações em estatísticas podem resultar em um plano de execução de consulta alterado.

  • Nível de compatibilidade do banco de dados alterado ou versão do avaliador de cardinalidade: alterações no nível de compatibilidade do banco de dados podem habilitar novas estratégias e recursos que podem resultar em um plano de execução de consulta diferente. Além do nível de compatibilidade do banco de dados, um sinalizador de rastreamento desabilitado ou habilitado 4199 ou um estado alterado da configuração no escopo do banco de dados QUERY_OPTIMIZER_HOTFIXES também pode influenciar as opções do plano de execução de consulta no momento da compilação. Os sinalizadores de rastreamento 9481 (forçar herança CE) e 2312 (forçar padrão CE) também afetam o plano.

Problemas de limites de recursos na Instância Gerenciada de SQL do Azure

O desempenho de consulta lento sem relação com os planos de consulta de qualidade inferior e índices ausentes geralmente está relacionado a recursos insuficientes ou utilizados em excesso. Quando o plano de consulta é ideal, a consulta (e o banco de dados) pode estar atingindo os limites de recursos da instância gerenciada. Um exemplo pode ser a taxa de transferência de gravação de log em excesso para o nível de serviço.

Se você identificar o problema como recurso insuficiente, pode atualizar os recursos para aumentar a capacidade do seu banco de dados para absorver os requisitos de CPU. Para saber mais sobre o dimensionamento de uma instância gerenciada, confira Limites de recursos na camada de serviço

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

Um aumento no tráfego do aplicativo e no volume da carga de trabalho pode causar um aumento no uso da CPU. Mas você deve ter cuidado para diagnosticar corretamente esse problema. Quando você observar um problema de alto uso da CPU, responda a essas perguntas para determinar se o aumento é causado por alterações no volume da carga de trabalho:

  • As consultas do aplicativo são a causa do problema de alto uso da CPU?

  • Para as consultas que mais consomem CPU que você pode identificar:

    • Existem vários planos de execução associados à mesma consulta? Em caso afirmativo, por quê?
    • Para consultas com o mesmo plano de execução, os tempos de execução são consistentes? A contagem de execuções aumentou? Nesse caso, o aumento da carga de trabalho provavelmente está causando problemas de desempenho.

Resumindo, se o plano de execução de consulta não está sendo executado de modo diferente, mas o uso da CPU e a contagem de execução tiver aumentado, o problema de desempenho provavelmente tem relação com o aumento da carga de trabalho.

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

  • Uso de recurso alterado: por exemplo, considere um cenário em que o uso da CPU tenha aumentado para 80% por um longo período. Apenas o uso da CPU não significa que o volume da carga de trabalho foi alterado. Regressões no plano de execução de consulta e mudanças na distribuição de dados também podem contribuir para mais uso de recursos, mesmo que o aplicativo execute a mesma carga de trabalho.

  • A aparência de uma nova consulta: um aplicativo pode direcionar um novo conjunto de consultas em momentos diferentes.

  • Um aumento ou diminuição no número de solicitações: esse cenário é a forma mais óbvia de medir uma carga de trabalho. O número de consultas nem sempre corresponde a uma maior utilização de recursos. No entanto, essa métrica ainda é um sinal significativo, supondo que outros fatores sejam os mesmos.

  • Paralelismo: o paralelismo excessivo pode piorar o desempenho de outras cargas de trabalho simultâneas, privando outras consultas de recursos de CPU e de thread de trabalho. Para saber mais sobre o paralelismo, confira Configurar a opção de configuração do servidor de grau máximo de paralelismo.

Depois de ter eliminado um plano de qualidade inferior e problemas Relacionados à espera relacionados a problemas de execução, geralmente o problema de desempenho é que as consultas provavelmente estão aguardando algum recurso. Problemas relacionados à espera podem ser causados por:

  • Bloqueio:

    Uma consulta pode manter o bloqueio em objetos no banco de dados enquanto outros tentam acessar os mesmos objetos. Você pode identificar consultas de bloqueio ao usar DMVs. Para obter mais informações, consulte Entender e resolver problemas de bloqueio.

  • Problemas de E/S

    As consultas podem estar aguardando que as páginas sejam gravadas nos arquivos de dados ou de log. Nesse caso, verifique as INSTANCE_LOG_RATE_GOVERNOR, WRITE_LOG ou PAGEIOLATCH_* estatísticas de espera no DMV. Consulte usando DMVs para identificar problemas de desempenho de E/S.

  • Problemas de tempdb

    Se a carga de trabalho usa tabelas temporárias ou houver tempdbdespejos nos planos, as consultas podem ter um problema com tempdba taxa de transferência. Para investigar melhor, examine identificar problemas de tempdb.

  • Problemas relacionados à memória

    Se a carga de trabalho não tiver memória suficiente, a expectativa de vida da página pode diminuir ou as consultas podem obter menos memória do que precisam. Em alguns casos, a inteligência interna no otimizador de consulta corrigirá os problemas relacionados à memória. Consulte usando DMVs para identificar problemas de concessão de memória. Se você encontrar erros de memória insuficiente, avalie sys.dm_os_out_of_memory_events. Considere também a camada Série Premium otimizada para memória do hardware da Instância Gerenciada de SQL do Azure com taxas mais altas de memória para vCores.

Métodos para mostrar as principais categorias de espera

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

  • Use Repositório de Consultas para localizar estatísticas de espera para cada consulta ao longo do tempo. No Repositório de Consultas, aguarde os tipos de espera combinados em categorias de espera. Você pode localizar o mapeamento das categorias de espera para tipos de espera disponível em sys.query_store_wait_stats.
  • Use sys.dm_os_wait_stats para retornar informações sobre todas as esperas encontradas por threads que foram executadas durante uma operação de consulta. É possível usar essa exibição agregada para diagnosticar problemas de desempenho com a Instância Gerenciada de SQL do Azure ou a instância do SQL Server. As consultas podem estar aguardando recursos, esperas de fila ou externas.
  • Use sys.dm_os_waiting_tasks para devolver informações sobre a fila de tarefas que está esperando algum recurso.

Em cenários de alto uso da CPU, o Repositório de Consultas e as estatísticas de espera podem não refletir o uso da CPU se:

  • As consultas de alto consumo da CPU ainda estão em execução.
  • Consultas de alto consumo da CPU estavam em execução quando ocorreu um failover.

As DMVs que rastreiam o Repositório de Consultas e estatísticas de espera mostram resultados somente para consultas concluídas com êxito e no tempo limite. Eles não mostram dados para demonstrativo em execução atualmente até que os demonstrativos sejam concluídos. Use a exibição de gerenciamento dinâmico sys.dm_exec_requests para acompanhar as consultas em execução no momento e o tempo de trabalho associado.

Dica

Ferramentas adicionais:

Próximas etapas