Identificar planos de consulta problemáticos

Concluído

O caminho que a maioria dos DBAs usa para solucionar problemas de desempenho da consulta é primeiro identificar a consulta problemática (normalmente a consulta que consome a maior quantidade de recursos do sistema) e, em seguida, recuperar o plano de execução dessa consulta. Há dois cenários. Uma delas é que a consulta tem um desempenho consistentemente ruim. O mau desempenho consistente pode ser causado por alguns problemas diferentes, incluindo restrições de recursos de hardware (embora essa situação normalmente não afete uma única consulta em execução isolada), uma estrutura de consulta subótima, configurações de compatibilidade de banco de dados, índices ausentes ou má escolha de plano pelo otimizador de consulta. O segundo cenário é que a consulta funciona bem para algumas execuções, mas não para outras. Esse problema pode ser causado por alguns outros fatores, sendo o mais comum a distorção de dados em uma consulta parametrizada que tem um plano eficiente para algumas execuções e um plano ruim para outras execuções. Os outros fatores comuns no desempenho inconsistente da consulta são o bloqueio, em que uma consulta está aguardando a conclusão de outra consulta para obter acesso a uma tabela, ou a contenção de hardware.

Vamos analisar cada um desses problemas potenciais com mais detalhes.

Restrições de hardware

Normalmente, as restrições de hardware não se manifestam com execuções de consulta única, mas ficam evidentes quando a carga de produção é aplicada e há um número limitado de threads de CPU e uma quantidade limitada de memória a ser compartilhada entre as consultas. Quando você tem contenção de CPU, geralmente será detetável observando o contador do monitor de desempenho '% Processor Time', que mede o uso da CPU do servidor. Examinando mais profundamente o SQL Server, você pode ver os tipos de espera SOS_SCHEDULER_YIELD e CXPACKET quando o servidor está sob pressão da CPU. No entanto, em alguns casos com baixo desempenho do sistema de armazenamento, até mesmo execuções únicas de uma consulta otimizada podem ser lentas. O desempenho do sistema de armazenamento é melhor controlado no nível do sistema operacional usando os contadores do monitor de desempenho 'Segundos de disco/leitura' e 'Segundos de disco/gravação', que medem quanto tempo uma operação de E/S leva para ser concluída. O SQL Server gravará em seu log de erros se detetar um desempenho de armazenamento insatisfatório (se uma E/S demorar mais de 15 segundos para ser concluída). Se você examinar as estatísticas de espera e ver uma alta porcentagem de esperas de PAGEIOLATCH_SH no SQL Server, poderá ter um problema de desempenho do sistema de armazenamento. Normalmente, o desempenho do hardware é examinado em um alto nível, no início do processo de solução de problemas de desempenho, porque é relativamente fácil de avaliar.

A maioria dos problemas de desempenho do banco de dados pode ser atribuída a padrões de consulta abaixo do ideal, mas, em muitos casos, a execução de consultas ineficientes colocará pressão indevida sobre o hardware. Por exemplo, índices ausentes podem causar pressão de CPU, armazenamento e memória, recuperando mais dados do que o necessário para processar a consulta. É recomendável que você resolva consultas abaixo do ideal e as ajuste antes de resolver problemas de hardware. Começaremos a examinar o ajuste de consultas a seguir.

Construções de consulta subótimas

Os bancos de dados relacionais têm melhor desempenho ao executar operações baseadas em conjunto. As operações baseadas em conjuntos executam a manipulação de dados (INSERT, , e ) em conjuntos, UPDATEDELETEonde o trabalho é feito em um conjunto de valores e SELECTproduz um único valor ou um conjunto de resultados. A alternativa às operações baseadas em conjunto é executar trabalho baseado em linha, usando um cursor ou um loop while. Esse tipo de processamento é conhecido como processamento baseado em linhas, e seu custo aumenta linearmente com o número de linhas afetadas. Essa escala linear é problemática à medida que os volumes de dados crescem para um aplicativo.

Embora a deteção do uso não ideal de operações baseadas em linha com cursores ou loops WHILE seja importante, há outros antipadrões do SQL Server que você deve ser capaz de reconhecer. As funções com valor de tabela (TVF), particularmente as funções com valor de tabela de várias instruções, causaram padrões de plano de execução problemáticos antes do SQL Server 2017. Muitos desenvolvedores gostam de usar funções com valor de tabela de várias instruções porque podem executar várias consultas dentro de uma única função e agregar os resultados em uma única tabela. No entanto, qualquer pessoa que escreva código T-SQL precisa estar ciente das possíveis penalidades de desempenho para o uso de TVFs.

