Identificar planos de consulta problemáticos

  • 15 minutos

A abordagem típica que os DBAs tomam para solucionar problemas de desempenho de consulta envolve primeiro identificar a consulta problemática, geralmente aquela que consome mais recursos do sistema e, em seguida, recuperar seu plano de execução. Há dois cenários principais. Um cenário é que a consulta consistentemente tem um desempenho ruim. Isso pode ocorrer devido a vários problemas, como restrições de recurso de hardware (embora isso geralmente não afete uma única consulta em execução isoladamente), estrutura de consulta abaixo do ideal, configurações de compatibilidade de banco de dados, índices ausentes ou escolhas de plano ruins pelo otimizador de consulta. O segundo cenário é que a consulta tem bom desempenho em algumas execuções, mas ruim em outras. Essa inconsistência pode ser causada por fatores como distorção de dados em uma consulta parametrizada, que tem um plano eficiente para algumas execuções e ruim para outras. Outros fatores comuns incluem bloqueio, onde uma consulta aguarda a conclusão de outra para obter acesso a uma tabela, ou disputa por recursos de hardware.

Vamos explorar cada um desses cenários com mais detalhes.

Restrições de hardware

Normalmente, as restrições de hardware não se manifestam durante execuções de consulta única, mas ficam evidentes na carga de produção quando os threads e a memória da CPU são limitados. A contenção da CPU pode ser detectada ao observar o contador do monitor de desempenho '% Tempo do Processador', que mede o uso da CPU do servidor. No SQL Server, os tipos de espera SOS_SCHEDULER_YIELD e CXPACKET podem indicar pressão da CPU. O desempenho ruim do sistema de armazenamento pode diminuir até mesmo as execuções de consulta única otimizadas. O desempenho do armazenamento é melhor acompanhado no nível do sistema operacional usando os contadores do monitor de desempenho Disk Seconds/Read e Disk Seconds/Write, que medem os tempos de conclusão das operações de E/S. O SQL Server registra um desempenho de armazenamento ruim se uma E/S levar mais de 15 segundos. Altas esperas de PAGEIOLATCH_SH no SQL Server podem indicar problemas de desempenho de armazenamento. O desempenho de hardware normalmente é avaliado no início do processo de solução de problemas devido à facilidade de avaliação.

A maioria dos problemas de desempenho do banco de dados decorre de padrões de consulta abaixo do ideal, o que pode pressionar indevidamente o hardware. Por exemplo, índices ausentes podem levar à pressão de CPU, armazenamento e memória recuperando mais dados do que o necessário. É recomendável resolver e ajustar consultas subótimas antes de resolver problemas de hardware. Em seguida, veremos o ajuste da consulta.

Construções de consulta subótimas

Os bancos de dados relacionais têm o melhor desempenho ao executar operações baseadas em conjunto, que manipulam dados (INSERT, UPDATE, DELETEe SELECT) em conjuntos, produzindo um único valor ou um conjunto de resultados. A alternativa é o processamento baseado em linhas, usando cursores ou loops while, que aumentam o custo linearmente com o número de linhas afetadas, uma escala problemática à medida que os volumes de dados aumentam.

Detectar o uso subótimo de operações baseadas em linhas com cursores ou loops WHILE é importante, mas há outros padrões inadequados do SQL Server a serem reconhecidos. Funções com valor de tabela (TVFs), especialmente TVFs multi-statement, causaram padrões problemáticos de plano de execução antes do SQL Server 2017. Os desenvolvedores costumam usar TVFs de várias declarações para executar várias consultas em uma única função e agregar resultados em uma única tabela. No entanto, o uso de TVFs pode causar penalidades de desempenho.

O SQL Server tem dois tipos de TVFs: inline e multi-statement. TVFs inline são tratadas como exibições, enquanto TVFs multi-statement são tratadas como tabelas durante o processamento de consultas. Como os TVFs são dinâmicos e não têm estatísticas, o SQL Server usa uma contagem de linhas fixa para estimar o custo do plano de consulta. Isso pode ser bom para contagens de linhas pequenas, mas ineficiente para milhares ou milhões de linhas.

Outro antipadrão é o uso de funções escalares, que têm problemas de estimativa e execução semelhantes. A Microsoft fez melhorias significativas de desempenho com o Processamento inteligente de consultas, nos níveis de compatibilidade 140 e 150.

SARGability

O termo SARGable em bancos de dados relacionais refere-se a um predicado (WHERE cláusula) formatado para usar um índice para acelerar a execução da consulta. Os predicados no formato correto são chamados de 'Argumentos de Pesquisa' ou SARGs. No SQL Server, usar um SARG significa que o otimizador avalia usando um índice não clusterizado sobre a coluna referenciada no SARG para uma operação SEEK, em vez de varrer todo o índice ou tabela para recuperar um valor.

A presença de um SARG não garante o uso de um índice para um SEEK. Os algoritmos de custo do otimizador ainda podem determinar que o índice é muito caro, especialmente se um SARG se refere a uma grande porcentagem de linhas em uma tabela. A ausência de um SARG significa que o otimizador não avaliará um SEEK em um índice não clusterizado.

