{"id":10154,"date":"2019-02-19T09:16:05","date_gmt":"2019-02-19T12:16:05","guid":{"rendered":"http:\/\/www.fabriciolima.net\/?p=10154"},"modified":"2020-10-07T08:36:51","modified_gmt":"2020-10-07T11:36:51","slug":"melhorando-a-performance-de-consultas-no-totvs-rm-apos-uma-migracao-para-o-sql-server-2016","status":"publish","type":"post","link":"https:\/\/fabriciolima.net\/blog\/2019\/02\/19\/melhorando-a-performance-de-consultas-no-totvs-rm-apos-uma-migracao-para-o-sql-server-2016\/","title":{"rendered":"Melhorando a performance de consultas no TOTVS RM ap\u00f3s uma migra\u00e7\u00e3o para o SQL Server 2016"},"content":{"rendered":"

Fala Pessoal,<\/p>\n

Hoje vou compartilhar para voc\u00eas um caso real e interessante que passamos ao migrar um banco de dados TOTVS RM<\/strong> do SQL Server 2008 para o SQL Server 2016.<\/p>\n

Fizemos a migra\u00e7\u00e3o e, como j\u00e1 estamos acostumados, algumas consultas passaram a ficar lentas ap\u00f3s a migra\u00e7\u00e3o, mesmo sem mudar o n\u00edvel de compatibilidade das bases.<\/p>\n

Nesse ambiente, o sistema ainda era antigo e estavam usando o BDE<\/strong> para se comunicar com o banco de dados.<\/p>\n

As queries com problema batiam no banco da forma abaixo com um sp_executesql<\/strong>:<\/p>\n

“exec sp_executesql N’SELECT A.CODCOLIGADA, A.NUMEROCONTA, A.SEQUENCIALCONTA, A.SEQPARCIAL, A.SEQDEBITOMATMED, A.IDPRD, A.FATORCONVERSAOMATMED, colunas from tabelas WHERE filtros”<\/em><\/strong><\/p>\n

Essa query demorava 5 minutos<\/strong> quando rodava na aplica\u00e7\u00e3o. Mas quando eu pegava o mesmo c\u00f3digo\/par\u00e2metros dela no profile para rodar manualmente, ela executava em 0 (zero) segundos<\/strong>.<\/p>\n

Fabr\u00edcio, esse \u00e9 aquele caso que o plano delas est\u00e3o diferentes, por isso essa diferen\u00e7a no tempo, certo?\u00a0\u00a0<\/strong><\/p>\n

Certo. Os planos est\u00e3o diferentes.<\/p>\n

S\u00f3 tem um pequeno grande detalhe: A query n\u00e3o pode ser alterada na aplica\u00e7\u00e3o.<\/p>\n

O milagre tem que ser feito s\u00f3 no SQL Server.<\/p>\n

Como podemos fazer isso? Query Store?<\/strong><\/p>\n

Query Store \u00e9 um dos benef\u00edcios que temos quando migramos para o SQL Server 2016 ou superior.<\/p>\n

Eu o utilizei para analisar algumas coisas, mas as queries tinham o query_id diferentes no Query Store. N\u00e3o era 1 query com dois planos diferentes, onde damos um simples clique no Query Store para for\u00e7ar o plano mais r\u00e1pido e resolvemos nosso problema.<\/p>\n

Mesmo com o mesmo c\u00f3digo e par\u00e2metros, l\u00e1 no query store eram duas consultas completamente diferentes.<\/p>\n

Como isso \u00e9 poss\u00edvel Fabr\u00edcio?<\/strong><\/p>\n

Isso acontece devido as\u00a0SET OPTIONS<\/strong>\u00a0que a aplica\u00e7\u00e3o est\u00e1 usando na conex\u00e3o com o banco.<\/p>\n

Rodei essa query abaixo para conferir o Plan_handle da query:<\/p>\n

SELECT deqs.plan_handle,dest.text\r\nFROM   sys.dm_exec_query_stats as deqs\r\nCROSS  APPLY sys.dm_exec_sql_text(deqs.sql_handle) as dest\r\nWHERE  dest.text like '%SELECT A.CODCOLIGADA, A.NUMEROCONTA, A.SEQUENCIALCONTA, A.SEQPARCIAL%'\r\norder by deqs.total_logical_reads desc\r\n<\/pre>\n
No resultado podemos ver a mesma query com o plan_handle diferente (destacado em vermelho):<\/p>\n
\"\"<\/p>\n
Em seguida, joguei os 2 plan_handles para uma tabela tempor\u00e1ria para validar as SET OPTIONS<\/strong> desses planos:<\/p>\n
drop table if exists #PlanHandles\r\n\r\nSELECT distinct deqs.plan_handle\r\ninto #PlanHandles\r\nFROM   sys.dm_exec_query_stats as deqs\r\nCROSS  APPLY sys.dm_exec_sql_text(deqs.sql_handle) as dest\r\nWHERE  dest.text like '%SELECT A.CODCOLIGADA, A.NUMEROCONTA%'\r\nand dest.text not like '%dm_exec_query_stats%'\r\n\r\nSELECT decp.plan_handle, epa.attribute, epa.[value], deqp.query_plan,*\r\nFROM sys.dm_exec_cached_plans as decp  \r\nOUTER APPLY sys.dm_exec_plan_attributes(decp.plan_handle) AS epa  \r\ninner join #PlanHandles as ph on ph.plan_handle = decp.plan_handle\r\nCROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) as deqp\r\nWHERE epa.attribute in ('set_options') ;  \r\n<\/pre>\n
Resultado da query?<\/p>\n
\"\"<\/p>\n
Repare que a coluna value<\/strong> dos 2 planos destacados est\u00e3o com valores diferentes.<\/p>\n
Jogando esses valores na query m\u00e1gica que tem no Treinamento do Fabiano Amorim<\/strong><\/a>:<\/p>\n
declare @set_options int = 266491\r\n\r\nif ((1 & @set_options) = 1) print 'ANSI_PADDING'\r\nif ((4 & @set_options) = 4) print 'FORCEPLAN'\r\nif ((8 & @set_options) = 8) print 'CONCAT_NULL_YIELDS_NULL'\r\nif ((16 & @set_options) = 16) print 'ANSI_WARNINGS'\r\nif ((32 & @set_options) = 32) print 'ANSI_NULLS'\r\nif ((64 & @set_options) = 64) print 'QUOTED_IDENTIFIER'\r\nif ((128 & @set_options) = 128) print 'ANSI_NULL_DFLT_ON'\r\nif ((256 & @set_options) = 256) print 'ANSI_NULL_DFLT_OFF'\r\nif ((512 & @set_options) = 512) print 'NoBrowseTable'\r\nif ((4096 & @set_options) = 4096) print 'ARITHABORT'\r\nif ((8192 & @set_options) = 8192) print 'NUMERIC_ROUNDABORT'\r\nif ((16384 & @set_options) = 16384) print 'DATEFIRST'\r\nif ((32768 & @set_options) = 32768) print 'DATEFORMAT'\r\nif ((65536 & @set_options) = 65536) print 'LanguageID'<\/pre>\n
Primeiramente joguei o valor 266491:<\/strong><\/p>\n
\"\"<\/p>\n
Podem ver que temos CONCAT_NUL_YIELDS_NULL<\/strong> e ARITHABORT<\/strong> setado para esse plano.<\/p>\n
Rodando a mesma query para o valor\u00a0262387<\/strong>:<\/p>\n
 <\/p>\n
\"\"<\/p>\n
As op\u00e7\u00f5es\u00a0ARITHABORT<\/strong> e\u00a0CONCAT_NULL_YIELDS_NULL<\/strong> n\u00e3o est\u00e3o habilitadas para esse outro plano. Esse \u00e9 o plano que estava lendo.<\/p>\n
Abrindo uma conex\u00e3o no SSMS e deixando essas op\u00e7\u00f5es como OFF, eu consegui rodar a query e ver ela demorando 5 minutos. Massa!<\/p>\n
E agora Fabr\u00edcio? Query Store n\u00e3o resolve, o que vamos fazer?<\/strong><\/p>\n
Vamos para a solu\u00e7\u00e3o raiz utilizando PLAN GUIDES<\/strong>.<\/p>\n
Por algum motivo, ap\u00f3s a migra\u00e7\u00e3o,\u00a0 o SQL Server estava estimando algo muito errado no plano quando vem com essas op\u00e7\u00f5es desligadas.<\/p>\n
Criei um Plan Guide<\/strong> para que compilasse essa query novamente ao ser executada e pronto! Problema resolvido!<\/p>\n
Segue o Plan Guide criado:<\/p>\n
\"\"<\/p>\n
A Query que estava rodando em 5 minutos foi para 0 (zero) segundos. <\/strong><\/p>\n
O cliente tomou um susto quando executou novamente.<\/p>\n
Tive que fazer isso para outras 5 queries do RM<\/strong> que estavam travando pelo mesmo motivo.<\/p>\n
E isso galera!!! Essa solu\u00e7\u00e3o resolveu o problema do TOTVS RM<\/strong>, mas isso tamb\u00e9m acontece com outras aplica\u00e7\u00f5es<\/strong>.<\/p>\n
Espero que tenham curtido.<\/p>\n
Segue um outro post onde falo sobre algumas\u00a0 dicas de migra\u00e7\u00e3o:<\/p>\n
https:\/\/www.fabriciolima.net\/blog\/2017\/05\/23\/migrando-um-sql-server-2008-totvs-protheus-para-o-sql-server-2016-standard\/<\/a><\/p>\n
Fica aqui um agradecimento ao Rodrigo Ribeiro<\/a> e Fabiano Amorim<\/a> que debateram esse problema comigo no Slack do #TeamFabricioLima<\/strong>.<\/p>\n
Ao Fabiano, um obrigado pela confer\u00eancia que fez comigo para discutir e testar a solu\u00e7\u00e3o do problema. Que time \u00e9 esse!!!<\/strong><\/p>\n
 <\/p>\n
Atualizado no dia 07\/10\/2020:<\/strong><\/p>\n
Publiquei um curso com 11 horas de dura\u00e7\u00e3o com toda minha experi\u00eancia de anos no assunto e de dezenas de clientes Protheus atendidos:<\/p>\n
Curso: Melhorando a Performance de Consultas no Totvs Protheus<\/a><\/p>\n
Gravei uma aula gr\u00e1tis com 60 minutos de dura\u00e7\u00e3o sobre o que voc\u00ea deve aprender para melhorar a performance no Protheus:<\/p>\n