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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
---------------------------------------------------------- -- Script Configurações Databases ---------------------------------------------------------- SELECT database_id, CONVERT(VARCHAR(1000), DB.name) AS dbName, state_desc, (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB], (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB], page_verify_option_desc AS [page verify option], recovery_model_desc AS [Recovery model], -- last backup ISNULL((SELECT TOP 1 CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' + LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' + CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' + CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) + ' (' + CAST(DATEDIFF(second, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' ' + 'seconds)' FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup], CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose], CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink], CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics], CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics], CASE compatibility_level WHEN 60 THEN '60 (SQL Server 6.0)' WHEN 65 THEN '65 (SQL Server 6.5)' WHEN 70 THEN '70 (SQL Server 7.0)' WHEN 80 THEN '80 (SQL Server 2000)' WHEN 90 THEN '90 (SQL Server 2005)' WHEN 100 THEN '100 (SQL Server 2008)' WHEN 110 THEN '110 (SQL Server 2012)' WHEN 120 THEN '120 (SQL Server 2014)' WHEN 130 THEN '130 (SQL Server 2016)' WHEN 140 THEN '140 (SQL Server 2017)' WHEN 150 THEN '150 (SQL Server 2019)' END AS [compatibility level], user_access_desc AS [User access], CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date], CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext] FROM sys.databases DB ORDER BY [Data MB] DESC, dbName, [Last backup] DESC, NAME |
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”
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
---------------------------------------------------------- -- Script Correções ---------------------------------------------------------- SELECT name, CASE WHEN page_verify_option_desc <> 'CHECKSUM' THEN 'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM' ELSE '' END AS [page verify option], CASE WHEN is_auto_close_on = 1 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CLOSE OFF' ELSE '' END AS [auto close], CASE WHEN is_auto_shrink_on = 1 THEN 'ALTER DATABASE [' + name + '] SET AUTO_SHRINK OFF' ELSE '' END AS [auto shrink], CASE WHEN is_auto_create_stats_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CREATE_STATISTICS ON' ELSE '' END AS [auto create statistics], CASE WHEN is_auto_update_stats_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS ON' ELSE '' END AS [auto update statistics] FROM sys.databases DB --WHERE -- DESCOMENTAR A OPCAO DESEJADA ABAIXO -- CASE WHEN page_verify_option_desc <> 'CHECKSUM' THEN 'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM' ELSE '' END <> '' -- PAGE VERIFY -- CASE WHEN is_auto_close_on = 1 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CLOSE OFF' ELSE '' END <> '' -- AUTO CLOSE -- CASE WHEN is_auto_shrink_on = 1 THEN 'ALTER DATABASE [' + name + '] SET AUTO_SHRINK OFF' ELSE '' END <> '' -- AUTO SHRINK -- CASE WHEN is_auto_create_stats_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_CREATE_STATISTICS ON' ELSE '' END <> '' -- AUTO CREATE STATISTICS -- CASE WHEN is_auto_update_stats_on = 0 THEN 'ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS ON' ELSE '' END <> '' -- AUTO UPDATE STATISTICS ORDER BY name |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
ALTER DATABASE [TESTE_PAGEVERIFY] SET PAGE_VERIFY CHECKSUM ALTER DATABASE [TESTE_AUTOCLOSE] SET AUTO_CLOSE OFF ALTER DATABASE [TESTE_AUTOSHRINK] SET AUTO_SHRINK OFF ALTER DATABASE [TESTE_STATISTICS] SET AUTO_CREATE_STATISTICS ON ALTER DATABASE [TESTE_STATISTICS] SET AUTO_UPDATE_STATISTICS ON ALTER DATABASE [TESTE_TODOS] SET PAGE_VERIFY CHECKSUM ALTER DATABASE [TESTE_TODOS] SET AUTO_CLOSE OFF ALTER DATABASE [TESTE_TODOS] SET AUTO_SHRINK OFF ALTER DATABASE [TESTE_TODOS] SET AUTO_CREATE_STATISTICS ON ALTER DATABASE [TESTE_TODOS] SET AUTO_UPDATE_STATISTICS ON |
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:
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
Pingback: Instant File Initialization x Tempo Restore – Luiz Lima