O SQL Server tem dois tipos de funções com valor de tabela, inline e multistatement. Se você usar um TVF embutido, o mecanismo de banco de dados o tratará como uma exibição. TVFs de várias instruções são tratados como outra tabela ao processar uma consulta. Como os TVFs são dinâmicos e, como tal, o SQL Server não tem estatísticas sobre eles, ele usou uma contagem de linhas fixa ao estimar o custo do plano de consulta. Uma contagem fixa pode ser boa, se o número de linhas for pequeno, no entanto, se o TVF retornar milhares ou milhões de linhas, o plano de execução pode ser ineficiente.

Outro anti-padrão tem sido o uso de funções escalares, que têm problemas semelhantes de estimativa e execução. A Microsoft fez melhorias significativas de desempenho com a introdução do Processamento Inteligente de Consultas, sob os níveis de compatibilidade 140 e 150.

SARGability

O termo SARGable em bancos de dados relacionais refere-se a um predicado (cláusula WHERE) em um formato específico que pode usar um índice para acelerar a execução de uma 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 avaliará usando um índice não clusterizado na coluna referenciada no SARG para uma operação SEEK , em vez de verificar todo o índice (ou a tabela inteira) para recuperar um valor.

A presença de um SARG não garante o uso de um índice para um SEEK. Os algoritmos de custeio do otimizador ainda podiam determinar que o índice era muito caro. Este pode ser o caso se um SARG se refere a uma grande percentagem de linhas numa tabela. A ausência de um SARG significa que o otimizador nem sequer avaliará um SEEK em um índice não clusterizado.

Alguns exemplos de expressões que não são SARGs (às vezes dizem que não são sargáveis) são aquelas que incluem uma LIKE cláusula com um curinga no início da cadeia de caracteres a ser correspondida, por exemplo, WHERE lastName LIKE ‘%SMITH%’. Outros predicados que não são SARGs ocorrem ao usar funções em uma coluna, por exemplo, WHERE CONVERT(CHAR(10), CreateDate,121) = ‘2020-03-22’. Essas consultas com expressões não sargáveis são normalmente identificadas examinando planos de execução para varreduras de índice ou tabela, onde as buscas deveriam estar ocorrendo.

Há um índice na coluna Cidade que está sendo usado na WHERE cláusula da consulta e, enquanto ele está sendo usado neste plano de execução acima, você pode ver que o índice está sendo verificado, o que significa que todo o índice está sendo lido. A LEFT função no predicado torna esta expressão não-SARGable. O otimizador não avaliará usando uma busca de índice no índice na coluna Cidade .

Esta consulta pode ser escrita para usar um predicado que é SARGable. O otimizador avaliaria então um SEEK no índice na coluna Cidade . Um operador de busca de índice, neste caso, leria um conjunto muito menor de linhas, como mostrado abaixo.

Mudar a LEFT função para um LIKE resultado em uma busca de índice.

Nota

A palavra-chave LIKE, neste caso, não tem um curinga à esquerda, então está procurando cidades que começam com M. Se fosse "frente e verso" ou começasse com um curinga ('%M%' ou '%M') não seria SARGable. Estima-se que a operação de busca retorne 1267 linhas, ou aproximadamente 15% da estimativa para a consulta com o predicado não SARGable.

Alguns outros anti-padrõ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 ao uso excessivo de E/S e CPU. Idealmente, você deve usar o banco de dados para operações de acesso a dados e construções de banco de dados otimizadas, como agregação.

Índices em falta

Os problemas de desempenho mais comuns que vemos como administradores de banco de dados são devido à falta de índices úteis, fazendo com que o mecanismo leia muito mais páginas do que o necessário para retornar os resultados de uma consulta. Embora os índices não sejam gratuitos em termos de recursos (adicionar mais índices a uma tabela pode afetar o desempenho de gravação e consumir espaço), os ganhos de desempenho que eles oferecem podem compensar os custos extras de recursos muitas vezes. Frequentemente, os planos de execução com esses problemas de desempenho podem ser identificados pelo operador de consulta Clustered Index Scan ou pela combinação de Nonclustered Index Seek e Key Lookup (que é mais indicativo de colunas ausentes em um índice existente).

O mecanismo de banco de dados tenta ajudar com esse problema relatando índices ausentes nos planos de execução. Os nomes e detalhes dos índices recomendados estão disponíveis por meio de uma exibição de gerenciamento dinâmico chamada sys.dm_db_missing_index_details. Há também outros DMVs no SQL Server como sys.dm_db_index_usage_stats e sys.dm_db_index_operational_stats, que destacam a utilização de índices existentes.

