Script Configurações das Databases – Best Practices

Olá Pessoal,

No post de hoje vou compartilhar um script que utilizamos sempre que iniciamos um atendimento em um novo cliente nas nossas consultorias de banco de dados.

No trabalho como Consultor SQL Server temos o privilégio de conhecer muitos cenários distintos de bancos de dados, pois cada cliente possui suas próprias particularidades. Com isso, temos um novo aprendizado a cada dia.

Além disso, no primeiro contato não temos ideia de como está o banco de dados do cliente, se já alteraram alguma configuração anteriormente, etc. Visto isso, nós utilizamos um script que retorna diversas informações das databases para que possamos validar se está tudo OK.

Esse script retorna algumas das informações abaixo:

  • Nome da database, data de criação, status, tamanho dos arquivos de dados e log;
  • Data do último backup da database, compatibility level;
  • PAGE VERIFY, AUTO CLOSE, AUTO SHRINK e AUTO CREATE STATISTICS / AUTO UPDATE STATISTICS.

Lembrando que existem diversas outras configurações de Best Practices. Contudo, nesse post vou falar apenas sobre as opções do último item.


Script Configurações das Databases – Best Practices

Segue abaixo o script que retorna diversas informações sobre as configurações das databases:

Segue um trecho do resultado da query:

Nessa query ordenamos o resultado pelo tamanho dos Arquivos de Dados, ou seja, as maiores bases serão listadas primeiro (normalmente são as mais críticas). Além das configurações que iremos citar mais abaixo, a query também retorna o RECOVERY MODEL (irá ajudar quando for definir a Estratégia de Backup), o COMPATIBILITY LEVEL, a data de criação da database, o tipo de acesso (SINGLE / MULTI USER) e se utiliza ou não a opção FULLTEXT.

Repare também que é informado a data e hora do último backup realizado na database. Com isso, conseguimos identificar facilmente quais databases estão sem backup (nesse exemplo apenas uma tinha feito backup).

Agora vou explicar de uma forma resumida algumas dessas opções:


PAGE VERIFY

CHECKSUM: É uma verificação que o SQL Server faz para identificar corrupção nas páginas de dados. Funciona da seguinte forma:

  • Escrita – Calcula uma soma do conteúdo da página inteira e salva no cabeçalho;
  • Leitura – Recalcula a soma do conteúdo da página inteira e compara com o valor do cabeçalho.

Isso ajuda a fornecer um alto nível de integridade nos arquivos de dados. Essa opção é RECOMENDADA pela própria Microsoft.

Link Referência: https://msdn.microsoft.com/pt-br/library/bb402873.aspx


AUTO CLOSE e AUTO SHRINK

AUTO CLOSE: Opção que coloca a database como OFFLINE caso não esteja sendo utilizada. Essa opção NÃO É RECOMENDADA na maioria das vezes (99,99% dos casos), pois toda vez que você tentar acessar a database, a sua query poderá ficar mais lenta devido ao tempo de deixar a database ONLINE. Portanto, essa opção pode diminuir o desempenho do banco de dados.

AUTO SHRINK: Recomendada apenas para cenários onde o espaço em disco é extremamente limitado. Quando habilitada, o SQL Server realiza um SHRINK automaticamente dos arquivos quando mais de 25% do arquivo não estiver sendo utilizado. Essa opção pode diminuir o desempenho do banco de dados e NÃO É RECOMENDADA na maioria das vezes (99,99% dos casos). Já pensou um SHRINK ser executado repentinamente na sua maior database e durante o horário de maior movimento? Seria um caos!!! Além disso, essa operação também consome mais CPU.

Link Referência 1: http://sqlmag.com/sql-server/avoiding-autoclose-and-autoshrink-options

Link Referência 2: http://www.sqlservercentral.com/articles/Administration/autocloseandautoshrinkjustdont/984/

Segue abaixo um post do Dirceu Resende com um caso que não é muito raro de ser encontrado nas consultorias que realizamos. Trata-se de um caso prático onde a opção AUTO CLOSE está habilitada e gera diversos registros no Error Log com a mensagem abaixo:

“Starting up database XXX”

“Starting up database XXX”

“Starting up database XXX”

Link Referência: https://www.dirceuresende.com/blog/sql-server-sua-instancia-esta-constantemente-com-mensagens-starting-up-database-e-databases-in-recovery-conheca-o-auto-close/


 AUTO CREATE STATISTICS / AUTO UPDATE STATISTICS

AUTO_CREATE_STATISTICS: Quando essa opção está ativada, o otimizador de consultas cria algumas estatísticas a fim de melhorar as estimativas do plano de consulta. Quando o otimizador de consultas cria estatísticas como resultado do uso dessa opção, os nomes das estatísticas começam com “_WA”. Isso pode melhorar o desempenho das queries. Essa opção é RECOMENDADA na maioria das vezes (99,99% dos casos).

AUTO_UPDATE_STATISTICS: Quando essa opção está ativada, o otimizador de consultas determina quando as estatísticas podem estar desatualizadas e as atualiza quando são usadas por uma consulta. As estatísticas ficam desatualizadas depois que operações de inserção, atualização, exclusão ou mesclagem alteram a distribuição de dados na tabela ou na exibição indexada. Isso pode melhorar o desempenho das queries. Essa opção é RECOMENDADA na maioria das vezes (99,99% dos casos).

Link Referência 1: https://docs.microsoft.com/pt-br/sql/relational-databases/statistics/statistics

Link Referência 2: https://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/ 


Script Correções

Agora vamos ver um exemplo prático do script que gera os comandos de alteração das databases para seguir as Best Practices.

Segue abaixo o resultado da query:

Aqui você também pode descomentar algum trecho da cláusula WHERE para poder copiar o script de alteração de uma opção específica. Imagine o cenário onde o cliente possui centenas de databases, com isso você pode executar a query acima para cada uma das opções, copiar o resultado e fazer a correção.

Por fim, segue o script com as alterações que foram geradas nesse exemplo. Basta copiar e executar em uma nova query.

Pronto! Agora suas databases estão seguindo algumas das Best Practices! WOW!!!

Quando for analisar um novo cliente e quiser descobrir algumas configurações das databases de forma rápida e prática, basta usar esses scripts. Não perca tempo e valide também como estão essas configurações nos bancos de dados que você administra.


Download “Script Configurações das Databases – Best Practices”:

Segue o link para baixar os scripts utilizados nesse Post:

Link: https://github.com/luizvitorf/SQLServer/blob/master/Scripts/Administra%C3%A7%C3%A3o/Script%20Configura%C3%A7%C3%B5es%20das%20Databases%20-%20Best%20Practices.sql


Deixe nos comentários algum caso interessante ou alguma configuração que você já tenha utilizado como Best Practices.

Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

1 comentário em “Script Configurações das Databases – Best Practices

  1. Pingback: Instant File Initialization x Tempo Restore – Luiz Lima

Deixe uma resposta