Melhorando a performance de consultas no TOTVS RM após uma migração para o SQL Server 2016
Fala Pessoal,
Hoje vou compartilhar para vocês um caso real e interessante que passamos ao migrar um banco de dados TOTVS RM do SQL Server 2008 para o SQL Server 2016.
Fizemos a migração e, como já estamos acostumados, algumas consultas passaram a ficar lentas após a migração, mesmo sem mudar o nível de compatibilidade das bases.
Nesse ambiente, o sistema ainda era antigo e estavam usando o BDE para se comunicar com o banco de dados.
As queries com problema batiam no banco da forma abaixo com um sp_executesql:
“exec sp_executesql N’SELECT A.CODCOLIGADA, A.NUMEROCONTA, A.SEQUENCIALCONTA, A.SEQPARCIAL, A.SEQDEBITOMATMED, A.IDPRD, A.FATORCONVERSAOMATMED, colunas from tabelas WHERE filtros”
Essa query demorava 5 minutos quando rodava na aplicação. Mas quando eu pegava o mesmo código/parâmetros dela no profile para rodar manualmente, ela executava em 0 (zero) segundos.
Fabrício, esse é aquele caso que o plano delas estão diferentes, por isso essa diferença no tempo, certo?
Certo. Os planos estão diferentes.
Só tem um pequeno grande detalhe: A query não pode ser alterada na aplicação.
O milagre tem que ser feito só no SQL Server.
Como podemos fazer isso? Query Store?
Query Store é um dos benefícios que temos quando migramos para o SQL Server 2016 ou superior.
Eu o utilizei para analisar algumas coisas, mas as queries tinham o query_id diferentes no Query Store. Não era 1 query com dois planos diferentes, onde damos um simples clique no Query Store para forçar o plano mais rápido e resolvemos nosso problema.
Mesmo com o mesmo código e parâmetros, lá no query store eram duas consultas completamente diferentes.
Como isso é possível Fabrício?
Isso acontece devido as SET OPTIONS que a aplicação está usando na conexão com o banco.
Rodei essa query abaixo para conferir o Plan_handle da query:
No resultado podemos ver a mesma query com o plan_handle diferente (destacado em vermelho):
Em seguida, joguei os 2 plan_handles para uma tabela temporária para validar as SET OPTIONS desses planos:
Resultado da query?
Repare que a coluna value dos 2 planos destacados estão com valores diferentes.
Jogando esses valores na query mágica que tem no Treinamento do Fabiano Amorim:
Primeiramente joguei o valor 266491:
Podem ver que temos CONCAT_NUL_YIELDS_NULL e ARITHABORT setado para esse plano.
Rodando a mesma query para o valor 262387:
As opções ARITHABORT e CONCAT_NULL_YIELDS_NULL não estão habilitadas para esse outro plano. Esse é o plano que estava lendo.
Abrindo uma conexão no SSMS e deixando essas opções como OFF, eu consegui rodar a query e ver ela demorando 5 minutos. Massa!
E agora Fabrício? Query Store não resolve, o que vamos fazer?
Vamos para a solução raiz utilizando PLAN GUIDES.
Por algum motivo, após a migração, o SQL Server estava estimando algo muito errado no plano quando vem com essas opções desligadas.
Criei um Plan Guide para que compilasse essa query novamente ao ser executada e pronto! Problema resolvido!
Segue o Plan Guide criado:
A Query que estava rodando em 5 minutos foi para 0 (zero) segundos.
O cliente tomou um susto quando executou novamente.
Tive que fazer isso para outras 5 queries do RM que estavam travando pelo mesmo motivo.
E isso galera!!! Essa solução resolveu o problema do TOTVS RM, mas isso também acontece com outras aplicações.
Espero que tenham curtido.
Segue um outro post onde falo sobre algumas dicas de migração:
Fica aqui um agradecimento ao Rodrigo Ribeiro e Fabiano Amorim que debateram esse problema comigo no Slack do #TeamFabricioLima.
Ao Fabiano, um obrigado pela conferência que fez comigo para discutir e testar a solução do problema. Que time é esse!!!
Atualizado no dia 07/10/2020:
Publiquei um curso com 11 horas de duração com toda minha experiência de anos no assunto e de dezenas de clientes Protheus atendidos:
Curso: Melhorando a Performance de Consultas no Totvs Protheus
Gravei uma aula grátis com 60 minutos de duração sobre o que você deve aprender para melhorar a performance no Protheus:
Gostou da dica?
Curta, comente, compartilhe com os coleguinhas…
Siga-nos no Linkedin, Youtube, Facebook e Instagram para receber dicas de leitura e eventos sobre SQL Server.
Abraços,
Fabrício Lima.
Microsoft Data Platform MVP
Consultor e Instrutor SQL Server
Trabalha com SQL Server desde 2006
Sensacional, Fabricio. Como sempre…
Obrigado Walter!