Detectando fragmentação em tabelas para as versões Microsoft SQL Server 2000 e 2005

Talvez você já tenha tido algum problema de performance em seu banco de dados e precisou fazer algum tunning para tentar melhorá-lo.  Este artigo visa ajudá-lo nesta árdua tarefa, dando mais condições de analisar cenários onde a fragmentação das tabelas influi diretamente no desempenho de um sistema de banco de dados.

A necessidade de índices no banco de dados é no mínimo indispensável, pois eles possibilitam acelerar o acesso aos dados. Sem esses índices, o SQL Server precisaria percorrer cada tabela lendo cada registro para localizar um dado qualquer. A primeira coisa que você precisa observar em um índice, principalmente os agrupados, é a divisão das páginas. Uma divisão de página ocorre quando uma página de dados fica 100% cheia e mais dados precisam ser adicionados a ela. A técnica de divisão de páginas possui várias desvantagens para o SGBD. Primeiro, a nova página agora fica fora de ordem. Com isso, em vez de ir direto de uma página para a seguinte ao procurar os dados, o SQL Server precisa ir de um lado para outro no banco de dados para ler a página seguinte de que precisa, esta situação chamamos de fragmentação. Além disso, o servidor também deve levar algum tempo apagando metade dos registros da página que está cheia e copiá-los na nova página.

Para superar os efeitos da fragmentação no banco de dados, é necessário reconstruir os índices das tabelas. Logo, precisamos ter em mente o que é necessário saber para identificar se uma tabela esta fragmentada. Aqui vai uma dica:

- Se a execução da consulta está levando um tempo maior do que o esperado, tendo muita atividade de disco rígido e baixa utilização de cachê, verifique o I/O da consulta e o número de Scan Density. Se este número for menor que 100, é porque existe alguma fragmentação. No SQL 2000 use a instrução DBCC SHOWCONTIG, já para no SQL 2005 utilize a visão de gerenciamento dinâmico sys.dm_db_index_physical_stats.

Como você pode medir uma tabela que está fragmentada e como você pode reduzir a fragmentação?

No Servidor SQL 2000, você pode usar o comando DBCC SHOWCONTIG, que mostrará a densidade e o nível de fragmentação de uma tabela. Se você usar esta instrução com o parâmetro WITH TABLERESULTS, o SQL 2000 expõe os resultados como um conjunto de linhas com informação adicional. As instruções DBCC INDEXDEFRAG e DBCC DBREINDEX são as mais usadas para reduzir a fragmentação de uma tabela. Veja exemplo abaixo:

use PROMO
DECLARE @table_id int
SET @table_id = OBJECT_ID('tMateria')
DBCC SHOWCONTIG(@table_id)

Resultado da consulta:

- Pages Scanned................................: 4280
- Extents Scanned..............................: 543
- Extent Switches..............................: 900
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 59.38% [535:901]
- Logical Scan Fragmentation ..................: 9.07%
- Extent Scan Fragmentation ...................: 30.02%
- Avg. Bytes Free per Page.....................: 1415.9
- Avg. Page Density (full).....................: 82.51%

Observe que o indicador Scan Density informa que não é contíguo, ou seja, que existe fragmentação na tabela, o número ideal para o Scan Density será 100. O Best Count é o número ideal de alterações de extensão se tudo fosse ligado continuamente. A seguir demonstraremos uma maneira de refazer um índice que tenha sido usado como chave primária. O DBCC DBREINDEX, também pode ser usado para corrigir índices danificados e reconstruir vários índices de uma só vez.

A sintaxe da instrução DBCC DBREINDEX :
DBCC DBREINDEX( 'tabela do banco de dados', chave primária, fator de preenchimento-Fill Factor)

use PROMO
DBCC DBREINDEX( 'dbo.tMateria',pk_tMateria,90)

use PROMO
DECLARE @table_id int
SET @table_id = OBJECT_ID('tMateria')
DBCC SHOWCONTIG(@table_id)

Resultado da consulta:

- Pages Scanned................................: 3853
- Extents Scanned..............................: 483
- Extent Switches..............................: 482
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.79% [482:483]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.62%
- Avg. Bytes Free per Page.....................: 675.6
- Avg. Page Density (full).....................: 91.65%

