Desfragmentação de dados - Defrag SQL Server

A fragmentação no SQL Server é um processo decorrente de operações de insert, update e delete deixando a paginação de dados e a indexação comprometidas com dados gravados em regiões descontínuas e espaços vazios entre os setores.

Isso é um problema antigo, desde o sistema operacional MS-DOS com as gravações de arquivos e remete ao mesmo conceito voltado a índices de banco de dados. Observação importante: a fragmentação ocorre nos índeces.

 A fragmentação afeta o armazenamento e o desempenho das tabelas. Em breve, com toda evolução das IAs, isso irá acabar, ou deixará de ser manual. Mas por ora, o DBAs de plantão ainda serão acionados para resolver o problema.

Não irei abordar conceitos intrisicamente técnicos ou descrevendo os processos internos do banco de dados para chegar a fragmentação. Ao invés disso, estou indo direto ao ponto como resolvemos o problema via T-SQL.

Coloquemos dois objetivos em mente: redução da base de dados e melhoria de performance das consultas. A primeira fica logo evidente ao final do processo. Pela experiência, uma base pode reduzir em 10% seu armazenamento dependendo da taxa de crescimento do banco (deixarei este tema para outro momento) em cerca de 3 meses de uso para uma empresa em médio/pequeno porte.

Para um usuário (neste caso, uma empresa) que utiliza um SQL Express inferior a versão de 2025 limitado a 10Gb, a redução de armazenamento faz toda diferença. Mas a própria redução de base faz diferença também em espaço de HD/SSD ou mídias de backup, independente da versão do SQL Server.

Quando falamos em desfragmentação também levamos a melhoria da indexação, o que nos permite acessar dados com melhor performance e desconsiderar a criação de novos índices (que tomam espaço em disco e diminuem a performance dos insert, update e delete).

Vou descrever etapas da melhor estratégia para desfragmentar. Via de regra, faça isso com todas as tabela do banco de dados. Mas podemos reduzir o tempo de atendimento focando em algumas tabelas estratégicas.

1- Procure pela tabela mais utilizada no seu ERP e verifique se existem muitos índices e se ela contém muitas colunas e registros. Esta tabela está envolvidas em consulta e geralmente precisam de muitos índices. Ou seja, seus índices ao longo dos dias, estarão fragmentados;

2- Efetue a checagem dos índices desta tabela e verifique a fragmentação observado duas propriedades: Scan Density e Logical Scan Fragmentation. O primeiro diz quanto há de buracos entre seus dados e precisa ficar próximo de 100% para que demonstre que não há. O segundo diz como os dados estão em regiões descontínuas e precisa ficar próximo de 0% para que os dados estejam contínuos.

3- Processe a desfragmentação;

4- Efetue a checagem dos índices novamente;

5- Faça o corte/redução do arquivo físico do banco de dados (MDF).

PRÁTICA

Aplicando estas regras, via T-SQL:

1- Minha maior tabela, em relação ao uso, consultas, dados e tudo mais é a SWD_Est_Fios, possui muitos índices devido a várias consultas no ERP e mantém o controle do estoque de fios. 

2- No Management Studio, executo:

dbcc showcontig (SWD_Est_Fios) with all_indexes

Verifico as duas propriedades e encontro 4 índices. Vou destacar um em especial, mas você poderá ver que os outros estão em situações parecidas, ou seja, fragmentada também (figura 1):

Figura 1 - Índices fragmentados da tabela SWD_Est_Fios

3- Vamos desfragmentar este índice. Para isso usamos o indexdefrag passando como 1o parâmetro o valor 0 referente ao banco de dados atual, o seja, o banco de dados em uso; o 2o parâmetro passamos o nome da tabela e no 3o parâmetro o ID do índice a desfragmentar (ID 6):

dbcc indexdefrag (0, SWD_Est_Fios, 6)

Ao final do processo, como resultado, o SQL Server nos dá o número de páginas escaneadas, páginas movidas e páginas removidas.

4- Agora vamos conferir o Scan Density e Logical Scan Fragmentation como ficaram:

dbcc showcontig (SWD_Est_Fios) with all_indexes

O resultado foi (figura 2):

Figura 2 - Conferência dos índices da tabela SWD_Est_Fios

Observe que melhoramos muito nosso Scan Density de 56,52% para 93,79%, retirando muito espaço em desuso. O Logical Fragmentation foi de 11,34% para 1,11% deixando bem mais contínuo os dados.

Lembrando que não há o caso ideal, sempre serão valores próximos de 100% e 0%, respectivamente.

5- Agora que os espaços em branco foram remanejados para o final do banco de dados com a desfrgmentação, podemos fazer então o corte destes espaços e reduzir fisicamente a base de dados. 

O comando T-SQL que efetua isso é:

dbcc shrinkdatabase (DBSword)

O resultado é o tamanho atual, tamanho mínimo, páginas usadas, páginas estimadas. Sem entrar nestes detalhes, você pode verificar o tamanho físico do arquivo MDF, que deve ter reduzido de tamanho.

CONCLUSÃO FINAL

Ao final, todo processo pode ser resumido na figura 3 (sem o processo do SHRINKDATABASE):

Figura 3 - Passos de verificação, desfragmentação e conferência

BÔNUS

Agora, vamos fazer uma SP que toma conta de desfragmentar todos os índices da tabela:

DROP PROCEDURE SP_DefragTables
GO

CREATE PROCEDURE SP_DefragTables(@table varchar(50))
AS BEGIN
 declare @sql varchar(100),@indid int

 select @sql='dbcc showcontig ('+@table+') with all_indexes'
 exec(@sql)

 Select IndId into #tmpindexes 
 From sysobjects o 
   inner join sysindexes i on (o.id=i.id)
 Where o.name=@table 

 While (select count(*) from #tmpindexes)>0
 Begin
   select top 1 @indid=indid from #tmpindexes

   exec('dbcc indexdefrag (0,'+@table+','+@indid+')')

   delete from #tmpindexes where indid=@indid
 End

 exec(@sql)
END
GO

Para usar em uma tabela, execute:

Exec SP_DefragTables 'SWD_Est_Fios'

Esta rotina irá desfragmentar todos os índices da tabela SWD_Est_Fios (ou qualquer outra tabela).

Até a próxima !