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