Exemplos de expressões não passíveis de SARG incluem aquelas com uma cláusula LIKE usando um curinga no início da string, como WHERE lastName LIKE '%SMITH%'. Outros predicados não SARGable ocorrem ao usar funções em uma coluna, como WHERE CONVERT(CHAR(10), CreateDate,121) = '2020-03-22'. Essas consultas são normalmente identificadas ao examinar planos de execução para verificações de índice ou tabela em que a procura deveria ocorrer.

Captura de tela do plano de execução da consulta usando uma função de filtro que não pode ser otimizado.

Há um índice na coluna City que está sendo utilizado na cláusula WHERE da consulta e, embora esteja sendo utilizado no plano de execução acima, você pode ver que o índice está sendo escaneado, o que significa que todo o índice está sendo lido. A função LEFT no predicado torna essa expressão não SARGable. O otimizador não irá realizar uma avaliação usando uma busca de índice na coluna City.

Essa consulta pode ser escrita para usar um predicado SARGable. O otimizador avaliaria uma SEEK no índice na coluna City. Um operador de busca de índice, nesse caso, leria um conjunto menor de linhas.

Captura de tela de um plano de consulta e de execução com um predicado SARGable.

Alterar a função LEFT para um LIKE resulta em uma procura de índice.

Observação

A palavra-chave LIKE, nesse exemplo, não tem um curinga à esquerda, portanto, está procurando cidades que começam com M. Se fosse "dos dois lados" ou começasse com um curinga ('%M%' ou '%M'), não seria SARGable. Estima-se que a operação de busca retorne 1.267 linhas ou aproximadamente 15% da estimativa para a consulta com o predicado não SARGable.

Alguns outros antipadrões de desenvolvimento de banco de dados estão tratando o banco de dados como um serviço em vez de um armazenamento de dados. Usar um banco de dados para converter dados em JSON, manipular cadeias de caracteres ou executar cálculos complexos pode levar ao uso excessivo da CPU e ao aumento da latência. Consultas que tentam recuperar todos os registros e, em seguida, executar cálculos no banco de dados podem levar a uso excessivo de E/S e CPU. Idealmente, você deve usar o banco de dados para operações de acesso a dados e constructos de banco de dados otimizados, como agregação.

Índices ausentes

Os problemas de desempenho mais comuns para os administradores de banco de dados decorrem da falta de índices úteis, fazendo com que o mecanismo leia mais páginas do que o necessário para retornar os resultados da consulta. Embora os índices consumam recursos (afetando o desempenho de gravação e o consumo de espaço), seus ganhos de desempenho geralmente superam os custos extras de recursos. Planos de execução com esses problemas podem ser identificados pelo operador de consulta Clustered Index Scan ou pela combinação de Pesquisa de Índice Não Clusterizado e Pesquisa de Chave, indicando colunas ausentes em um índice existente.

O mecanismo de banco de dados ajuda relatando índices ausentes em planos de execução. Os nomes e detalhes dos índices recomendados estão disponíveis por meio da exibição de gerenciamento sys.dm_db_missing_index_detailsdinâmico. Outras DMVs como sys.dm_db_index_usage_stats e sys.dm_db_index_operational_stats realçam a utilização de índices existentes.

Remover um índice não utilizado pode ser sensato. As DMVs de índice ausentes e os avisos de plano devem ser pontos de partida para ajustar consultas. É crucial entender as principais consultas e criar índices para dar suporte a elas. Não é recomendável criar todos os índices ausentes sem avaliá-los no contexto.

Estatísticas ausentes e desatualizadas

Entender a importância das estatísticas de coluna e índice para o otimizador de consulta é crucial. Também é essencial reconhecer condições que podem levar a estatísticas desatualizadas e como esse problema pode se manifestar no SQL Server. As ofertas de SQL do Azure costumam ter as estatísticas de autoupdate definidas como ON. Antes do SQL Server 2016, o comportamento padrão das estatísticas de preenchimento automático era não atualizar estatísticas até que o número de modificações em colunas no índice fosse igual a cerca de 20% do número de linhas em uma tabela. Esse comportamento pode resultar em modificações significativas de dados que alteram o desempenho da consulta sem atualizar as estatísticas, levando a planos abaixo do ideal com base em estatísticas desatualizadas.

Antes do SQL Server 2016, o sinalizador de rastreamento 2371 podia ser usado para alterar o número necessário de modificações para um valor dinâmico, de modo que, à medida que sua tabela aumentava, o percentual de modificações de linha necessárias para disparar uma atualização de estatísticas diminuiu. As versões mais recentes do SQL Server, do Banco de Dados SQL do Azure e da Instância Gerenciada de SQL do Azure dão suporte a esse comportamento por padrão. A função sys.dm_db_stats_properties de gerenciamento dinâmico mostra a última vez que as estatísticas foram atualizadas e o número de modificações desde a última atualização, permitindo que você identifique rapidamente estatísticas que possam precisar de atualizações manuais.

Opções inadequadas de otimizador

