Loading…

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:

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:

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:

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 ARITHABORTCONCAT_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:

https://www.fabriciolima.net/blog/2017/05/23/migrando-um-sql-server-2008-totvs-protheus-para-o-sql-server-2016-standard/

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

2 thoughts on “Melhorando a performance de consultas no TOTVS RM após uma migração para o SQL Server 2016

Deixe uma resposta

%d blogueiros gostam disto: