«

»

out 25

Seu job de rebuild demora muito?

Muitos ambientes possuem um job agendado para fazer o rebuild dos índices mas com o constante crescimento da base acabam chegando próximos da janela ou, quando não monitorados, até passam do horário permitido. Existe uma alternativa que se aplica a grande maioria dos ambientes. Para entendermos melhor, vamos relembrar os tipos de fragmentação e a diferença entre REBUILD e REORGANIZE:

Externa ou lógica

As páginas estão fora da ordem sequencial. É necessário fazer uma série de movimentos com o braço do disco e uma quantidade muito maior de operações de I/O para se obter os registros solicitados. Impossibilita tirar vantagem do read-ahead.

Interna ou física 

As páginas estão com muito espaço em branco ou poucos registros, necessitando ler muitas páginas para se obter os registros solicitados.

 

REBUILD

No REBUILD o índice é copiado para uma área contígua do arquivo de dados colocando suas páginas e registros de forma sequencial, preenchendo-as de acordo com a configuração do FILL FACTOR ou fator de preenchimento, excluindo o índice anterior. Tome muito cuidado com esta opção pois o que ela faz é simplesmente deixar espaços em branco dentro das páginas, fazendo com que seus registros precisem de muito mais páginas para serem armazenados. Quanto mais páginas, maior o seu arquivo de dados ($$). Além disso, o SQL vai precisar ler mais páginas para trazer a mesma quantidade de registro, precisando também de mais espaço em memória já que o cache do SQL Server é feito por páginas e não por registros. Também temos que nos perguntar, pra que vamos deixar espaço em branco nas páginas de objetos que são somente leitura ou que possuem alteração mínima? Se aplica melhor em casos onde há a fragmentação externa ou lógica.

 

REORGANIZE

No REORGANIZE há apenas a tentativa de preencher melhor as páginas, fazendo com que as páginas armazenem o máximo de registro possível (como se o FILL FACTOR estivesse em 100%). Como a alocação na memória é feita por página, são mais registros disponíveis na mesma quantidade de memória ocupada pelo cache. Além disso, para ler determinada quantidade de registros a quantidade de páginas necessárias é bem menor e o salto entre as páginas diminui. Diminui I/O, diminui o tempo necessário pra concluir o procedimento, diminui o consumo de hardware durante a execução, etc.

 

 

O indiano Satnam Singh desenvolveu um script  e compartilhou através deste artigo capaz de verificar quais objetos possuem fragmentação externa maior que uma quantidade personalizada como 30%, por exemplo. Já elimina os objetos somente leitura (se não tem alteração não tem fragmentação). Um baixo índice de fragmentação nos permite utilizar o REORGANIZE, reduzindo drasticamente o tempo necessário:

 

———————————————————————————

USE AdventureWorks2008R2

GO

SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);

— Coleta as informações de fragmentação e traz também os nomes
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’)
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

–Armazena em uma tabela temporária
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
OPEN partitions;
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

— Ajuste aqui o nível de fragmentação para REORGANIZE ou REBUILD
IF @frag < 30
SET @command = N’ALTER INDEX ‘ + @indexname + N’ ON ‘ + @schemaname + N’.’ + @objectname + N’ REORGANIZE’;
Print ‘OK’
IF @frag >= 30
SET @command = N’ALTER INDEX ‘ + @indexname + N’ ON ‘ + @schemaname + N’.’ + @objectname + N’ REBUILD’;
IF @partitioncount > 1
SET @command = @command + N’ PARTITION=’ + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
–PRINT N’Executed: ‘ + @command;
END;
CLOSE partitions;
DEALLOCATE partitions;
select * from #work_to_do

— Apaga a tabela temporária
DROP TABLE #work_to_do;
GO

———————————————————————————

 

Coloquei no código um select que traz a fragmentação encontrada, você pode comentá-lo se quiser. Neste outro artigo que publiquei hoje, 21/04/2013, tem uma explicação bacana e um ótimo script sobre fragmentação.

 

Existe também a fragmentação de extents, que é quando existem extents em branco, como se resolve em ambos os casos não comentei para não atrapalhar o raciocínio.

 

Tome cuidado pois esta operação exige uso intenso do Transaction Log e pode acabar com o seu espaço em disco. Procure mudar seu recovery model para simple e não se esqueça de fazer um backup full antes e depois do procedimento para retomar os backups do TLog..

1 comentário

  1. Rubens Araujo

    Ja usei esse script, na verdade fiz uma adaptação para usar onde trabalhava, muito bom ele!!

Deixe uma resposta