Pode fazer sentido descartar um índice que não é usado por nenhuma consulta no banco de dados. Os DMVs de índice ausentes e os avisos de plano devem ser usados apenas como ponto de partida para ajustar suas consultas. É importante entender quais são suas principais consultas e criar índices para dar suporte a essas consultas. Criar todos os índices ausentes sem avaliar os índices no contexto uns dos outros não é recomendado.

Estatísticas em falta e desatualizadas

Você aprendeu sobre a importância das estatísticas de coluna e índice para o otimizador de consulta. Também é importante entender as condições que podem levar a estatísticas desatualizadas e como esse problema pode se manifestar no SQL Server. As ofertas SQL do Azure têm como padrão ter estatísticas de atualização automática definidas como ON. Antes do SQL Server 2016, o comportamento padrão das estatísticas de atualização automática era não atualizar estatísticas até que o número de modificações nas colunas no índice fosse igual a cerca de 20% do número de linhas em uma tabela. Devido a esse comportamento, você pode ter modificações de dados que foram significativas o suficiente para alterar o desempenho da consulta, mas não atualizar as estatísticas. Qualquer plano que utilizasse a tabela com os dados alterados seria baseado em estatísticas desatualizadas e seria frequentemente subótimo.

Antes do SQL Server 2016, você tinha a opção de usar o sinalizador de rastreamento 2371, que alterava o número necessário de modificações para ser um valor dinâmico, de modo que, à medida que sua tabela crescia em tamanho, a porcentagem de modificações de linha necessária para disparar uma atualização de estatísticas ficava menor. As versões mais recentes do SQL Server, do Banco de Dados SQL do Azure e da Instância Gerenciada do SQL do Azure oferecem suporte a esse comportamento por padrão. Há também uma função de gerenciamento dinâmico chamada sys.dm_db_stats_properties, que mostra a última vez que as estatísticas foram atualizadas e o número de modificações que foram feitas desde a última atualização, o que permite que você identifique rapidamente as estatísticas que podem precisar ser atualizadas manualmente.

Más opções de otimizador

Embora o otimizador de consulta faça um bom trabalho na otimização da 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á muitas maneiras de resolver isso, incluindo o uso de dicas de consulta, sinalizadores de rastreamento, força do 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 dizer ao otimizador de banco de dados para sempre usar um nome de cidade de Seattle. Esta dica não garantirá o melhor plano de execução para todos os valores da cidade, mas será previsível. O valor de 'Seattle' for @city_name só será usado durante a otimização. Durante a execução, será utilizado o valor (‘Ascheim’) real fornecido.

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 OPTION cláusula) para dizer ao otimizador para usar um valor de variável específico para criar seu plano de execução.

Deteção de parâmetros

O SQL Server armazena em cache planos de execução de consulta para uso futuro. Uma vez que o processo de recuperação do plano de execução se baseia no valor hash de uma consulta, o texto de consulta tem de ser idêntico para cada execução da consulta para o plano em cache a ser utilizado. Para suportar vários valores na mesma consulta, muitos desenvolvedores usam parâmetros, passados através de procedimentos armazenados, como visto no exemplo abaixo:

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 é geralmente feita através da aplicação com alguma forma (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, neste caso, 42. Esse comportamento, chamado de deteção de parâmetros, permite que a carga de trabalho geral de compilação de consultas seja reduzida no servidor. No entanto, se houver distorção de dados, o desempenho da consulta pode variar muito.

Por exemplo, uma tabela que tinha 10 milhões de registros, e 99% desses registros têm um ID de 1, e os outros 1% são números exclusivos, o desempenho será baseado em qual ID foi usado inicialmente para otimizar a consulta. Esse desempenho extremamente flutuante é indicativo de distorção de dados e não é um problema inerente à deteçã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. Existem algumas maneiras de resolver esse problema, mas cada uma delas vem com compensações:

• Use a dica em sua consulta ou a RECOMPILE opção de WITH RECOMPILE execução em seus procedimentos armazenados. Essa dica fará com que a consulta ou procedimento seja recompilado toda vez 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 OPTIMIZE FOR UNKNOWN consulta. Essa dica fará com que o otimizador opte por não farejar parâmetros e comparar o valor com o histograma de dados da coluna. Esta 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 de valores de parâmetro para apenas RECOMPILAR 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

O exemplo acima é uma boa solução, mas requer um esforço de desenvolvimento bastante grande e uma compreensão firme da sua distribuição de dados. Também pode exigir manutenção à medida que os dados mudam.