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:
|
1 2 3 4 5 |
SELECT deqs.plan_handle,dest.text FROM sys.dm_exec_query_stats as deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) as dest WHERE dest.text like '%SELECT A.CODCOLIGADA, A.NUMEROCONTA, A.SEQUENCIALCONTA, A.SEQPARCIAL%' order by deqs.total_logical_reads desc |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
drop table if exists #PlanHandles SELECT distinct deqs.plan_handle into #PlanHandles FROM sys.dm_exec_query_stats as deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) as dest WHERE dest.text like '%SELECT A.CODCOLIGADA, A.NUMEROCONTA%' and dest.text not like '%dm_exec_query_stats%' SELECT decp.plan_handle, epa.attribute, epa.[value], deqp.query_plan,* FROM sys.dm_exec_cached_plans as decp OUTER APPLY sys.dm_exec_plan_attributes(decp.plan_handle) AS epa inner join #PlanHandles as ph on ph.plan_handle = decp.plan_handle CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) as deqp WHERE epa.attribute in ('set_options') ; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
declare @set_options int = 266491 if ((1 & @set_options) = 1) print 'ANSI_PADDING' if ((4 & @set_options) = 4) print 'FORCEPLAN' if ((8 & @set_options) = 8) print 'CONCAT_NULL_YIELDS_NULL' if ((16 & @set_options) = 16) print 'ANSI_WARNINGS' if ((32 & @set_options) = 32) print 'ANSI_NULLS' if ((64 & @set_options) = 64) print 'QUOTED_IDENTIFIER' if ((128 & @set_options) = 128) print 'ANSI_NULL_DFLT_ON' if ((256 & @set_options) = 256) print 'ANSI_NULL_DFLT_OFF' if ((512 & @set_options) = 512) print 'NoBrowseTable' if ((4096 & @set_options) = 4096) print 'ARITHABORT' if ((8192 & @set_options) = 8192) print 'NUMERIC_ROUNDABORT' if ((16384 & @set_options) = 16384) print 'DATEFIRST' if ((32768 & @set_options) = 32768) print 'DATEFORMAT' if ((65536 & @set_options) = 65536) print 'LanguageID' |
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!