Fragmentação de datafiles no SQL Server

Entre as várias dúvidas que se discutem no dia-a-dia do DBA, está a necessidade (ou não) de se realizar desfragmentação dos datafiles dos bancos de dados do SQL Server pelo Sistema Operacional. Outras questões pertinentes são: a performance das consultas no banco de dados é afetada pela fragmentação do Windows?

É necessária a realização de desfragmentarão periódica dos discos? Este tipo de fragmentação afeta o desempenho de leitura e escrita no database?

Iremos abordar essas dúvidas nesse artigo conceituando como o Windows armazena as informações em disco com o seu sistema de arquivos NTFS, entendendo qual “responsabilidade” o sistema operacional tem sobre as informações guardadas quando se trata dos datafiles do SQL Server.

Também analisaremos como os índices se comportam antes e depois da desfragmentação do datafile via sistema operacional, observando se existe algum ganho de performance na resposta do índice em uma consulta.

Conceituaremos fragmentação interna e externa e testaremos qual efeito a fragmentação causa em uma simples consulta dentro do banco de dados.

Iremos também fazer uma comparação entre o datafile fragmentado e um datafile não fragmentado. Partindo dessas ações, algumas configurações no SQL Server são muito importantes para melhorar a fragmentação do disco de maneira a minimizar efeitos negativos como lentidão nas consultas e alto picos de I/O.

Para isso, apresentaremos como a fragmentação pode ocorrer no SQL Server, sendo que ela pode ser do Windows (do datafile), quando o arquivo é gravado em vários “pedaços” dentro do disco rígido de maneira que o tempo de consulta entre os clusters acaba ficando maior e também temos a fragmentação dos índices (a fragmentação interna no banco de dados) que ocorre pelas inserções, deleções e atualizações de registros, deixando espaços vazios nas páginas de dados.

Nesse caso, é necessário a reconstrução (rebuild) dos índices para que os mesmos se tornem mais otimizados.

Veremos também que o uso da opção autogrow é prejudicial, podendo fragmentar os datafiles de maneira que afete o desempenho de consultas, inserções e atualizações nos databases.

Essa opção, combinada com a autoshrink pode afetar não somente a fragmentação, mas também aumentar o tempo de processamento, visto que o servidor necessita sempre analisar quando é necessário aumentar ou diminuir automaticamente sua base.

Com um script para análise do espaço dos datafiles frente a quantidade de espaço disponível, podemos avaliar quando será necessário crescer os datafiles do banco de dados manualmente, podendo o script ser executado a partir de um job.

Por fim, mostraremos como realizar uma análise do datafile do SQL Server através do Windows a fim de descobrir se é necessário realizar a desfragmentação nesses arquivos. O comando contig.exe mostra os espaços por cluster e fragmentação por bloco."