Neste artigo, falaremos sobre os recursos de ajuste de consulta que foram anunciados com o SQL Server 2019.

Às vezes, ajustar as consultas pode ser uma tarefa difícil para desenvolvedores ou administradores de banco de dados. O ponto mais significativo de otimizar uma consulta é ser capaz de interpretar o plano de consulta com precisão, pois fornece muitas informações sobre a consulta, desta forma podemos superar os problemas de desempenho com mais facilidade. Ao mesmo tempo, usar os novos recursos do SQL Server 2019 pode ajudar a superar os problemas de desempenho sem nenhuma alteração de código. Nesse post, veremos como o desempenho da consulta muda em duas versões diferentes do SQL Server e examinaremos quais recursos do SQL Server 2019 levam a essa melhoria de desempenho.

Pré requisitos: Usaremos o banco de dados de exemplo Adventureworks2017, mas o tamanho desse banco de dados é muito pequeno para simular problemas de desempenho da consulta de exemplo, por esse motivo, usaremos o script Create Enlarged AdventureWorks Tables para obter uma versão ampliada do SalesOrder e Tabelas SalesOrderDetail .

Interpretar o plano de execução de uma consulta

O plano de execução mostra quais etapas foram executadas pelo mecanismo de consulta durante a execução da consulta. Portanto, podemos descobrir qual etapa ou etapas da consulta são mais problemáticas e causam problemas de desempenho. Agora, vamos executar a consulta a seguir e interpretar o plano de execução real.

SELECT p.ProductNumber,
       p.Name,
       s.CarrierTrackingNumber,
       h.AccountNumber,
       h.CreditCardApprovalCode,
       dbo.[ufnGetStock](p.ProductID)
FROM Sales.SalesOrderDetailEnlarged s
INNER JOIN Production.Product p
       ON s.ProductID = p.ProductID
INNER JOIN Sales.SalesOrderHeaderEnlarged h
       ON h.SalesOrderID = s.SalesOrderID
WHERE s.OrderQty > 2 AND  LEN(CreditCardApprovalCode)>6
ORDER BY  CONCAT(
       SUBSTRING(CarrierTrackingNumber,1,4),
       SUBSTRING(p.Class,1,4))
DESC

Primeiramente, no plano de execução, clicaremos com o botão direito do mouse no operador select e, em seguida, verificaremos o tempo de conclusão da consulta. O atributo de tempo decorrido indica quanto tempo a consulta levou para ser executada. Esta consulta foi concluída em 169 segundos.

Depois disso, podemos fazer as seguintes perguntas:

  • Por que o otimizador de consulta não gera um plano de execução paralela?
  • O que o sinal de aviso indica no operador de classificação?
  • Devo criar a recomendação de índice ausente?

Vamos procurar respostas para as perguntas juntos agora.

Funções escalares definidas pelo usuário e problema de caixa preta

As funções definidas pelo usuário geralmente causam problemas de desempenho nas consultas porque o otimizador de consultas não tem nenhuma ideia sobre o código de função com valor escalar, portanto, ele não pode incluí-los nos custos do plano de consulta com precisão. Este comportamento das funções escalares pode ser comparado às caixas pretas.

Outro problema importante com funções escalares é que elas evitam a criação de um plano de execução paralelo. Quando lidamos com o plano de execução da consulta de exemplo, o atributo NonParallelPlanReason identifica por que o otimizador de consulta não gera um plano de execução paralela, mas a explicação CouldNotGenerateValidParallelPlan não fornece informações detalhadas sobre por que o otimizador de consulta não pôde gerar um plano de execução paralela. Na verdade, a função escalar é a culpada porque, como afirmamos, ela impede a geração de um plano paralelo pelo otimizador de consulta.

  • Pergunta 1: Por que o otimizador de consulta não gera um plano de execução paralela?
  • Resposta: Por causa da função de valor escalar

Operador de classificação e derramamento de tempdb

O otimizador de consulta estima quanta memória é necessária para executar uma consulta usando o número estimado de linhas. Ao mesmo tempo, as operações de classificação e hash join afetam essa demanda de memória. Estatísticas desatualizadas fazem com que o número estimado de linhas seja calculado incorretamente, de modo que a quantidade de memória necessária para a consulta seja calculada incorretamente. Durante a execução da consulta, o SQL Server não pode exigir mais memória dinamicamente. Nesse caso, a consulta começa a usar o banco de dados tempdb e esse problema causa problemas de desempenho. Em nosso plano de execução de consulta de amostra, podemos ver um sinal de aviso no operador de classificação e nos avisa sobre o problema de derramamento de tempdb.

Quando passamos o mouse sobre a seta à esquerda que está entre o operador escalar de cálculo e o operador de classificação, podemos ver a diferença dramática entre o número real de linhas e o número estimado de linhas. Este caso indica estatísticas desatualizadas.

  • Pergunta 2: O que o sinal de aviso indica no operador de classificação?
  • Resposta: Indica o problema de derramamento de tempdb devido às estatísticas desatualizadas

