Por Que Acontece Fragmentação?

Mais especificamente, o que chamamos de fragmentação, ao final das contas, é o percentual de espaço não utilizado nas páginas de dados “ativas”.

Pela descrição acima, você deve ter captado a mensagem: é natural que haja “algum nível” de fragmentação na página de dados. Ela pode acontecer em alguns cenários, mesmo usando operações simples como INSERT, UPDATE e DELETE. O problema acontece quando esta fragmentação atinge níveis mais altos.

Qual nível é de fato preocupante? Esse é um critério subjetivo. Alguns DBAs consideram que fragmentação acima de 10% (moderada) já é um problema, enquanto outros adotam 30% (alta fragmentação) ou mais. A partir disso é recomendável fazer uma reorganização das páginas de dados desse índice ou tabela.

Como Identificar Objetos Fragmentados

Precisamos adotar alguns critérios para avaliação dessas tabelas e índices que mereçam atenção.

O primeiro deles é que tabelas com menos de 10.000 registros costumam ocupar tão pouco espaço que nem merecem atenção. Esse então será nosso primeiro critério (variável @minRows= 10000).

Outra questão é que vamos considerar dois níveis de fragmentação que merecem tratamentos diferentes (como veremos a seguir). O nível baixo de fragmentação considerado é de 10% (@minFrag=10) e o nível alto começa nos 30% (@maxFrag=30).

Finalmente, vamos considerar o caso de tabelas sem índice principal, as chamadas “heaps”. Neste caso, é de se esperar que as páginas de dados tenham o maior nível possível de utilização, portanto vamos pesquisar os casos de heaps que tenham menos que 75% de ocupação (@maxPgHeap = 75).

Com estes critérios em vista, usamos a função de sistema sys.dm_db_index_physical_stats, que é um verdadeiro coringa em matéria de fragmentação.

Quadro 01: identificação de objetos fragmentados

DECLARE @minRows   VARCHAR(10) = '10000'
DECLARE @minFrag	   VARCHAR(10) = '10'
DECLARE @maxFrag	   VARCHAR(10) = '30'
DECLARE @maxPgHeap VARCHAR(10) = '75'

SELECT DISTINCT q.object_id,
	DB_NAME() AS DB, 
	cast(SUB.ESQUEMA as VARCHAR(15)) AS ESQUEMA, 
	cast(SUB.TABELA as VARCHAR(50)) as TABELA, 
	cast(SUB.INDICE as VARCHAR(50)) as INDICE, 	
	Q.index_id,
	CAST(index_type_desc AS VARCHAR(20)) AS TIPO_INDICE,
	index_level,
	SUB.num_registros ,
	avg_fragmentation_in_percent,
	avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(0, NULL, NULL, NULL , 'SAMPLED') Q
	LEFT JOIN (
		select 
			I.object_id, 
			isnull(I.index_id, 0) as index_id,  
			MIN(S.name) as esquema, 
			MIN(O.name) as tabela, 
			MIN(isnull(I.name, '')) as indice ,  
			MIN(I.type) as tipo, 
			SUM(P.Rows) *10 AS num_registros
		from sys.indexes I 
			inner join sys.all_objects O on I.object_id = O.object_id
			inner join sys.schemas S on O.schema_id = S.schema_id
			inner join sys.partitions AS P on O.object_id = P.object_id
		WHERE
			  O.type = 'U'
			  AND P.index_id < 2 -- 0:Heap, 1:Clustered
		GROUP BY 	
			I.object_id, 
			isnull(I.index_id, 0)
		) SUB ON SUB.object_id = Q.object_id AND SUB.index_id = Q.index_id
WHERE  (
			SUB.num_registros >= @minRows
			AND
			(avg_fragmentation_in_percent > @minFrag
			OR 
			avg_page_space_used_in_percent < @maxPgHeap
			)
		)
		OR 
		(
			SUB.num_registros >=@minRows 
			AND index_type_desc IN ('HEAP')
			AND avg_page_space_used_in_percent < @maxFrag
		)
ORDER BY avg_fragmentation_in_percent DESC

ALTER INDEX FK_Sales_Orders_PickedByPersonID ON Sales.Orders REBUILD ; -- frag = 99.58%
ALTER INDEX IX_Sales_OrderLines_AllocatedStockItems ON Sales.OrderLines REBUILD ; -- frag = 99.28%
ALTER INDEX PK_Sales_CustomerTransactions ON Sales.CustomerTransactions REBUILD ; -- frag = 99.24%
ALTER INDEX FK_Sales_InvoiceLines_StockItemID ON Sales.InvoiceLines REBUILD ; -- frag = 99.19%
ALTER INDEX IX_Sales_OrderLines_Perf_20160301_02 ON Sales.OrderLines REBUILD ; -- frag = 98.79%
ALTER INDEX FK_Sales_CustomerTransactions_CustomerID ON Sales.CustomerTransactions REBUILD ; -- frag = 98.75%
ALTER INDEX FK_Sales_CustomerTransactions_InvoiceID ON Sales.CustomerTransactions REBUILD ; -- frag = 98.75%
ALTER INDEX FK_Sales_CustomerTransactions_PaymentMethodID ON Sales.CustomerTransactions REBUILD ; -- frag = 98.75%
ALTER INDEX FK_Sales_CustomerTransactions_TransactionTypeID ON Sales.CustomerTransactions REBUILD ; -- frag = 98.75%
ALTER INDEX PK_Application_People ON Application.People REBUILD ; -- frag = 98.72%
ALTER INDEX FK_Sales_Invoices_AccountsPersonID ON Sales.Invoices REBUILD ; -- frag = 98.68%
ALTER INDEX FK_Sales_Invoices_BillToCustomerID ON Sales.Invoices REBUILD ; -- frag = 98.68%
ALTER INDEX PK_Sales_Invoices ON Sales.Invoices REORGANIZE ; -- frag = 15.57%
GO