Banco de Dados

2 mai, 2019

Script Configurações das Databases – Melhores práticas

Publicidade

Olá, pessoal!

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

Na função de 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, utilizamos um script que retorna diversas informações das databases para validarmos 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, neste artigo vou falar apenas sobre as opções do último item.

Script Configurações das Databases – Melhores práticas

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

----------------------------------------------------------
-- 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)'
	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 citaremos mais abaixo, a query também retorna o RECOVERY MODEL (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 (neste exemplo apenas uma tinha feito backup).

Agora vou explicar resumidamente algumas dessas opções:

PAGE VERIFY

CHECKSUM: 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:

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, sua query poderá ficar mais lenta devido ao tempo para 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:

Link referência 2:

Segue um artigo 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:

AUTO CREATE STATISTICS/AUTO UPDATE STATISTICS

AUTO_CREATE_STATISTICS: quando essa opção é 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 é 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:

Link referência 2:

Script Correções

Agora veremos um exemplo prático do script que gera os comandos de alteração das databases para seguir as melhores práticas.

----------------------------------------------------------
-- 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

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 neste exemplo. Basta copiar e executar em uma nova query.

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 melhores práticas!

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.

Segue o link para baixar os scripts utilizados neste artigo:

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

Espero que tenha gostado e que isso também possa ser útil no seu dia a dia.

Siga a minha página do Facebook e fique ligado no iMasters para mais novidades.

Até o próximo artigo!