Embora o otimizador de consulta faça um bom trabalho para otimizar a maioria das consultas, há alguns casos de borda em que o otimizador baseado em custo pode tomar decisões impactantes que não são totalmente compreendidas. Há várias maneiras de resolver isso, incluindo o uso de dicas de consulta, sinalizadores de rastreamento, imposição de plano de execução e outros ajustes para alcançar um plano de consulta estável e ideal. A Microsoft tem uma equipe de suporte que pode ajudar a solucionar esses cenários.

No exemplo abaixo do banco de dados AdventureWorks2017, uma dica de consulta está sendo usada para informar ao otimizador de banco de dados para sempre usar o nome da cidade de Seattle. Essa dica não garantirá o melhor plano de execução para todos os valores da cidade, mas é previsível. O valor de 'Seattle' para @city_name só será usado durante a otimização. Durante a execução, o valor (‘Ascheim’) real fornecido é usado.

DECLARE @city_name nvarchar(30) = 'Ascheim',
        @postal_code nvarchar(15) = 86171;

SELECT * 
FROM Person.Address
WHERE City = @city_name 
      AND PostalCode = @postal_code
OPTION (OPTIMIZE FOR (@city_name = 'Seattle');

Como visto no exemplo, a consulta usa uma dica (a cláusula OPTION) para informar o otimizador sobre o uso de um valor de variável específico ao criar seus planos de execução.

Detecção de parâmetros

O SQL Server armazena em cache planos de execução de consulta para uso futuro. Como o processo de recuperação do plano de execução é baseado no valor de hash de uma consulta, o texto da consulta deve ser idêntico para cada execução da consulta para que o plano armazenado em cache seja usado. Para dar suporte a vários valores na mesma consulta, muitos desenvolvedores usam parâmetros, passados por procedimentos armazenados, como visto no exemplo a seguir:

CREATE PROC GetAccountID (@Param INT)
AS

<other statements in procedure>

SELECT accountid FROM CustomerSales WHERE sales > @Param;

<other statements in procedure>

RETURN;

-- Call the procedure:

EXEC GetAccountID 42;

As consultas também podem ser explicitamente parametrizadas usando o procedimento sp_executesql. No entanto, a parametrização explícita de consultas individuais é feita por meio do aplicativo com algum formulário (dependendo da API) de PREPARE e EXECUTE. Quando o mecanismo de banco de dados executa essa consulta pela primeira vez, ele otimiza a consulta com base no valor inicial do parâmetro, nesse caso, 42. Esse comportamento, chamado de detecção de parâmetros, permite que a carga de trabalho geral da compilação de consultas seja reduzida no servidor. No entanto, se houver distorção de dados, o desempenho da consulta poderá variar bastante.

Por exemplo, uma tabela que tinha 10 milhões de registros e 99% desses registros têm uma ID de 1 e as outras 1% são números exclusivos, o desempenho é baseado no qual a ID foi usada inicialmente para otimizar a consulta. Esse desempenho extremamente flutuante é um indicativo de distorção de dados e não é um problema inerente à detecção de parâmetros. Esse comportamento é um problema de desempenho bastante comum que você deve estar ciente. Você deve entender as opções para aliviar o problema. Há algumas maneiras de resolver esse problema, mas cada uma delas vem com compensações:

  • Use a dica RECOMPILE em sua consulta ou a opção de execução WITH RECOMPILE em seus procedimentos armazenados. Essa dica faz com que a consulta ou o procedimento seja recompilado sempre que for executado, o que aumentará a utilização da CPU no servidor, mas sempre usará o valor do parâmetro atual.
  • Você pode usar a dica de consulta OPTIMIZE FOR UNKNOWN. Essa dica faz com que o otimizador escolha não detectar parâmetros e compare o valor com o histograma de dados da coluna. Essa opção não lhe dará o melhor plano possível, mas permitirá um plano de execução consistente.
  • Reescreva seu procedimento ou consultas adicionando lógica em torno dos valores de parâmetro para RECOMPILAR apenas para parâmetros problemáticos conhecidos. No exemplo abaixo, se o parâmetro SalesPersonID for NULL, a consulta será executada com o OPTION (RECOMPILE).
CREATE OR ALTER PROCEDURE GetSalesInfo (@SalesPersonID INT = NULL)
AS
DECLARE  @Recompile BIT = 0
         , @SQLString NVARCHAR(500)

SELECT @SQLString = N'SELECT SalesOrderId, OrderDate FROM Sales.SalesOrderHeader WHERE SalesPersonID = @SalesPersonID'

IF @SalesPersonID IS NULL
BEGIN
     SET @Recompile = 1
END

IF @Recompile = 1
BEGIN
    SET @SQLString = @SQLString + N' OPTION(RECOMPILE)'
END

EXEC sp_executesql @SQLString
    ,N'@SalesPersonID INT'
    ,@SalesPersonID = @SalesPersonID
GO

Este exemplo é uma boa solução, mas requer um esforço de desenvolvimento bastante grande e uma compreensão firme da distribuição de dados. Ele requer manutenção conforme os dados são alterados.