Verificaremos quais procedimentos podemos tomar para que a desfragmentação do sistema operacional tenha efeito sobre os datafiles, além de observar a performance das consultas dentro do banco de dados.
Faremos um rápido estudo sobre a estrutura de um arquivo dentro do Windows e como o SQL Server se comporta gravando as informações dentro do datafile, além de entender como ele organiza essas informações internamente.
Saber tirar proveito dos recursos da desfragmentação é essencial na busca pela otimização de seu banco de dados. Dados desfragmentados minimizam o custo de uma das operações mais custosas desempenhadas por um SGBD: o acesso físico aos dados.
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.
Sistemas de arquivos NTFS e sua integração no SQL Server
O sistema de arquivos NTFS (New Technology File System) foi desenvolvido pela Microsoft na década de 80 para o Windows NT, ...
