Rotina para identificação e desfragmentação automática das estruturas de um database.

Boa tarde!!!

Este post visa mostrar a vocês uma rotina para identificação das estruturas (tabelas e seus índices) que estão fragmentados de acordo com métricas previamente definidas e, automaticamente, reorganiza ou reconstrói os índices das tabelas.

Esta rotina recupera todos os índices que estão com o LogicalFragmentation acima de 20% ou o menor ScanDensity abaixo de 80% e realiza a reorganização destes.

Para os casos mais absurdos, que ultrapassem o limiar de 50%, o REBUILD do índice será realizado, com isso, a estrutura será refeita de forma organizada.

Uma observação importante é que essa operação poderá deixar a base lenta e gerar bloqueios para reorganização (REORGANIZE) / reconstrução (REBUILD). Vale ressaltar que o script poderá ser utilizado para as versões 2005 e 2008 do MS SQL Server.

Segue o script:

--Início do Script

use <nome-do-banco>

declare @maxLogicalFrag tinyint,

           @minScanDensity tinyint

--Métricas previamente definidas para os limiares de fragmentação e ScanDensity

set @maxLogicalFrag = 20

set @minScanDensity = 80

--Estrutura auxiliar que será criada no TEMPDB para guardar informações estatísticas das tabelas

if object_id('tempdb..#showcontig') is not null

       drop table #showcontig



create table #showcontig (

       ObjectName sysname,

       ObjectId int,

       IndexName sysname,

       IndexId int,

       Level int,

       Pages int,

       Rows int,

       MinimumRecordSize int,

       MaximumRecordSize int,

       AverageRecordSize int,

       ForwardedRecords int,

       Extents int,

       ExtentSwitches int,

       AverageFreeBytes int,

       AveragePageDensity int,

       ScanDensity int,

       BestCount int,

       ActualCount int,

       LogicalFragmentation int,

       ExtentFragmentation int

)

declare @nome as sysname

         , @str as varchar(512)

--Seleciona todas as tabelas do database que não são do sistema.

declare cTables cursor for

select name

from sys.tables

where name not like 'sys%'

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

       exec (@str)

       FETCH NEXT FROM cTables INTO @nome

end

close cTables

deallocate cTables

declare @ObjectName sysname, @IndexId int, @IndexName sysname, @cmd varchar(512)

declare cIndices cursor for

--Seleciona os índices para realizar o reorganize

select ObjectName

       , IndexId

       , IndexName

       , 'alter index ' + IndexName + ' ON ' + ObjectName + ' REORGANIZE;' as command

from #showcontig

where (ScanDensity <= @minScanDensity

          or LogicalFragmentation >= @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

from #showcontig

where (ScanDensity <= @minScanDensity

      or LogicalFragmentation >= @maxLogicalFrag)

    and (ScanDensity <= 50 and

            LogicalFragmentation >= 50)

order by ObjectName, IndexId

--Loop para execução dos comandos gerados para a reorganização dos índices selecionados.

Open cIndices

FETCH NEXT FROM cIndices INTO @ObjectName, @IndexId, @IndexName, @cmd

while @@fetch_status = 0 begin

       exec (@cmd)

       FETCH NEXT FROM cIndices INTO @ObjectName, @IndexId, @IndexName, @cmd

end

close cIndices

deallocate cIndices

--Fim do Script