SQL SERVER – Índices – Fragmentação – Rebuild ou Reorganize

Então a sua solução de índice é o que, literalmente, deu vida a alguma query ou relatório. Bom trabalho!
Entretanto, após determinado período de tempo, o problema de performance parece estar voltando…
Você analisa novamente o plano de execução, afinal, uma má gestão de estatística pode ter feito seu índice ser desconsiderado na construção do plano atualmente.. Mas não, seu índice ainda é utilizado.
Pode ter chegado a hora de controlar o nível de fragmentação de seu índice!

Documentação Microsoft:
“The workload performance increase realized in the small-scale environment ranged from 60 percent at the low level of fragmentation to more than 460 percent at the highest level of fragmentation. The workload performance increased realized for the large-scale environment ranged from 13 percent at the low fragmentation level to 40 percent at the medium fragmentation level”

Até 460% de melhora de performance. E acreditem, não é dificil atingir 97% de fragmentação em um índice!

Detectando fragmentação:
DMV: sys.dm_db_index_physical_stats
avg_fragmentation_in_percent = The percent of logical fragmentation (out-of-order pages in the index).
fragment_count = The number of fragments (physically consecutive leaf pages) in the index.
avg_fragment_size_in_pages = Average number of pages in one fragment in an index.

Recomendação Microsoft:
avg_fragmentation_in_percent > 5% and 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

Cuidados com rebuild:
SQL Standard: Rebuild apenas offline, ou seja, concorrência com os dados indexados não será permitida (downtime…). E para somar, single-threaded…
SQL Enterprise: Pode utilizar ‘… rebuild with(online = on)’, ou seja, concorrência ‘permitida” (sem downtime…). Mais ou menos, um SCH-M lock será grantted quando o novo índice substituir o corrente…

Abaixo, segue um script t-sql que gera os comandos – ora rebuild, ora reorganize – considerando as melhores práticas Microsoft + uma ambiente de licença Standard.

use seu_banco
go

select 'alter index '+idx.name+' on '+object_name(dmv.object_id,dmv.database_id)+case when avg_fragmentation_in_percent > 30 then ' rebuild' else ' reorganize' end
from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) dmv
	left join sys.indexes idx 
		on		idx.object_id=dmv.object_id
			and idx.index_id = dmv.index_id
where index_type_desc <> 'HEAP'
	and avg_fragmentation_in_percent > 5

T+

Referências:
http://www.sqlservercentral.com/blogs/the-dba-who-came-in-from-the-cold/2014/03/13/index-fragmentation/
http://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/
http://technet.microsoft.com/en-us/library/ms189858%28v=sql.90%29.aspx
http://technet.microsoft.com/en-us/library/ms188917%28v=sql.90%29.aspx

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s