- \n
- 5 motivos para quem utiliza o Protheus (Totvs) contratar um DBA SQL Server<\/a><\/li>\n
- Migrando um SQL Server 2008 Totvs Protheus para o SQL Server 2016 Standard<\/a><\/li>\n<\/ul>\n
Como j\u00e1 disse nesses posts, os \u00edndices padr\u00f5es do Protheus n\u00e3o s\u00e3o os melhores para todas as consultas que s\u00e3o realizadas pelo ERP. Se ningu\u00e9m nunca criou \u00edndices customizados no seu ambiente, certamente voc\u00ea tem margem para melhoria de performance no sistema da Totvs.<\/strong><\/p>\n
Nesse cen\u00e1rio, passo bastante tempo melhorando a performance de ambientes Protheus com a cria\u00e7\u00e3o de \u00edndices.<\/p>\n
E como eu fa\u00e7o isso Fabr\u00edcio?<\/strong><\/p>\n
O primeiro passo \u00e9 voc\u00ea identificar as queries mais demoradas, que normalmente s\u00e3o as queries que os usu\u00e1rios reclamam, pois ficam muito tempo esperando uma resposta das telas.<\/p>\n
Criando meus Alertas ou o meu CheckList, automaticamente voc\u00ea j\u00e1 implementa uma rotina para monitorar as queries que demoram mais de X segundos no seu BD:<\/p>\n
- \n
- V\u00eddeo: Criando 15 Alertas no SQL Server em apenas 5 minutos<\/a><\/li>\n
- Criando um E-mail de CheckList Di\u00e1rio no SQL Server<\/a><\/li>\n<\/ul>\n
Feito isso, agora \u00e9 analisar uma a uma as queries que mais consomem CPU<\/strong>, que fazem mais Leituras<\/strong> ou as queries que mais se repetem<\/strong> em nosso log.<\/p>\n
Para tentar ajudar voc\u00eas nessa atividade de an\u00e1lise de queries, vou realizar uma s\u00e9rie de posts curtos com a an\u00e1lise de 1 query por post<\/strong> para verem como a cria\u00e7\u00e3o de \u00edndices customizados se faz necess\u00e1ria no Protheus.<\/p>\n
Vamos come\u00e7ar por uma query bem simples, mas que \u00e9 um caso REAL de produ\u00e7\u00e3o<\/strong>.<\/p>\n
Em um dos meus clientes, peguei a seguinte query sendo executada algumas vezes durante o dia:<\/p>\n
SELECT R_E_C_N_O_\r\nFROM dbo.CT2010\r\nWHERE D_E_L_E_T_ = ' '\r\n AND ( CT2_CREDIT = '65454565464' )\r\nORDER BY CT2_FILIAL DESC ,\r\n CT2_DATA DESC ,\r\n CT2_LOTE DESC ,\r\n CT2_SBLOTE DESC ,\r\n CT2_DOC DESC ,\r\n CT2_LINHA DESC ,\r\n CT2_TPSALD DESC ,\r\n CT2_EMPORI DESC ,\r\n CT2_FILORI DESC ,\r\n CT2_MOEDLC DESC ,\r\n R_E_C_N_O_ DESC;\r\n<\/pre>\n
<\/p>\n
Sempre falo isso nas minhas palestras e treinamentos. A primeira coisa que voc\u00ea deve fazer ao come\u00e7ar a analisar uma query \u00e9<\/p>\n
set statistics io,time on -- para ver a quantidade de leitura e consumo de CPU da query\r\n--ctrl+M para ver o plano real da query ap\u00f3s a execu\u00e7\u00e3o<\/pre>\n
Ao setar essas op\u00e7\u00f5es e executar a query, podemos ver o plano de execu\u00e7\u00e3o abaixo:<\/p>\n
![\"\"](\"https:\/\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2017\/12\/queryprotheysparte1_1.png\")
<\/p>\n<\/p>\n
A query demorou 5.7 segundos<\/strong> e teve o seguinte consumo:<\/p>\n
Table 'CT2010'. Scan count 3, logical reads 91143\r\n\r\nSQL Server Execution Times:\r\n\r\n\u00a0\u00a0 CPU time = 10531 ms,\u00a0 elapsed time = 5764 ms<\/pre>\n
O SQL est\u00e1 fazendo um Scan<\/strong> em um \u00edndice nonclustered<\/strong>, mas como ele n\u00e3o tem todas as colunas que precisa nesse \u00edndice, depois ainda faz um Key Lookup<\/strong>\u00a0no \u00edndice clustered<\/strong> para pegar o resto das colunas que faltam.<\/p>\n
Todos os \u00edndices dessa tabela come\u00e7am com a coluna CT2_FILIAL<\/strong>, que n\u00e3o \u00e9 uma coluna com dados muito seletivos para descer r\u00e1pido na \u00e1rvore do \u00edndice e dar o resultado da query.<\/p>\n
No caso dessa query, o pr\u00f3prio SQL Server est\u00e1 te sugerindo um \u00edndice.<\/p>\n
P\u00f4 Fabr\u00edcio… a\u00ed ficou f\u00e1cil…. nem precisava de um post…<\/strong><\/p>\n
Mas mesmo que ele n\u00e3o te sugerisse, quando voc\u00ea v\u00ea uma cl\u00e1usula como essa em um WHERE, o SQL Server est\u00e1 praticamente te implorando para voc\u00ea criar um \u00edndice por essa coluna (exceto quando essa coluna n\u00e3o \u00e9 muito seletiva).<\/p>\n
AND ( CT2_CREDIT = '65454565464' )<\/pre>\n
Palavras do SQL Server para voc\u00ea que est\u00e1 analisando a query:<\/strong><\/p>\n
- \n
- “Meu amigo, por favor, crie um \u00edndice nessa coluna CT2_CREDIT”<\/em><\/li>\n
- “Estou trabalhando demais, economiza meu processamento a\u00ed. Please!!!”<\/em><\/li>\n
- “Me ajuda que eu te ajudo DBA!”<\/em><\/li>\n
- “Dev, entende como esse neg\u00f3cio de \u00edndice funciona e cria um a\u00ed para mim… help!! “<\/li>\n<\/ul>\n
Nossa Resposta:<\/strong> OK SQL. Deixa comigo SQL. #tamojunto<\/p>\n
Quando o SQL te sugere um \u00edndice voc\u00ea faz o que? Cria n\u00e9! L\u00f3gico! Mel na chupeta! Muito f\u00e1cil! Easy!<\/p>\n
S\u00f3 tem um por\u00e9m. Essa tabela tem 48 milh\u00f5es de registros<\/strong>.<\/p>\n
Olha o tamanho do \u00edndice que o SQL sugeriu criar:<\/p>\n
CREATE NONCLUSTERED INDEX CT2010W01\r\nON [dbo].[CT2010] ([CT2_CREDIT],[D_E_L_E_T_])\r\nINCLUDE ([CT2_FILIAL],[CT2_DATA],[CT2_LOTE],[CT2_SBLOTE],[CT2_DOC],[CT2_LINHA],[CT2_MOEDLC],[CT2_EMPORI],[CT2_FILORI],[CT2_TPSALD])\r\n<\/pre>\n
Vai demorar bastante para criar e ficar bem grande. Se fosse t\u00e3o f\u00e1cil assim o cliente nem nos contratava.<\/p>\n
E se eu tentar criar um \u00edndice s\u00f3 pela coluna\u00a0CT2_CREDIT<\/strong>? Ser\u00e1 que j\u00e1 \u00e9 suficiente para resolver meu problema e fazer a query rodar r\u00e1pida e sem consumir muitos recursos?<\/p>\n
Se a quantidade de registros retornada pela query for baixa, fazer um lookup<\/strong> n\u00e3o \u00e9 t\u00e3o custoso assim. Vamos tentar:<\/p>\n
CREATE NONCLUSTERED INDEX CT2010W01\r\nON [dbo].[CT2010] ([CT2_CREDIT])\r\nwith(FILLFACTOR=90,DATA_COMPRESSION=PAGE)<\/pre>\n
A cria\u00e7\u00e3o desse \u00edndice levou 5 minutos<\/strong> (discos SSD) e o \u00edndice ficou com o 420 MB<\/strong> de tamanho. Imagina o \u00edndice sugerido com muitas colunas.<\/p>\n
WOW!!!!<\/strong> Ao rodar a query novamente ela passou a rodar de forma instant\u00e2nea:<\/p>\n
Table 'CT2010'. Scan count 1, logical reads 4\r\n\r\nSQL Server Execution Times:\r\nCPU time = 0 ms, elapsed time = 44 ms.<\/pre>\n
Esse \u00e9 o novo plano da query:<\/p>\n
![\"\"](\"https:\/\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2017\/12\/queryprotheysparte1_2.png\")
<\/p>\nAquele \u00edndice gigante que o SQL sugeriu, iria fazer apenas um seek<\/strong>. O meu \u00edndice que \u00e9 bem menor, fez o seek + lookup<\/strong>. Contudo, podemos ver que j\u00e1 resolveu totalmente o problema da query.<\/p>\n
Dessa maneira, decidi manter esse \u00edndice menor para ter menos impacto na manuten\u00e7\u00e3o dele.<\/p>\n
Ap\u00f3s criarmos um \u00edndice, conseguimos acompanhar se ele est\u00e1 sendo bem utilizado ou n\u00e3o com a query abaixo:<\/p>\n
select getdate(), o.Name,i.name, s.user_seeks,s.user_scans,s.user_lookups, s.user_Updates,\r\nisnull(s.last_user_seek,isnull(s.last_user_scan,s.last_User_Lookup)) Ultimo_acesso,fill_factor\r\nfrom sys.dm_db_index_usage_stats s\r\njoin sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id\r\njoin sys.sysobjects o on i.object_id = o.id\r\nwhere s.database_id = db_id() and o.name in ('CT2010') \r\norder by s.user_seeks + s.user_scans + s.user_lookups desc<\/pre>\nBem legal para ver se nosso \u00edndice est\u00e1 sendo MUITO utilizado ou pouco. Se por acaso um \u00edndice customizado parar de ser utilizado, sua fun\u00e7\u00e3o \u00e9 excluir esse \u00edndice para que ele n\u00e3o gere mais I\/O sem necessidade (monitoramento cont\u00ednuo<\/strong>).<\/p>\n
Olhando no Trace<\/strong><\/a>\u00a0de queries demoradas, a query ficou t\u00e3o r\u00e1pida que nem aparece mais l\u00e1. Tenho que apelar e colocar um waitfor delay<\/strong> para poder visualizar e comparar:<\/p>\n
![\"\"](\"https:\/\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2017\/12\/queryprotheysparte1_3.png\")
<\/p>\nEla rodou em 0,07 segundos<\/strong> e a diferen\u00e7a de leituras de p\u00e1ginas (91 mil para 145)<\/strong> e do consumo de CPU (10 mil para 15)<\/strong> \u00e9 consider\u00e1vel conforme pode ser visto acima.<\/p>\n
\u00c9 isso ai pessoal, essa \u00e9 a parte 1<\/strong> dessa s\u00e9rie de posts onde faremos a an\u00e1lise de queries em um ambiente real do Protheus.<\/p>\n
<\/p>\n
Atualizado no dia 07\/10\/2020:<\/strong><\/span><\/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:\u00a0Melhorando 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
https:\/\/www.youtube.com\/watch?v=BKmUa3aZn6s<\/a><\/div>\n<\/div>\n<\/div>\n<\/div>\nGostou desse Post?<\/b><\/p>\n
Curta, comente, compartilhe com os coleguinhas\u2026<\/p>\n
Assine meu canal no\u00a0Youtube<\/a>\u00a0e curta minha\u00a0P\u00e1gina no Facebook<\/a>\u00a0para receber Dicas de Leituras e Eventos sobre SQL Server.<\/p>\n
At\u00e9 a pr\u00f3xima.<\/p>\n
Abra\u00e7os,<\/p>\n
Fabr\u00edcio Lima<\/p>\n
Microsoft Data Platform MVP<\/p>\n
Consultor e Instrutor SQL Server<\/p>\n
Trabalha com SQL Server desde 2006<\/p>\n","protected":false},"excerpt":{"rendered":"
Fala Pessoal, No meu dia a dia de Consultor SQL Server, atendo muitos clientes com Totvs Protheus e SQL Server. Inclusive j\u00e1 compartilhei algumas experi\u00eancias com voc\u00eas aqui no Blog: 5 motivos para quem utiliza o Protheus (Totvs) contratar um DBA SQL Server Migrando um SQL Server 2008 Totvs Protheus para o SQL Server 2016 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[3,942,991,1248,33,280],"tags":[15,1031,1032,1101,1099,1100,1097,1098,1096,1048,1044,1103,942,1102,1595,1054,23,1029,1030,1088,1089,1025,1026,1049,1045,110,1034,1033,1597,1016,1040,1019,1598,1035,1042,1023,1024,1596,1046,1021,1020,1039,1053,1022,1018,1059,1047,1041,1036,1052,1060,1038,1051,33,1057,1017,1015,1055,1050,1043],"class_list":["post-8435","post","type-post","status-publish","format-standard","hentry","category-administracao-de-bd","category-consultoria-sql-server","category-dba-remoto","category-queries-do-dia-a-dia","category-sql-server","category-virtual-pass-br","tag-banco-de-dados","tag-banco-protheus","tag-banco-totvs","tag-consultor-crm","tag-consultor-datasul","tag-consultor-microsiga","tag-consultor-protheus","tag-consultor-rm","tag-consultor-totvs","tag-consultoria-microsiga","tag-consultoria-protheus","tag-consultoria-rm","tag-consultoria-sql-server","tag-consultoria-totvs","tag-crm-datasul-datasul-erp","tag-datasul-totvs","tag-dba","tag-dba-protheus","tag-dba-totvs","tag-dica-banco-de-dados-protheus","tag-dicas-banco-de-dados-protheus","tag-erp","tag-erp-com-sql-server","tag-erp-microsiga","tag-erp-protheus","tag-indice","tag-lentidao-banco-de-dados","tag-lentidao-protheus","tag-lentidao-protheus-12","tag-microsiga","tag-microsiga-protheus","tag-microsiga-sql-server","tag-migracao-protheus-12","tag-performance-protheus","tag-protheus","tag-protheus-10","tag-protheus-11","tag-protheus-12","tag-protheus-microsiga","tag-protheus-microsoft-sql-server","tag-protheus-sql-server","tag-protheus-totvs","tag-rm-sistemas","tag-rm-sql-server","tag-siga","tag-sistema-crm","tag-sistema-microsiga","tag-sistema-protheus","tag-sistema-rm","tag-sistema-rm-totvs","tag-sistema-wms","tag-sistemas-de-gestao","tag-sistemas-erp","tag-sql-server","tag-suporte-totvs","tag-top-connect","tag-totvs","tag-totvs-datasul","tag-totvs-microsiga","tag-totvs-protheus"],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/8435","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/comments?post=8435"}],"version-history":[{"count":6,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/8435\/revisions"}],"predecessor-version":[{"id":13848,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/8435\/revisions\/13848"}],"wp:attachment":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/media?parent=8435"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/categories?post=8435"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/tags?post=8435"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- “Estou trabalhando demais, economiza meu processamento a\u00ed. Please!!!”<\/em><\/li>\n
- Criando um E-mail de CheckList Di\u00e1rio no SQL Server<\/a><\/li>\n<\/ul>\n
- Migrando um SQL Server 2008 Totvs Protheus para o SQL Server 2016 Standard<\/a><\/li>\n<\/ul>\n