5 motivos para quem utiliza o Protheus (Totvs) contratar um DBA SQL Server
Olá Pessoal,
Trabalho com administração de banco de dados SQL Server Protheus desde 2009 em um ambiente que considero de grande porte:
- Base de Dados de 300 GB completamente compactada à nível de página, caso contrário já estaria passando de 1 TB de dados. (update em 28/09/2015 – 500 GB e 1.5 TB)
- 3.000 usuários simultâneos conectados no Top Connect.
- Tenho um Database Mirroring dessa base de dados do Protheus configurado para outro servidor para subir o ambiente em caso de desastre.
Vejo muita gente em fóruns ou listas de e-mails sobre SQL Server reclamando de alguns pontos da arquitetura do banco de dados Protheus.
Defendendo um pouco o lado da Totvs, o sistema deles é feito para ser executado em todos os grandes bancos de dados do mercado, então, acredito que por isso eles acabam fazendo algo um pouco mais genérico. Apesar se sempre me perguntar porque não existe algo do tipo na implantação de um ambiente protheus:
IF SGBD = ‘SQL Server’
exec Customizações SQL Server
IF SGBD = ‘Oracle’
exec Customizações Oracle
IF SGBD = ‘DB2’
exec Customizações DB2
A complexidade de fazer isso deve ser muito alta e\ou o investimento nessa estratégia não vale a pena financeiramente para a empresa.
Diante disso tudo, mesmo não sendo possível alterar queries padrões do sistema Protheus, existem uma série de ações que você pode fazer para melhorar a performance do seu ambiente Totvs Protheus com SQL Server. Por isso, se você tem problema de performance no seu ambiente Protheus e não quer comprar um servidor mais potente, o que normalmente é a indicação da Totvs, segue abaixo 5 motivos para você contratar um DBA ou uma Consultoria em SQL Server:
1 – Para realizar a criação de novos índices para o ambiente Protheus
Já começo pelo ponto que eu acho mais problemático no banco de dados Totvs. 99% dos índices do Protheus começam com a coluna FILIAL.
Existe uma tabela no Protheus chamada SE1010 (empresa 1). Em uma base da versão 10 padrão (sem customizações), essa tabela possui 25 índices. O índice clustered é a coluna R_E_C_N_O_ ( parte boa dos índices do Protheus) e, dos 24 índices nonclustered, 23 começam com a coluna E1_FILIAL.
Olhando como DBA, esses 23 índices são “praticamente” iguais, pois o SQL server montará a B-Tree do índice para fazer as buscas de forma mais rápida por essa coluna. Se você procurar por um código de cliente, uma data de emissão ou um código de documento, ao invés do Protheus fazer uma busca por esses campos, ele vai filtrar primeiro a filial e só depois filtrar o campo principal que você quer buscar. Isso gera uma perda de performance incrível. Se você só tem uma filial, o índice não filtra quase nada.
Para amenizar esse problema, no meu ambiente identifiquei a necessidade de criação de MUITOS índices customizados e isso melhorou demais o desempenho do nosso ambiente.
Como seu índice customizado via SQL Server não está no dicionário de dados do Protheus, quando você realizar alguma atualização maior no seu ambiente, os índices podem ser excluídos. Para resolver isso, deve ser criado um job para criar todos índices customizados caso eles ainda não existam (IF NOT EXISTS … CREATE INDEX …).
2 – Para desabilitar índices do Protheus *Necessário validação com seu suporte Totvs e homologação
Não podemos nunca excluir um índice do Protheus, todavia, existe uma solução de contorno para isso.
Ao invés de excluir um índice, no SQL Server você pode desabilitar um índice. Dessa forma, o índice continua existindo na tabela sys.indexes, mas agora com o valor da coluna is_disable = 1. Como o Protheus não usa hints para forçar a utilização de um índice (with index= XXX), o SQL Server é quem escolhe o índice que deve ser usado quando uma consulta é realizada na base. Caso o Protheus forçasse a utilização de um índice, ele não poderia ser desabilitado de forma alguma.
Desabilitando o índice, o SQL Server libera o espaço em disco ocupado por esse índice e não atualiza mais esse índice, reduzindo o custo das operações de I/O.
Existem muitos índices criados pelo Protheus que não são usados, ainda mais quando você criar os índices customizados. Por isso, desabilitar esses índices pode trazer grandes ganhos de performance.
Mas Fabrício, e se eu começar a usar uma nova funcionalidade do Protheus que utilizaria esses índices que foram desabilitados?
R: A necessidade de utilizar novos índices você tem que identificar com as queries mais lentas ou as que estão consumindo mais recursos no seu banco de dados. Assim, você pode criar os índices necessários e melhorar essas queries. Esse monitoramento deve ser constante.
Já homologuei e utilizei isso em produção (versão 10) e não tive problema com suporte da Totvs. Já vi pessoas da Totvs dizendo que eles podiam ser desabilitados e pessoas que usam Protheus dizendo que o suporte da Totvs não deixa realizar essa operação. Dessa forma, fica como dica para validarem com o suporte Totvs e homologar no seu Protheus antes de colocar em produção. Se o suporte dizer que não pode, peça uma explicação técnica para isso.
Da mesma forma que deve ser criado um job para a recriar os índices, deve se criado um job para desabilitar os índices novamente caso uma atualização do Protheus crie eles novamente.
3 – Para alterar o FILLFACTOR dos índices
Todos os índices do Protheus são criados com o FILLFACTOR de 100%. Isso significa que as páginas são preenchidas por completo, o que gera gera um aumento na fragmentação das páginas desses índices e também eleva o número de Page Splits no ambiente (operação de I/O que divide uma página em duas).
Para melhorar o desempenho do seu Protheus SQL, você pode analisar os índices que estão se fragmentando mais e/ou gerando mais PAGE SPLIT e alterar o FILLFACTOR desses índices.
Seguem dois artigos deste blog que podem te ajudar:
4 – Para realizar um expurgos de dados da base de produção
Se sua área de tributos\contabilidade precisa de cinco anos de dados ONLINE, deixe apenas cinco anos ONLINE. Se precisa de três anos, deixe apenas três anos.
Você pode criar uma ou várias bases de histórico para que os dados da base de produção sejam expurgados para essas bases. Dessa maneira, a performance do seu ambiente de produção aumentará pois serão menos dados para o banco de produção fazer uma busca.
5 – Para compactar tabelas e índices (disponível apenas na versão enterprise do SQL Server 2008/2008R2/2012)
Novamente utilizando a tabela SE1010 como exemplo, essa tabela possui na versão do Protheus 10 um total de 228 colunas. É isso mesmo, 228 colunas.
Achou isso impressionante?
Para aumentar o problema, a tabela possui uma constraint DEFAULT para cada uma dessas colunas. Caso seja um número ele preenche com zero, caso seja string ele preenche com espaço em branco. Exemplo de uma constraint na tabela SE1010:
Esse é um dos principais motivos que fazem a compactação de páginas de tabelas do Protheus ter GANHOS IMPRESSIONANTES.
Segue um exemplo de uma tabela antes e depois de uma compactação de todos os seus índices à nível de página:
Antes:
Depois:
De 100 GB baixou para apenas 19 GB. 81 GB de ganho de espaço em disco. Redução de 81% de espaço utilizando a compactação.
A compactação gera um aumento de consumo de CPU, mas além do ganho de espaço em disco ($$), otimiza a utilização da memória, pois como a tabela possui menos páginas, ela ocupa menos espaço em memória e mais tabelas conseguem permanecer no Data Cache do SQL. O Saldo final é um ambiente com uma performance melhor.
[Atualizado dia 17/11/2016] No Service Pack 1 (SP1) do SQL Server 2016 uma série de funcionalidades que antes eram exclusivas da edição Enterprise agora estão disponíveis nas edições Standard e Express. E uma dessas funcionalidades é a Compressão de Dados. Esse era o motivo que você estava esperando para migrar o seu SQL Server do Protheus/RM para a nova versão do SQL Server 2016. Como expliquei nesse post, a compressão de dados no Protheus é LINDA e agora está acessível para nós meros mortais que não temos condições de comprar a edição Enterprise.
Dica Extra – Não desabilite o auto create statistics
A indicação da Totvs é para você desabilitar a opção auto create e update statistics e criar uma rotina para atualizar as estatísticas.
A dica de criar uma rotina para atualizar as estatísticas é excelente e também faço isso, contudo, não desabilito as opções de estatísticas do SQL Server, pois penso que ele pode criar novas estatísticas e atualizar estatísticas de tabelas menores para otimizar a performance. As tabelas maiores que podiam causar uma perda de performance, teriam as estatísticas atualizadas somente na rotina da noite.
Segue post deste blog sobre esse assunto: Rotina para Atualizar as Estatísticas do seu Banco de Dados
ARTIGO sobre Protheus (Totvs)
Fiz uma busca no google e encontrei apenas o artigo abaixo do meu amigo Marcel Inowe (Blog|Twitter) falando sobre o assunto e nele tem uma dica que ainda não implantei em produção que é o particionamento de tabelas. Mais uma ação que posso realizar para melhorar o desempenho do meu ambiente SQL Server com Protheus. Seria o sexto motivo para contratar um DBA ou uma Consultoria SQL SERVER.
Artigo: http://4sqlserver.wordpress.com/2012/09/12/dicas-sobre-o-banco-de-dados-do-protheustotvs/
Finalizando esse post, para quem está com problema de performance OU não quer que um problema de performance aconteça em seu ambiente Protheus com SQL Server, você tem três opções:
- Caso você já tenha um DBA na sua empresa, ele pode trabalhar nessas customizações caso ainda não tenha feito.
- Caso sua empresa seja pequena e não tenha um DBA FULL Time, pode contratar meus serviços de Consultoria SQL Server ou o serviço de consultoria de outra empresa para implementar essas melhorias.
- Comprar um Hardware de maior desempenho, que na maioria das vezes é a solução indicada pela Totvs.
Antes de comprar um servidor novo para resolver um problema de lentidão no seu ambiente, contrate um especialista SQL Server e peça para ele fazer um Tuning no seu banco de dados. A economia de não comprar um novo Hardware vai pagar a consultoria e ainda sobrará $$!
Caso sua empresa não tenha um DBA FULL Time e você além de ser responsável pela aplicação do protheus, também é responsável pelo banco de dados, esse treinamento abaixo vai te ajudar muito:
Tarefas do Dia a Dia de um DBA: https://www.fabriciolima.net/blog/cursos-online/treinamento-tarefas-do-dia-a-dia-de-um-dba-online/
Se não possui ninguém interno para aprender a trabalhar com banco, posso ajudar com minha Consultoria de Tuning.
[Atualizado dia 29/05/2017] Já realizei consultoria em mais de 22 empresas com banco de dados Totvs Protheus ou RM e posso dar referências para contato.
[Atualizado dia 29/05/2017] Segue abaixo um caso real de migração do SQL Server 2008 para o SQL Server 2016:
https://www.fabriciolima.net/blog/2017/05/23/migrando-um-sql-server-2008-totvs-protheus-para-o-sql-server-2016-standard/
[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 dessa Dica?
Curta, comente, compartilhe…
Assine meu canal no Youtube e curta minha página no Facebook para receber Dicas de Leituras, Vídeos e Eventos sobre SQL Server.
Até a próxima.
Fabrício Lima
MCITP – Database Administrator
Consultor e Instrutor SQL Server
Trabalha com SQL Server desde 2006
Fabrício, excelente post. Atualmente trabalho com banco de dados de diversos tamanho, 300GB até 1.5TB – SQL Server 2008 R2 Enterprise. Há alguns dias analisei que o banco de dados estava crescendo bastante e após isso um Analista SAP expurgou alguns dados da tabela, porém o espaço livre não foi liberado para o S.O. Logo o espaço livre ficou guardado logicamente dentro dos arquivos de dados (assim penso eu). Ao meu ver é algo bom e ruim. Bom devido a possuir páginas vazias e na medida que o SQL Server for recebendo dados, com certeza ele irá para essas páginas, ruim porque estou “armazenando” um espaço não utilizável, mas levando em consideração que as páginas não utilizadas não estarão dentro do backup quando é feito (me corriga se eu estiver errado). Desde então, criei uma rotina para realizar algumas operações, dentre elas tive “pânico” no resultado de uma. Rebuild ou Reorganize. Tenho um script que analisa o nivel (avg) de fragmentação, se for acima de > 30, rebuild nos 3 (três) niveis da árvore, caso contrário apenas nas folhas. Após realizar a operação, analisei que esta tarefa consumiu 150GB, distribuido em 2 (dois) discos, ou seja, 75GB para cada um. Logo achei estranho não ter liberado esse espaço temporário, visto que a operação foi commitada com sucesso. Já passou por algo do gênero?
Att,
Lucas Saraiva
Ótimo post Fabrício. Será de grande ajuda quando me deparar com a base de dados desse sistema. Parabéns.
Obrigado Angelo.
Fabrício,
em relação á concorrência? 3.000 usuários simultâneos acessam os dados sobre qual isolation level? RCSI?
Isolation level READ COMMITTED (padrão).
Não fiz essa alteração.
Tenho problemas de locks em alguns momentos quando usuários fazem processamentos demorados no protheus, mas não é constante.
Olá Fabrício,
Muito legal o post.. Atualmente administro um ambiente SQL Server com SAP e com certeza é tudo muito parecido, principalmente na parte de índices.
Meus parabéns.
Att.,
Ismael Junior
Obrigado Ismael!!!
Ótimo artigo Fabrício. Eu pensei em alguns clientes fazer o uso da compactação mas sempre fiquei com receio do aumento de uso da CPU, mas passar a colocar em pauta de reunião de agora em diante e muito obrigado pela citação do meu post.
Abs
Beleza Marcel.
Monitorando o uso da CPU é tranquilo.
Compactação para Protheus vale a pena.
Valeu pelas informações, gostei muito!
Eu estou estudando efetuar replicação do banco de dados para uma empresa que tem filiais entre estados (e sobre de quedas quando a infra-estrutura falha).
Você aconselha? Já fez? Abraços.
Boa Tarde Kayo,
Ainda não tive oportunidade de trabalhar em um projeto desse.
Conselho é estudar bem e realizar muitos testes antes de subir em produção.
Bom dia Fabricio,
Muito bom o conteúdo do seu POST, estaremos substituindo o ERP hoje que utilizamos pelo Protheus, esse conteúdo vai ser de grande ajuda!
Obrigado Leonardo.
O Protheus não foi customizado para trabalhar com vários bancos,ele usa o TOP CONNECT ,que foi desenvolvido por uma empresa CHilena.
No fundo é uma gambiarra que transforma commandos XBASE(do clippao) em clausulas SQL padrão ANSI(que rodam em qualquer banco).
óbvio que qualquer estágiario escreve uma clausula SQL melhor que um negócio destes.
Fora que ele faz através de algoritimos ,loops via código muita coisa que poderia ser feita através de clausulas SQL,e o pior faz isto do lado do cliente.
Ou seja muitas vezes leva 3 tabelas inteiras par ao lado cliente,faz uma conta básica e despreza os dados depois,uma arquitetura da década de 70,80.
EM um ambiente destes só sendo louco para desprezar um DBA.
Ótimo post! Com banco realmente não dá para bancar o curioso. O barato sai caro com o tempo. Além do suporte que sem o DBA, não existirá.
Tenho acessado alguns blogs de Protheus e achei este também:
http://microsigadvpl.blogspot.com.br/
Parabéns pelo blog.
Arthur.
Obrigado Arthur.
Velho, assino embaixo de cada um dos itens que você desenvolveu nesse post. Atendo a clientes Totvs de todos os tamanhos dentro de Minas Gerais e praticamente todas elas sofrem de dor nos mesmos “calos” que você citou no post.
Vou passar a recomendar a leitura deste post.
Abraço !
Obrigado João!!!
Bom dia,
Fabricio muito bom artigo, Parabéns, tenho um ambiente parecido, só no lugar do Protheus é o RM com varios modulos diferentes, seria recomendado as mesmas praticas?
Boa Tarde Djones.
Sim.
Esses ambientes são muito parecidos. Já fiz consultorias em ambientes RM também.
Mandou bem, cara !!!
Abraços
Obrigado Júlio.
Fabricio
Existe uma técnica nativa do Dbaccess, até mesmo da época do TopConnect, que permite virtualizar estes índices do Protheus.
Funciona assim: Numa tabela do próprio Dbaccess que fica no banco, fica hospedada a informação de qual ambiente, tabela, indice e chave do indice, serão virtualizados.
Assim mesmo que exista o índice na SX1, o Dbaccess não irá criar o indice no banco de dados. Mas a instrução SQL de ordenação pelo indice tudo será respeitado.
Dessa forma você poderá criar alguns indices customizados de campos realmente chaves pelo Configurador do Protheus, por exemplo>
Tabela SE1 eu virturalizei 19 indices que não forma utilizados em Dbseeks do Dbaccess num determinado periodo de avaliação.
Assim eu posso criar um indice:
E1_CLIENTE+E1_LOJA+E1_VENCREA+E1_FILIAL ( coloco o campo filial no final )
E1_VALOR+E1_VENCREA+E1_FILIAL ( Para buscar por valor de título )
que terá muito mais performance em relatórios e ou customizações, ou necessidade de filtros dos usuários.
Abraços
Marcelo Alberto Lauschner.
Obrigado por compartilhar essa informação Marcelo.
Abraços.
Fabricio, seu post é um dos melhores que já li sobre o assunto, e ser elogiado pelo Julio Wittner, não é para qualquer um.
Show Erike.
Que bom que gostou.
Obrigado pelo feedback.
Excelente Post. Agradeço a contribuição!
Flws.
Que bom que ajudou Rômulo.
Fabrício qual é sua experiencia quanto a criação de um filegroup e um arquivo especifico para índices em ambientes TOTVS? Já realizou tal atividade? Na empresa em que trabalho acompanhamos sua saga para administrar o TOTVS e sempre tivemos bons resultados, e gostariamos de criar um arquivo e filegroup para índices pois temos disco em storage ocioso e ganharíamos em performance.
O que nos recomenda?
Obrigado.
Outro filegroup só ajuda se colocar em um disco diferente para que o SQL consiga usar dois discos ao mesmo tempo. Salvar no mesmo disco só vai te dar mais trabalho para administrar.
sim!! como eu disse temos esse disco que será alocado em LUN independente no storage, porém pelas características do TOTVS quanto a manipulação de índices, isso não acarretará problemas na leitura dos índices onde o TOTVS reclama se vc exclui fisicamente algum índice? Posso movê-los sem poblema?
Se suas LUNS usam discos independentes no storage, pode ajudar. Se usam o mesmo discos no fim das contas não deve mudar nada.
Usar filegroup diferente não vai parar o protheus. Ele nem sabe como isso fica nesse nivel. a propria totvs faz isso em alguns clientes.
Ótimo Artigo !
Obrigado Ricardo.
Caros colegas.
Dando um feedback sobre o status mais recente com relação ao Protheus, a partir de algumas versões (estou utilizando a 12.1.17) temos as seguintes funcionalidades disponibilizadas:
– Remoção de espaços adicionais à direita: Nas primeiras versões, para procurar manter o legado com as aplicações xBase, o sistema completava com espaços em branco o campo caracter até o seu tamanho. Num exemplo, se um campo de nome de cliente possui 40 caracteres de tamanho e preenchermos apenas com a letra “A” o sistema completava com 39 espaços em branco. A partir de algumas versões, habilitado o parâmetro TIMRIGHTSPACE=1 no dbAccess o sistema passa a preencher apenas com a informação de fato, economizando espaço no banco de dados;
– Aceitação de conteúdo NULL nos campos: A partir de determinada versão passa a ser aceito NULL nos campos. Deve-se habilitar AcceptNullContent=1 no dbAccess;
– Compressão: Passa-se a aceitar o parâmetro COMPRESSION=X onde X representa um método de compressão conforme o banco de dados. Para SQL Server, 1=compressão por linha e 2=compressão por página;
– Habilitando o parâmetro StartSysInDB=1 no appserver.ini habilitamos a inicialização do dicionário de dados no banco de dados onde as tabelas SX, o arquivo SIGAMAT,EMP, as tabelas XX, o arquivo de usuários, não são mais criados na pasta SYSTEM mas dentro do banco de dados.
Com estas melhorias observei ganho de performance e economia de espaço.
Estou testando neste momento e está funcional.
Atenciosamente,
Leandro Michelsen
Olá Leandro,
WOW. Feedback fantástico… Melhorias que não sabia que estavam surgindo no protheus.
Obrigado por compartilhar.
Boa tarde Leandro,
Dentro de qual TAG você colocou a COMPRESSION = 2 dentro do dbacess.ini? [GENERAL], [MSSQL] ?
Att. Beloni
Atenção, não habilitem AcceptNullContent=1 no dbAccess. Ocorreram falhas significativas de compatibilidade. Recentemente verifiquei que a própria TOTVS removeu qualquer menção essa chave no portal.
Bom dia Fabricio,
O Post já tem bastante tempo, mas só hoje estou passando por isso e gostaria de tirar uma dúvida, caso ainda receba notificação deste post.
Você mostra a diminuição de 100Gb para 19GB com a compressão, essa redução reflete no arquivo .mdf do banco de dados? Fizemos a compressão em nosso ambiente, mas o arquivo .mdf permaneceu com 500GB, aí ficamos na dúvida, depois da compressão por página é necessário fazer um shirink para diminuir o tamanho do arquivo .mdf do banco de dados?
Depois de rodar a compressão você tem que rodar um shrink… depois Rebuild dos indices pq shrink fragmenta…
O shrink que reduz o arquivo