«

»

abr 21

Fragmentação das bases

No artigo “Seu job de rebuild demora muito” explicamos a diferença entre os principais tipos de fragmentação das bases do SQL Server, a diferença entre REBUILD e REORGANIZE, o que é FILL FACTOR, além de um script que faz REORGANIZE quando a fragmentação externa é inferior a 30% e REBUILD se for maior. Também explica como alterar este parâmetro caso você queira alterar os 30% para outro valor.

Como muitos ambientes são grandes e não comportam a automatização deste tipo de atividade, neste artigo vamos explicar como levantar e analisar a fragmentação para executá-la quando necessário, podendo definir uma estratégia.

 

Problema

 

Analisar e documentar a fragmentação interna e externa das páginas de dados do SQL Server, detalhando os objetos impactados com informações como nome da tabela, nome do índice, tipo (cluster, heaps, etc.), porcentagem de fragmentação externa, porcentagem de fragmentação interna, quantidade de fragmentos e quantidade de registros do objeto.

 

Solução

 

Encontramos muitos scripts na internet capazes de nos fornecer dados sobre fragmentação das bases, porém, apesar de utilizar a mesma fonte (sys.dm_db_index_physical_stats) a maioria é pobre em informações e não nos permite avaliar corretamente o impacto ou tipo da fragmentação.

O Arshad Ali escreveu este excelente artigo (em inglês) no MSSQL Tips, explicando os tipos de fragmentação, como extrair informações da sys.dm_db_index_physical_stats e como interpretar os resultados. Alterei um pouco seu scritp para atender nossa necessidade, agora vamos ao que interessa.

 

Como utilizar a DMV sys.dm_db_index_physical_stats

 

A sintaxe é bem simples:

 

SELECT * FROM sys.dm_db_index_physical_stats (database_id, object_id, index_id, partition_number, mode) 

 

Os três primeiros parâmetros são intuitivos, ID da base, ID do objeto, ID do índice e número da partição. O último parâmetro define o tipo de retorno que teremos que pode ser LIMITED, SAMPLED ou DETAILED.

Para todos os parâmetros podemos utilizar NULL, aceitando assim seus valores default. Ex:

 

SELECT * FROM sys.dm_db_index_physical_stats (null,null,null,null,null);

 

Se quiser obter informações detalhadas de todas as bases, objetos e índices utilize:

 

SELECT * FROM sys.dm_db_index_physical_stats (null,null,null,null,’DETAILED’);

 

O script

 

Agora que já entendemos de onde partem as informações, vamos ao script. Fiz algumas adaptações para verificar todas as bases exceto do próprio SQL:

  

DECLARE @db_id int;
DECLARE db_cursor CURSOR FOR
SELECT dbid
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @db_id

WHILE @@FETCH_STATUS = 0
BEGIN
select DB_NAME(@db_id) as [Database]
,object_name(IPS.[object_id]) as [TableName]
,SI.index_id as [SI.index_id]
,SI.name AS [IndexName]
,IPS.index_type_desc
,IPS.index_id
,ROUND(IPS.avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
,ROUND(IPS.avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
,IPS.record_count
,IPS.fragment_count
from sys.dm_db_index_physical_stats(@db_id, null, null, null, ‘detailed’) as IPS
inner join sys.indexes as SI with (nolock) on IPS.[object_id] = SI.[object_id] and IPS.index_id = SI.index_id
inner join sys.tables as ST with (nolock) on IPS.[object_id] = ST.[object_id]
where ST.is_ms_shipped = 0
and IPS.avg_fragmentation_in_percent>=10 
and IPS.page_count>25 
and IPS.index_type_desc<>’heap’ 
and IPS.index_level=0 
order by IPS.avg_fragmentation_in_percent desc

FETCH NEXT FROM db_cursor INTO @db_id
END
CLOSE db_cursor
DEALLOCATE db_cursor

 

Pra quem quer entender o script, estou apenas dando um select na DMV que comentamos acima e relacionando ela com as tabelas de sistema sys.indexes e sys.tables, para obtermos os nomes dos objetos. Na cláusula where temos:

 

is_ms_shipped = para distinguir objetos criados por você dos objetos que já vem no SQL Server;

avg_fragmentation_in_percent > 10 = não queremos saber sobre os objetos contíguos, somente os fragmentados;

page_count > 25 = objetos muito pequenos nem compensa levantar fragmentação. Uma tabela que ocupa duas páginas, por exemplo, se estas não estiverem uma ao lado da outra, já estaria 50% fragmentada. Vamos ver somente objetos com mais de 25 páginas;

index_type_desc <> ‘heap’  = eu geralmente comento esta linha pois se é heap e tem mais de 25 páginas, deveria ao menos ter um índice, então já aproveito este report para levantar essas heaps;

index_level = para o relatório não ficar confuso, vamos analisar o nível onde ficam os dados, já que durante o REBUILD todos os níveis são refeitos;

 

O resultado do script

 

Vamos ver o que este script traz e interpretar os dados:

 

 

Tirando as colunas óbvias como TableName, IndexName, etc., vamos ver o que diz cada coluna:

 

avg_fragmentation_in_percent: diz respeito a fragmentação lógica, ou seja, de todas as páginas do objeto quantas estão fora de ordem, em porcentagem;

avg_page_space_used_in_percent: aqui podemos ver a fragmentação física, o quanto as páginas de dados estão preenchidas com registros. Depois de fazer um REBUILD com o FILL FACTOR em 90%, por exemplo, devemos ver esta coluna com 90% no caso dos índices;

record_count: quantos registros este objeto possui. É uma informação útil pra saber se é um objeto grande, com muitos registros;

fragment_count: suponhamos que você tem um objeto de 30 páginas e as dez primeiras estão contíguas em um bloco, outras dez estão 50 páginas depois e as últimas 10 em um outro bloco de páginas mais adiante. Neste caso teríamos fragment_count igual a 3.

 

Agora, utilizando a linha 1 como exemplo, podemos ver que na tabela tbCustomer, o índice tbCustomer5 é do tipo não cluster, está 98,96% fragmentado, suas páginas estão em média com 59,44% do espaço preenchido, possui 37.882 registros e está espalhado em 288 pedaços pelo arquivo de dados.

 

Veja que o script ordena colocando os mais fragmentados no início, utilizando a coluna avg_fragmentation_in_percent. Eu copio os dados, removo as colunas de ID, formato como tabela e rapidamente tenho uma tabela bonita pra colocar no relatório. Ajustei o script pra já arredondar a fragmentação e o espaço utilizado da página, agora já retorna tipo 96,55% ao invés de vários dígitos:

 

 

Quem gostou ou tem críticas pode comentar, curtir no Facebook, Google+, Tweetar ou postar em qualquer outra rede social.

 

Deixe uma resposta