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