Note que o indicador Scan Density ficou próximo do ótimo, isso significa que reconstruirmos com êxito os índices, eliminando a fragmentação encontrada.

O DBCC INDEXDEFRAG faz o defrag no nível de folha de um índice, que permitirá à ordem física de páginas combinarem com a ordem lógica dos nós de folha. Esta instrução informa a porcentagem prevista do processo a cada cinco minutos.

Importante: Mesmo se os índices forem propriamente criados, eles tendem a perder a eficácia dentro de algum tempo devido à fragmentação que ocorre na tabela em todos os processos de INSERT, UPDATE E DELETE.

E no SQL Server 2005?

Nesta nova versão do SQL Server, a instrução DBCC SHOWCONTIG foi substituída por uma tabela Gerenciamento Dinâmico: sys.dm_db_index_physical_stats. Com ela podemos descobrir a fragmentação em um índice específico ou todos os índices em uma tabela ou visão indexada. Em caso de índices particionados, esta DMV também fornecerá a informação sobre fragmentação de cada partição. A fragmentação excessiva é problemática em grandes operações de I/O. A função com valor sys.dm_db_index_physical_stats da tabela Gerenciamento Dinâmico, retorna o percentual de fragmentação na coluna avg_fragmentation_in_percent. A fragmentação não deve exceder os 25%. Reduzir a fragmentação de índices pode beneficiar verificações de grandes intervalos, comuns em cenários de data warehouse e relatórios. A sintaxe geral desta DMV é:

SELECT * FROM sys.dm_db_index_physical_stats  (DB_ID(N'<Database Name>'), OBJECT_ID(N' '), NULL, NULL , '<SCANNING MODE>');

Para retornar a informação de todas as tabelas e índices, use:

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

O Scanning Mode ajuda a determinar o nível de scaneamento executado para obter os dados estatísticos usados pela função. Os três modos que podemos utilizar são: LIMITED, SAMPLED, ou DETAILED. O modo de default é LIMITED, que mostra o page counts e a fragmentação externa sem qualquer densidade da página. Os modos de SAMPLED E DETAILED analisarão a tabela inteira tanto da fragmentação interna como da externa. Veja o exemplo.

 

SELECT * INTO #tbl_Fragmentation FROM sys.dm_db_index_physical_stats (DatabaseID, TableId, IndexId, NULL, Mode);

Observe que no Scanning Mode da instrução acima será LIMITED, que é o comportamento de default no Servidor SQL 2005. Para adquirir mais informação nos resultados, execute como se segue:

SELECT * INTO #tbl_Fragmentation FROM sys.dm_db_index_physical_stats (DatabaseID, TableId, IndexId, NULL, DETAILED);

Algumas dicas para evitar a fragmentação

·         Quando um banco de dados é criado, assegure-se que os arquivos de dados sejam criados com os maiores valores possíveis de acordo com a modelagem de dados e destino. Você pode fazer isto planejando usar um valor que pode ajustar-se o montante máximo de dados durante certo período.

·         Às vezes é fatível permitir aos arquivos de dados crescerem automaticamente guardando um limite no crescimento, especificando um tamanho de crescimento de arquivo de dados máximo que deixe algum espaço disponível no disco rígido.

·         Depois de um determinado período, apure e reavalie o tamanho do banco de dados estimado acrescentando mais arquivos ou filegroups ao banco de dados, se necessário.

·         Não deixe os arquivos de dados crescerem automaticamente, se lá muitos arquivos de dados que compartilham a mesma partição de disco. Se os arquivos de dados forem pesadamente usados, então os coloque em filegroups diferentes ou em uma partição distinta.

·         Execute tarefas de manutenção de banco de dados regularmente, como DBCC DBREINDEX, e também a recompilação de stored procedures and triggers.

·         Se a(s) linha(s) da tabela forem modificadas ou eliminadas freqüentemente, então é interessante executar a instrução UPDATE STATISTICS na tabela, que o ajudará a evitar qualquer performance lenta no plano de execução não esperada.