Script para manutenção do SQL Server

Script para manutenção do SQL Server

Olá pessoal…

Trago hoje um script de manutenção para o SQL Server, com intuito de otimizar o Banco de Dados.

SQL Server
SQL Server

Primeiramente gostaria de agradecer ao Ednilson Amarante, vulgo A Lenda, por disponibilizar esse script para ajudar.

Agora como ele funciona, basicamente, pense da seguinte forma, você para ir até a padaria, passa por 12 quadras e sempre você pega o mesmo caminho, porém com o passar do tempo, a avenida entre você e a padaria, começou a ficar extremamente congestionada, além de várias ruas mudarem para se tornar de mão única.

Assim também é um banco de dados, quando ele faz a consulta em uma tabela, supondo que a tabela tenha 10 registros, ele vai criar um “mapa” para buscar esses dados, mas conforme a tabela vai aumentando, esse mapa precisa ser revisto, e muitas das vezes não atualizamos o mapa, limpamos o caminho para o banco.

Essa consulta serve para otimizar e limpar esses gargalos das consultas. Abaixo o código.

/*
 Script criado por Ednilson Amarante,
 Adaptado por Daniel Atilio.

 Deve-se alterar onde está [Base_de_Dados] pelo nome da sua base

 Obs.: O ideal é executar a consulta com a saída como texto
 */

--Declarando variáveis
Declare @Tabelas table (Tabela varchar(max))
Declare @cNomTab as varchar(max)
Declare @cString as nvarchar(max)
Declare @dInicio as datetime
Declare @nTabelas as integer

--Deixando a base em uso
Use Base_de_Dados

--Mostrando log de início
Print('Manutenção do Banco de Dados Base_de_Dados iniciada em: ' + Convert(varchar(10),GetDate(),103)) + ' ' + Convert(varchar(10),GetDate(),108)
Print('===============================================================================================================================================================================================')

--Definindo a data de inicio
Set @dInicio = GetDate()

--Pegando as tabelas da base
Select @nTabelas = Count(name) From Base_de_Dados.dbo.sysobjects
Where xtype = 'U' and category = 0 

--Inserindo na tabela temporária
Insert Into @Tabelas (Tabela)
Select name From Base_de_Dados.dbo.sysobjects
Where xtype = 'U' and category = 0
Order By name

--Declarando o cursor
Declare csTab Cursor For Select Tabela From @Tabelas Order By Tabela
Open csTab
Fetch Next From csTab Into @cNomTab

--Enquanto houver tabelas
While @@Fetch_Status = 0 Begin

	--Desfragmentando índices
	Set @cString = 'DBCC INDEXDEFRAG(Base_de_Dados,' + @cNomTab + ')'
	Print('===============================================================================================================================================================================================')
	Print('Desfragmentando índices da tabela Base_de_Dados.dbo.' + @cNomTab + '...')
	Exec sp_executesql @cString
	Print('===============================================================================================================================================================================================')
	Print('')

	--Atualizando estatísticas
	Set @cString = 'UPDATE STATISTICS Base_de_Dados.dbo.' + @cNomTab
	Print('===============================================================================================================================================================================================')
	Print('Atualizando estatísticas da tabela Base_de_Dados.dbo.' + @cNomTab + '...')
	Exec sp_executesql @cString
	Print('===============================================================================================================================================================================================')
	Print('')

	--Marcando tabela para recompilação
	Print('===============================================================================================================================================================================================')
	Set @cString = 'Exec sp_recompile ''Base_de_Dados.dbo.' + @cNomTab + ''''
	Print('Marcando a tabela Base_de_Dados.dbo.' + @cNomTab + ' para recompilação...')
	Exec sp_executesql @cString
	Print('===============================================================================================================================================================================================')
	Print('')

Fetch Next From csTab Into @cNomTab
End

Close csTab
Deallocate csTab

--Mostrando mensagem de quantas tabelas foram processadas
Print('===============================================================================================================================================================================================')
Print('A manutenção foi realizada em ' + Ltrim(Rtrim(Str(@nTabelas))) + ' tabela(s) do Banco de Dados Base_de_Dados')
Print('===============================================================================================================================================================================================')
Print('')

--Finalizando o script
Print('===============================================================================================================================================================================================')
Print('Manutenção do Banco de Dados Base_de_Dados terminada em: ' + Convert(varchar(10),GetDate(),103)) + ' ' + Convert(varchar(10),GetDate(),108)
Print('===============================================================================================================================================================================================')
Print('')

--Mostrando a diferença de tempo
Print('===============================================================================================================================================================================================')
Print('Tempo utilizado para a manutenção do Banco de Dados Base_de_Dados: ' + Convert(varchar(12),GetDate() - @dInicio,114))
Print('===============================================================================================================================================================================================')

Obs.: Substitua Base_de_Dados pelo nome da sua base.

Bom pessoal, por hoje é só.
Abraços e até a próxima.

About Dan_Atilio

Analista e desenvolvedor de sistemas. Técnico em Informática pelo CTI da Unesp. Graduado em Banco de Dados pela Fatec Bauru. Entusiasta de soluções Open Source e blogueiro nas horas vagas. Autor do projeto Terminal de Informação, onde são postados tutoriais e notícias envolvendo o mundo da tecnologia.

2 comentários em “Script para manutenção do SQL Server

Deixe uma resposta

%d blogueiros gostam disto: