8 de Agosto de 2018 / Last updated : 8 de Agosto de 2018 victorgarcia_wp BANCO DE DADOS

Faz a manutenção dos índices de todas as tabelas do banco

Rotina para fazer a manutenção diária dos índices da tabela. Cumpre um prazo de 120min… para sair da rotina.

-- =============================================
-- Author:		Victor Duarte
-- Create date: 06/08/2018
-- Description:	Faz a manutenção dos índices de todas as tabelas do banco
-- =============================================
CREATE PROCEDURE [dbo].[usp_Manutencao_DB]

AS

BEGIN

	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	-- Sai da rotina quando a janela de manutenção é finalizada
	--IF GETDATE()> DATEADD(MI,+00,DATEADD(HH,+06,CAST(FLOOR(CAST(GETDATE()AS FLOAT))AS DATETIME)))-- hora > 06:00

	--BEGIN
	--RETURN
	--END

	-- Seta horario para sair do laço
	DECLARE @HoraFim_Manut datetime
	set @HoraFim_Manut = (select dateadd(minute,120,GETDATE()))

	-- Declara variaveis que serao utilizadas pelo cursor
	DECLARE @nome AS SYSNAME,
	@str AS VARCHAR(512),
	@qtde int


	set @qtde = (select COUNT(1) from Manutencao_DB_Atualiza_Indices)


	--Caso não exista nada pendente para rebuild/reorganize, vai efetuar uma nova varredura em todas as tbls
	if(@qtde = 0)
	begin

		select 'entrou no if' as msg
		
		--Limpa informacoes antigas
		truncate table  Manutencao_DB_Atualiza_Indices
		truncate table Manutencao_DB_Showcontig

		--Faz uma nova varredura
		--Seleciona todas as tabelas do database que não são do sistema
		DECLARE cTables CURSOR FOR
		SELECT --top 100
		'"' + s.name +	'.' + t.name + '"'
		FROM
		sys.tables t
		INNER JOIN sys.indexes i ON
		t.object_id = i.object_id
		INNER JOIN sys.schemas s ON
		t.schema_id = s.schema_id
		WHERE
		t.name NOT LIKE 'sys%'
		and
		i.Index_Id > 0

	
		--Loop para coleta de informações estatísticas sobre as tabelas e seus respectivos índices
		OPEN cTables
	
		FETCH NEXT FROM cTables INTO @nome

		WHILE @@fetch_status = 0 BEGIN
		SET @str = 'dbcc showcontig(' + @nome + ') with all_indexes, fast, tableresults'
		INSERT INTO Manutencao_DB_Showcontig
		EXEC (@str)
		FETCH NEXT FROM cTables INTO @nome
		END

		CLOSE cTables

		DEALLOCATE cTables
	


		-- Inicia a CTE para concatenar o comando a ser executado, segmentado pelas operacoes de REORGANIZE ou REBUILD
		;WITH Tamanho_Tabelas AS (

			select  distinct
			ObjectName
			, IndexId
			, IndexName
			, command
			, LogicalFragmentation
			from (
				--Seleciona os índices para realizar o reorganize
				SELECT ObjectName
				, IndexId
				, IndexName
				, 'alter index ' + IndexName + ' ON ' + ObjectName + ' REORGANIZE;' AS command
				, LogicalFragmentation
				FROM
				Manutencao_DB_Showcontig
				WHERE
				(LogicalFragmentation > 5 and LogicalFragmentation < 30)
				/*
				(ScanDensity = @maxLogicalFrag)
				AND
				(ScanDensity > 50
				OR
				LogicalFragmentation < 50)
				*/

				UNION

				-- Seleciona os índices para realizar o rebuild
				SELECT
				ObjectName,
				IndexId,
				IndexName,
				'alter index ' + IndexName + ' ON ' + ObjectName + ' REBUILD;' AS command
				, LogicalFragmentation
				FROM
				Manutencao_DB_Showcontig
				WHERE
				(LogicalFragmentation >= 30)
				/*
				(ScanDensity = @maxLogicalFrag)
				AND
				(ScanDensity = 50)
				)
				*/
			) a 
		)

		-- Insere na tabela temporaria o retorno da CTE
		INSERT INTO  Manutencao_DB_Atualiza_Indices (Ds_Comando, LogicalFragmentation)
		SELECT command,LogicalFragmentation FROM tamanho_tabelas
	end


	-- Declara variaveis que serao utilizadas pelo looping de execucao de comandos
	DECLARE 
	@Comando NVARCHAR(4000)

	--Debug
	select GETDATE() as inicia, @HoraFim_Manut as finaliza
	
	-- Inicia o looping de execucao de comandos de manutencao
	IF EXISTS(SELECT COUNT(1) FROM  Manutencao_DB_Atualiza_Indices)
	BEGIN


		DECLARE @DS_Comando varchar(100)
  
		DECLARE cCorrigeIndice CURSOR  
		FOR

		--Inicia pelos indices mais fragmentados
		SELECT DS_Comando
		FROM  Manutencao_DB_Atualiza_Indices
		order by LogicalFragmentation desc
	
		OPEN cCorrigeIndice
		
			FETCH cCorrigeIndice
			INTO @DS_Comando
		
			WHILE @@FETCH_STATUS = 0
			BEGIN
				
				SELECT @DS_Comando	as comando
				exec(@DS_Comando)				
				delete from  Manutencao_DB_Atualiza_Indices where DS_Comando = @DS_Comando
				--Debug
				--WAITFOR DELAY '00:01'

			IF GETDATE()> @HoraFim_Manut
			BEGIN
			select 'vai sair' as msg
			RETURN
			END


			FETCH cCorrigeIndice
			INTO @DS_Comando
					
		END
		CLOSE cCorrigeIndice
		DEALLOCATE cCorrigeIndice

		select @HoraFim_Manut as HoraFim_Manut, GETDATE() as HoraSaiu

	END

END


GO