Índice ausente

Para algumas consultas, o SQL Server recomenda a criação de um índice ou índices. Esses índices podem ajudar a melhorar o desempenho da consulta. Por outro lado, existem alguns riscos envolvidos na aplicação dessas sugestões de índice, pois quando o SQL Server faz essas sugestões de índice para uma consulta específica, ele não tem ideia sobre toda a carga de trabalho do mecanismo de banco de dados. Os índices podem reduzir o desempenho das instruções de inserção, atualização e exclusão. Como resultado, devemos considerar as vantagens e desvantagens antes de criar o índice recomendado.

  • Pergunta 3: Devo criar a recomendação de índice ausente?
  • Resposta: Precisamos considerar os prós e contras antes de criar sugestões de índice ausentes

Ajuste de consulta no SQL Server 2019

No SQL Server 2019 foram anunciados alguns novos recursos que ajudam a melhorar o desempenho da consulta e os que se destacam são:

  • Inlining UDF escalar
  • Modo em lote no Rowstore
  • Feedback de concessão de memória do modo linha

Inlining UDF escalar

O inlining UDF escalar pode resolver os problemas de desempenho da função escalar porque esse novo recurso transforma automaticamente as funções escalares em subconsultas ou expressões escalares e as inclui na consulta. Assim, o otimizador de consulta gera planos de execução mais precisos e consistentes e também pode gerar planos paralelos. Agora mudamos o nível de compatibilidade do Adventureworks para SQL Server 2019 e observamos o efeito desse recurso no plano de execução.

ALTER DATABASE AdventureWorks2017 SET COMPATIBILITY_LEVEL = 150
  • Dica: também podemos alterar o nível de compatibilidade do banco de dados no SQL Server Management Studio (SSMS). Primeiramente, clique com o botão direito no banco de dados e selecione o menu Propriedades . Como uma segunda etapa, clique na guia Opções e altere o nível de compatibilidade

Após alterar o nível de compatibilidade do banco de dados, executaremos a mesma consulta de exemplo e reanalisaremos o plano de execução.

SELECT p.ProductNumber,
       p.Name,s.CarrierTrackingNumber,
       h.AccountNumber,
       h.CreditCardApprovalCode,
       dbo.[ufnGetStock](p.ProductID)
FROM Sales.SalesOrderDetailEnlarged s
INNER JOIN Production.Product p
       ON s.ProductID = p.ProductID
INNER JOIN Sales.SalesOrderHeaderEnlarged h
       ON h.SalesOrderID = s.SalesOrderID
WHERE s.OrderQty > 2 AND  LEN(CreditCardApprovalCode)>6
ORDER BY  CONCAT(
      SUBSTRING(CarrierTrackingNumber,1,4),
      SUBSTRING(p.Class,1,4))
DESC

Como podemos ver na execução, o otimizador de consultas gerou um plano de execução paralela e transformou a função escalar em uma subconsulta.

Modo em lote no Rowstore

Com a ajuda do modo de lote no recurso rowstore, o SQL Server pode processar as linhas com lotes. Portanto, o desempenho da consulta melhorará enormemente. No plano de execução, o atributo BatchModeOnRowStoreUsed indica que o SQL Server processa as linhas no modo em lote.

Nas propriedades do operador de varredura de índice clusterizado, podemos descobrir mais detalhes sobre o processamento do modo em lote.

A imagem acima mostra que cada encadeamento passou por várias linhas no modo em lote no recurso rowstore. Quando fazemos um cálculo muito simples, podemos descobrir que o mecanismo de banco de dados processou ~ 900 linhas por vez, em vez de linha por linha.

Número da linha processada = número real de linhas / número real de linhas

Até agora, percebemos que os recursos do SQL Server 2019 podem ser muito úteis para impulsionar o desempenho das consultas sem nenhuma alteração de código. O gráfico a seguir ilustra a dramática diferença de tempo decorrido entre as duas versões do SQL Server.

Feedback de concessão de memória do modo linha

Se a alocação de memória for insuficiente para uma consulta, na próxima execução da mesma consulta, o SQL Server atribuirá mais memória para ela usando os planos de execução de consulta pré-executados. Esse recurso é chamado de feedback de concessão de memória. Quando executamos a consulta de amostra 3 ou 4 vezes, veremos que desaparecerá o sinal de aviso que foi colocado no operador de classificação. Quando re-examinar as propriedades do operador selecione, veremos o IsMemoryGrantFeedbackAdjusted atributo indica YesStable valor. Isso significa que a opção de feedback de concessão de memória foi aplicada a esta consulta e a memória de concessão agora está estável.

Conclusão

Neste artigo, aprendemos os detalhes do recurso de ajuste de consulta que foram anunciados com o SQL Server 2019 e examinamos os detalhes em um exemplo. Particularmente, o escalar UDF Inlining e o modo em lote no Rowstore podem melhorar o desempenho da consulta sem nenhuma alteração de código e este ponto é uma parte impressionante desses recursos.

Fonte: https://www.sqlshack.com/