Oi! Seja bem-vindo!

Assumindo que temos uma boa indexação de banco de dados, a manutenção de índices fragmentados no SQL Server se torna a cereja do “bolo performance”. 🙂

Por essa razão, hoje vamos aprender as duas principais estratégias, recomendadas pela Microsoft, para fazermos manutenção dos índices de nosso banco de dados SQL Server – com direito a script que gera script automaticamente!

Como fazer a Manutenção de Índices Fragmentados no SQL Server

Embora eu tenha iniciado o artigo falando de uma boa indexação, criei um banco de dados mal definido em termos de índices. Fiz isso para que com poucos INSERTs, possamos aferir desordem no índice criado:

-- Banco de testes
use master
if db_id('curso') is not null 
	drop database curso
go
create database curso
go
use curso
go

-- Tabela de testes com dados aleatórios
if object_id('teste') is not null drop table teste
create table teste (numero int constraint pk_teste primary key nonclustered)
;with cte_seq as (
	select 1 as nr_cont, checksum(newid()) as aleatorio
	union all
	select nr_cont + 1, checksum(newid())
	from cte_seq where nr_cont < 10000)
insert into teste
	select 
		aleatorio
	from cte_seq
	option (maxrecursion 0)
go

 

O script abaixo consulta a fragmentação das tabelas, e como você pode ver, apesar de poucos registros teremos uma grande fragmentação do índice primário:

-- Consultar a fragmentação média:
select 
	nome_tabela = object_name(b.object_id),
	nome_indice = name, 
	fragmentacao_media = avg_fragmentation_in_percent,
	script = case 
		when avg_fragmentation_in_percent > 30 then 'alter index ' + name + ' on ' + object_name(b.object_id) + ' rebuild with (online = on)'
		when avg_fragmentation_in_percent >= 5 and avg_fragmentation_in_percent <= 30 then 'alter index ' + name + ' on ' + object_name(b.object_id) + ' reorganize'
	end
from sys.dm_db_index_physical_stats (db_id('curso'), null, null, null, null) as a -- (Parâmetros da função: banco de dados, tabela, indice, partição física, modo de analise: default, null, limited (limitado), sampled (amostra), detailed (detalhado))
join sys.indexes as b on a.object_id = b.object_id and a.index_id = b.index_id

nome_tabela	nome_indice	fragmentacao_media
teste	pk_teste	96.8750

 

De acordo com a recomendação da Microsoft, as fragmentações devem ser tratadas da seguinte forma:

percentual de fragmentação	recomendação
Entre 5% e 30%	Reorganizar o índice
Maior que 30%	Reconstruir o índice

 

No primeiro teste que fizemos no vídeo, a recomendação foi REBUILD. No segundo REORGANIZE. Para facilitar nossa vida, meu script de análise gera automaticamente o script de manutenção recomendado pela Microsoft.

 

Então aqui vai o script completo da aula de hoje:

------------------------------------------------------------------
-- Defragmentação de indices
------------------------------------------------------------------
--------------------------------------------------
-- Banco de dados para nosso teste
--------------------------------------------------
-- Banco de testes
use master
if db_id('curso') is not null 
	drop database curso
go
create database curso
go
use curso
go

-- Tabela de testes com dados aleatórios
if object_id('teste') is not null drop table teste
create table teste (numero int constraint pk_teste primary key nonclustered)
;with cte_seq as (
	select 1 as nr_cont, checksum(newid()) as aleatorio
	union all
	select nr_cont + 1, checksum(newid())
	from cte_seq where nr_cont < 10000) insert into teste select aleatorio from cte_seq option (maxrecursion 0) go -------------------------------------------------- -- Manutenção de indices -------------------------------------------------- -- Consultar a fragmentação média: select nome_tabela = object_name(b.object_id), nome_indice = name, fragmentacao_media = avg_fragmentation_in_percent, script = case when avg_fragmentation_in_percent > 30 then 'alter index ' + name + ' on ' + object_name(b.object_id) + ' rebuild with (online = on)'
		when avg_fragmentation_in_percent >= 5 and avg_fragmentation_in_percent <= 30 then 'alter index ' + name + ' on ' + object_name(b.object_id) + ' reorganize'
	end
from sys.dm_db_index_physical_stats (db_id('curso'), null, null, null, null) as a -- (Parâmetros da função: banco de dados, tabela, indice, partição física, modo de analise: default, null, limited (limitado), sampled (amostra), detailed (detalhado))
join sys.indexes as b on a.object_id = b.object_id and a.index_id = b.index_id
go
-- Recomendação MS:
-- Entre 5 e 30 = reorganize
-- Maior que 30 = rebuild


-- Desorganizando os dados
insert into teste values (checksum(newid()))
insert into teste values (checksum(newid()))
go


--------------------------------------------------
-- Excluindo dados de teste
--------------------------------------------------
use master
drop database curso

CONCLUSÃO

Naturalmente um banco de dados bem definido não fragmentará freneticamente como o exemplo desse vídeo, que montei dessa forma apenas para podermos simular o funcionamento dos comandos e scripts de manutenção.

Entretanto, não se assuste se você encontrar, no mercado, bancos com definições tão cabeludas quanto essas…. Eu já encontrei, e você com certeza também encontrará…

O ideal, nesses casos extremos, é redefinir as tabelas de banco, certo? Uma pena que quase sempre isso envolve muita gente e muito dinheiro… kkkkk então o jeito é ir mantendo da forma como dá, e as ferramentas que apresentei a você também apoiam nisso.

Abraço do seu amigo Josué 🙂