Como podemos te ajudar?

  1. Senior
  2. TECNOLOGIA
  3. Tecnologia | G5/G6

    TECNOLOGIA - Performance/Lentidão/Travamentos - Quais as configurações adequadas para utilização do sistema em base de dados Microsoft SQLServer

    Felipe Nascimento
    • Atualizado

    Dúvida

    Quais as configurações adequadas para utilização do sistema em base de dados Microsoft SQLServer?

     

    Solução

    Todas as configurações e recomendações para utilização dos sistemas Senior de Tecnologia G5/G6 podem ser encontradas na Documentação da Senior, especificamente na parte de Tecnologia (Clique aqui para acessar a documentação).

    Clique aqui para verificar especificamente informações sobre o Banco de Dados Microsoft SQLServer dentro da Documentação da Tecnologia.

     

    Neste artigo você encontrará orientações abrangentes sobre o configurações adequadas para utilização do sistema em base de dados Microsoft SQLServer, visando melhorias de performance. Em diversos momentos você poderá ser redirecionado para execução de processos que já estão descritos na Documentação da Tecnologia.

     

    Importante

    Verifique configurações pertinentes a comunicação com banco de dados também descritas no artigo ERP - Performance/Lentidão/Travamentos - Quais são as orientações gerais para otimização de Performance (configurações dentro do SeniorConfigCenter).

     

    Atenção

    • As alterações de eventuais parâmetros do banco de dados descritas neste artigo devem ser executada com acompanhamento do DBA da sua empresa e a recomendação é que essas alterações sejam realizadas em ambiente de Homologação para prévia validação e que sejam replicadas em ambiente de Produção mediante validação de janela de alteração adequada com o seu ambiente/volume de dados. As alterações podem ser realizadas em momentos separados, ou seja, não é obrigatório que todos os parâmetros que não estejam corretos sejam alterados ao mesmo tempo;
    • É obrigatória a execução de backup da base de dados pelo DBA antes da execução de qualquer processo descrito neste artigo.

     

     

    1. Parâmetro READ_COMMITED_SNAPSHOT (Isolation Level)

    Este parâmetro deve estar habilitado obrigatoriamente

    Para maiores informações sobre a configuração, clique aqui para acessar a documentação.

     

    Explicação breve sobre READ_COMMITED_SNAPSHOT (Isolation Level)

    Isolation Level é o recurso que define o controle de acesso multiusuário à base de dados. O comportamento do banco de dados Microsoft SQLServer para controle de acesso multi-usuário é:
    em uma transação de alteração os dados são travados tanto para alteração quanto para
    leitura de outras instâncias (conexões). Essa característica é funcional mas não é a mais
    indicada para os sistemas Senior. A característica com maior benefício para os sistemas
    Senior é a leitura de dados mesmo que estes dados estejam sendo alterados por outra instância que esteja em transação, os dados são apresentados no estado em que estavam antes de a outra instância abrir a transação. Por este motivo o parâmetro READ_COMMITTED_SNAPSHOT deve estar habilitado, pois é a opção para que o isolation level READ_COMMITED funcione criando SNAPSHOTs a nível de comandos.

     

     

    Para que verificar o estado atual do parâmetro e também ativá-lo, verifique as informações abaixo.

     

    1.1. Para verificar se o parâmetro está habilitado, execute o select abaixo:

    SELECT NAME, IS_READ_COMMITTED_SNAPSHOT_ON FROM SYS.DATABASES;

     

    Caso o retorno do comando seja igual a 1 quer dizer que o parâmetro está devidamente ativado.

    Caso o retorno do comando seja igual a 0 quer dizer que o parâmetro está desativado, e deve haver ação do DBA da sua empresa.

     

    1.2. Para ativar o parâmetro, execute o comando indicado abaixo:

     

    Importante

    A ativação deste parâmetro deverá ser realizada pelo DBA da sua empresa e dependerá da reinicialização de todo o ambiente dos sistemas para ter efeito.

     

    ALTER DATABASE <DATABASE_NAME> SET READ_COMMITTED_SNAPSHOT ON

     

     

    2. Parâmetro LOCK ESCALATION

    O parâmetro LOCK ESCALATION deve estar desabilitado para todas as tabelas do banco de dados.

    Para maiores informações sobre a configuração, clique aqui para acessar a documentação.

     

    Esta é uma configuração realizada a nível de tabela de cada base de dados.

     

    Para que verificar o estado atual do parâmetro e também desativá-lo, verifique as informações abaixo.

     

    2.1. Para verificar se o parâmetro está habilitado, execute o select abaixo:

    SELECT NAME, LOCK_ESCALATION, LOCK_ESCALATION_DESC FROM SYS.TABLES WHERE LOCK_ESCALATION=0;

     

    Importante

    O comando acima está filtrando explicitamente apenas tabelas onde o Lock Escalation esteja ativado. Então se houver retorno de alguma tabela no comando, quer dizer que existe ação a ser realizada. Ou seja:

    • Caso o parâmetro LOCK_ESCALATION seja igual a 1 para a tabela quer dizer que o parâmetro está devidamente desativado (o que está correto).
    • Caso o parâmetro LOCK_ESCALATION seja igual a 0 quer dizer que o parâmetro está ativado, e deve haver ação do DBA da sua empresa.

     

    2.2. Para desativar o parâmetro, execute o processo indicado abaixo pelo CBDS;

     

    Importante

     

    2.2.1. Acesse o CBDS;

     

    2.2.2. Acione o menu Arquivo / Conectar;

     

    2.2.3. Selecione o arquivo de Configuração da base de dados onde você realizará;

     

    2.2.4. Na tela de Login, selecione a 'Forma de Logon' como 'Logon no banco de dados' e digite a senha do usuário do banco de dados:

     

     

    2.2.5. Após realizar o Login, acesse o menu Ferramentas / Recursos de Banco / Lock Escalation das Tabelas:

     

    2.2.6. Verifique para que todas as tabelas estejam listadas do lado esquerdo da tela (na parte de 'Lock Escalation Desabilitado para'), conforme exemplo abaixo:

     

    2.2.7. Ao mover as tabelas para a seção correta, clique em OK. Será exibida a mensagem Tem certeza que deseja alterar as configurações? Você deve clicar na opção 'Sim'. Neste momento será executada a alteração do parâmetro nas tabelas do banco de dados selecionadas.

     

    Importante

    O procedimento será aplicado apenas para tabelas que constam dentro do TBS do sistema. Caso na sua empresa tenha sido criada alguma tabela na base de dados que não tenha sido adicionada no TBS, você deverá acionar o DBA da sua empresa para que ele possa fazer tratativa manualmente diretamente no banco de dados para desativar o Lock Escalation para essas tabelas de forma manual.

     

     

     

    3. Índices Clusterizados

    Para utilização dos sistemas Senior de Tecnologia G5/G6 com performance ideal, é necessário que os índices estejam clusterizados no Microsoft SQLServer. Verifique abaixo os passos para ativar os índices clusterizados através do CBDS.

     

    Atenção

    • Para realização desse processo você deve possuir acompanhamento do DBA da sua empresa;
    • Antes da realização desse processo em ambiente de Produção, você deve validar estimativa de execução do processo em ambiente de Homologação, para validar a janela de indisponibilidade do sistema;
    • É obrigatória a execução de backup da base de dados pelo DBA antes da execução desse processo.
    • O processo deve ser executado com todos os usuários deslogados do sistema e sem nenhum processo acessando a base do sistema. Se necessário, utilize o assinalamento de base em Manutenção do CBDS. Você pode procurar pelo termo 'Assinalamento de base em manutenção' no Portal da Documentação da Senior para maiores informações sobre esse tema;
      • A realização do processo com algum processo/procedimento sendo executado em paralelo no sistema poderá afetar a integridade da base de dados, uma vez de que durante o processo existe a exclusão total das consistências da base de dados, com posterior recriação;
    • Para verificar a documentação sobre a rotina de Clusterização, você pode procurar pelo termo 'Configuração de Índice Clustered em Microsoft SQL Server' no Porta da Documentação.

     

    3.1. Para fazer a ativação dos índices clusterizados

     

    3.1.1 Acesse o CBDS;

     

    3.1.2. Acione o menu Arquivo / Conectar;

     

    3.1.3. Selecione o arquivo de Configuração da base de dados onde você realizará;

     

    3.1.4. Na tela de Login, selecione a 'Forma de Logon' como 'Logon no banco de dados' e digite a senha do usuário do banco de dados:

     

     

    3.1.5. Após realizar o Login, acesse o menu Ferramentas / Recursos de Banco / Tabelas com chave primaria clustered:

     

    3.1.6. Verifique para que todas as tabelas estejam listadas do lado esquerdo da tela (na parte de 'Clustered'), conforme exemplo abaixo:

     

    3.1.7. Ao mover alguma tabela para a seção 'Clustered', clique em OK. Ao terminar o procedimento será exibida a mensagem É necessário recriar índices e chaves primárias para aplicar as configurações, conforme imagem abaixo:

     

    3.1.8. Para recriar índices e chaves primárias, execute os passos abaixo:

     

    3.1.8.1. Acesse a opção Ferramentas / Alterar Tabela... no CBDS;

     

     

    3.1.8.1.1. Selecione todas para execução do processo, movendo as tabelas para a seção 'Tabelas selecionadas'

     

    3.1.8.1.2. Você deve executar o comando 'Apagar' com o destino 'Executar no banco' para as três opções indicadas abaixo. O botão Processar da tela confirma a execução do processo.

     

    Sequência da execução dos tipos de Objetos:

    • Primeiro: Índices;
    • Segundo: Relacionamentos;
    • Terceiro: Chaves Primárias.

     

    Atenção

    • Você deverá executar três vezes o processo 'Apagar' com o destino 'Executar no banco', uma vez para cada tipo de 'Objeto';
    • A execução deve ser obrigatoriamente na sequência indicada acima. Do contrário, haverá erros na execução.

     

    Exemplo das execuções:

     

    Índices

     

    Relacionamentos

    Chave primária

     

    Atenção

    Para a execução do comando 'Apagar' em tabelas da base de dados que não possua chave primária, a operação poderá gerar um log de erro com a mensagem:

    Não foi possível desabilitar a primary key XXXX da tabela XXX. 
    Banco de Dados: SQL Server XXXX.
    Erro do Banco (3728): [Microsoft][SQL Server Native Client 11.0][SQL Server]'XXXX' is not a constraint.
    Erro do Banco (3727): [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not drop constraint. See previous errors.

    Isso ocorre porque tabelas que não tenham chave primária não terão a chave para ser executado o comando de 'Apagar'. O processo pode seguir normalmente neste caso.

     

     

    3.1.8.2. Agora que os objetos foram excluídos, você deve executar o processo de criação para cada um dos objetos excluídos. Esse processo continua sendo executado através do menu Ferramentas / Alterar Tabela... no CBDS;

     

    3.1.8.2.1. Selecione todas para execução do processo, movendo as tabelas para a seção 'Tabelas selecionadas':

     

    3.1.8.2.2. Você deve executar o comando 'Criar' com o destino 'Executar no banco' para as três opções indicadas abaixo. O botão Processar da tela confirma a execução do processo.

     

    Sequência da execução dos tipos de Objetos:

    • Primeiro: Chaves Primárias;
    • Segundo: Relacionamentos;
    • Terceiro: Índices.

    Atenção

    • Você deverá executar três vezes o processo 'Criar' com o destino 'Executar no banco', uma vez para cada tipo de 'Objeto';
    • A execução deve ser obrigatoriamente na sequência indicada acima. Do contrário, haverá erros na execução. Note que a execução de 'Criar' é na ordem contrária da execução de 'Apagar';

     

    Exemplo das execuções:

     

    Chaves primárias

     

    Relacionamentos

     

    Índices

     

    3.1.8.3. Após a realização do processo de apagar e criar os objetivos, realize a consistência de base através do menu Ferramentas / Consistir Base...

     

     

    3.2. Para fazer a validação de que os índices clusterizados foram aplicados

    Importante

    A validação da existência de índices não clusterizados na base de dados pode ser executada tanto antes da execução dos procedimentos descritos no tópico 3.1 como depois dessa execução. É uma forma de validar se o processo de clusterização de índices foi efetivo também.

     

     

    3.2.1. Validação via CBDS;

     

    3.2.1.1. Acesse o CBDS;

     

    3.2.1.2. Acione o menu Arquivo / Conectar;

     

    3.2.1.3. Selecione o arquivo de Configuração da base de dados onde você realizará;

     

    3.2.1.4. Na tela de Login, selecione a 'Forma de Logon' como 'Logon no banco de dados' e digite a senha do usuário do banco de dados:

     

     

    3.2.1.5. Após realizar o Login, acesse o menu Ferramentas / Recursos de Banco / Tabelas com chave primaria clustered:

     

    3.2.1.1. Para efetuar a validação após a execução do processo, todas as tabelas o banco de dados devem ser listadas do lado esquerdo da tela (na parte de 'Clustered'), conforme exemplo abaixo:

    Importante

    Mesmo tabelas que no tópico 3.2.2. apareçam no retorno do Select como 'HEAP' porque não possuam chave primária, no CBDS (imagem de exemplo acima), é obrigatório que a tabela apreça como 'Clustered'.

     

     

    3.2.2. Validação através de selects na base de dados

     

    Para efetuar essa validação, execute os comandos abaixo diretamente em um aplicativo de acesso ao banco de dados (note que alguns comandos podem não ser executados pelo CBDS, neste caso, orienta-se utilização do Microsoft SQL Management Studio):

     

    3.2.2.1. Select para identificar a quantidade de tabelas que não existe índice clusterizado (é o tipo de índice chamado de HEAP):

     

    SELECT COUNT(1)

      FROM SYS.TABLES T

           INNER JOIN SYS.SCHEMAS S

                   ON T.SCHEMA_ID=S.SCHEMA_ID

                 INNER JOIN SYS.INDEXES I

                     ON T.OBJECT_ID=I.OBJECT_ID

                        AND I.TYPE=0;

     

    Exemplo de resultado indicando a quantidade de tabelas que estão sem índices clusterizados:

    Atenção

    Tabelas do banco de dados que não tenha Chave Primária podem ser listados como resultado deste select.

     

     

    3.2.1.1 Select para identificar quais são as tabelas que não existe índice clusterizado (é o tipo de índice chamado de HEAP):

     

    SELECT T.NAME, I.TYPE_DESC

      FROM SYS.TABLES T

           INNER JOIN SYS.SCHEMAS S

                   ON T.SCHEMA_ID=S.SCHEMA_ID

                 INNER JOIN SYS.INDEXES I

                     ON T.OBJECT_ID=I.OBJECT_ID

                        AND I.TYPE=0 -- TYPE=0 SIGNIFICA TABELA HEAP

    ORDER BY 1 ASC;   

     

    Exemplo de resultado indicando a quais são as tabelas que estão sem índices clusterizados:

    Atenção

    Tabelas do banco de dados que não tenha Chave Primária podem ser listados como resultado deste select. Também podem ser listadas tabelas que tenha sido indevidamente criadas na base de dados e não foram adicionadas no TBS.

    O mais importante é garantir de que todas as tabelas padrões e de usuário da base de dados do sistema que contenham chave primária não sejam listadas no retorno deste select.

     

     

     

     

    4. Fragmentação de Indices

    A fragmentação de índices muito grande pode trazer perda de performance para as rotinas do banco de dados e consequentemente para o sistema.

     

    Abaixo segue um exemplo de comando para verificar a fragmentação dos índices de tabelas que possuam mais de 1000 linhas.

     

    Você deve alterar o trecho NOME_BASE pelo nome da base de dados do sistema onde você deseja fazer a análise de fragmentação.

     

    Importante

    Tome cuidado para escrever o nome da base em letras maiúsculas.

     

    SELECT dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Table',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
    INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
    INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
    INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
    WHERE indexstats.database_id = DB_ID('NOME_BASE')
    and dbindexes.[name] is not null and page_count > 1000
    ORDER BY indexstats.avg_fragmentation_in_percent desc, indexstats.page_count desc

     

    Importante

    Um valor acima de 4% de fragmentação é um valor alto e a desfragmentação deve ser realizada pelo DBA da sua empresa.

     

     

    5. Coleta de Estatísticas

    A coleta de estatísticas é um processo regular que deve ser executado no banco de dados.

    Esse processo deve ser configurado pelo DBA da sua empresa, seguindo as recomendações da Senior.

     

    Para verificar maiores informações sobre as recomendações sobre coleta de Estatísticas, procure pelo termo 'Coleta de estatística em Microsoft SQL Server' no Portal da Documentação da Senior.

     

    Importante

    Em casos de tratativas de situações relacionadas a Performance/lentidão/Travamentos, você deve sempre solicitar evidência ao seu DBA de que a rotina  de coleta de estatísticas está sendo efetuada semanalmente e enviar essas evidências sempre que solicitado pelo Suporte da Senior.

     

     

     

    Observação

    Para mais informações sobre o questões relacionadas a Performance/Lentidão/Travamentos do Gestão Empresarial | ERP, consulte o artigo ERP - Performance/Lentidão/Travamentos - Onde é possível encontrar informações diversas sobre questões relacionadas a Performance/Lentidão/Travamentos (índice).

     

     

    Atenção! Antes de realizar qualquer alteração, analise o impacto que poderá ter em outros lançamentos/processos do sistema.