SQL Server 2005/2008 - vários arquivos / grupos de arquivos - quantos? Por quê?

Question

Eu sou um desenvolvedor no coração - mas de vez em quando, um cliente não tem um DBA decente para lidar com esses problemas, então eu sou chamado para decidir ...

Quais são suas estratégias / melhores práticas quando se trata de lidar com um banco de dados SQL Server de tamanho razoável (qualquer coisa maior que Northwind ou AdventureWorks; aproximadamente 2 a 4 GB de dados mais índices etc.) - você usa vários arquivos / grupos de arquivos?

Se sim: quantos? E por quê?

Quais são seus critérios para decidir quando se afastar da abordagem "um grupo de arquivos para tudo":

* database size?
* database complexity?
* availability / reliability requirements?
* what else?

Se você usa vários grupos de arquivos, quantos usa? Um para dados, um para índice, um para log? Vários (quantos) para dados? Quais são os seus motivos para sua escolha - por que você usa esse número exato de grupos de arquivos: -)

Obrigado por quaisquer sugestões, sugestões, pensamentos!

Felicidades, Marc

    

Answer 1

A regra básica é separar arquivos em volumes diferentes para evitar contenção, no entanto, a quantidade de ganho de desempenho que você obtém varia consideravelmente de acordo com o subsistema de E / S e a carga de trabalho. Por exemplo, múltiplos arquivos em um único fuso físico vão sugar até onde vai o desempenho, mas o mesmo arranjo com o volume que está em um SAN LUN com várias centenas de drives de arrays RAID 10 pode ser ótimo. Os contadores de tamanho de fila de disco são seus amigos como a maneira mais simples de saber se você tem um afunilamento de E / S.

Você está olhando para os padrões de E / S nos bancos de dados - somente leitura, leitura-principalmente, leitura-gravação, gravação-principalmente, somente gravação - e baseando as coisas nisso. Você também precisa escolher o nível de RAID correto e certificar-se de que as compensações de partição de disco, o tamanho da faixa RAID e o tamanho da unidade de alocação NTFS estejam definidos corretamente. Algumas pessoas gostam de separar os índices não clusterizados em um grupo de arquivos separado, mas os ganhos de desempenho aqui variam exatamente como expliquei acima.

Além do desempenho, você deve considerar a capacidade de gerenciamento e a capacidade de recuperação. Ter um único arquivo de dados monolítico para um banco de dados de 100 GB significa que sua unidade de restauração é esse arquivo. A divisão em 4 grupos de arquivos de 25 GB significa que você pode usar a disponibilidade parcial do banco de dados e a restauração parcial para restaurar apenas um único grupo de arquivos no caso de ser danificado. Ao particionar tabelas e índices em vários grupos de arquivos, você também pode limitar quais partes do banco de dados são afetadas pelas operações de manutenção (por exemplo, remoção de fragmentação de índice).

O Tempdb é um caso especial, e eu vou apontar para você em um post meu que explica tudo sobre o porquê e como dividir o tempdb - há muitos equívocos por aí.

Sem fornecer uma recomendação de "generalização generalizada" aqui, vou mostrar um monte de white papers e postagens de blog para você ler:

• Whitepaper: Projeto de armazenamento de banco de dados físico
• Whitepaper: Práticas recomendadas de E / S de pré-implantação
• Whitepaper: Tabelas e índices particionados no SQL Server 2005

• White paper: Disponibilidade parcial do banco de dados

• Postagem no blog: Equívocos em torno do TF 1118 ( layout tempdb)

• Postagem no blog: Seus deslocamentos de partição de disco, tamanhos de faixa RAID e unidades de alocação NTFS estão definidos corretamente? (com link para o white paper da partição de disco)

Espero que isso ajude você!

    

Answer 2

A decisão de dividir um banco de dados em diferentes grupos de arquivos deve ser tomada depois de analisar o tamanho atual e o crescimento futuro de suas tabelas. Na minha opinião, a menos que você tenha um grande banco de dados ou tabelas com milhões de linhas, você deve considerar cuidadosamente os prós e contras, pois você pode acabar criando mais problemas de desempenho do que consertar.

Existem alguns cenários que podem ser interessantes sob certas premissas:

• 2 grupos de arquivos: dados e índice
• 3 grupos de arquivos: tabelas somente leitura, tabelas de leitura / gravação, índice
• vários grupos de arquivos: somente leitura, leitura-gravação, índice, tabela de chaves 1, tabela de chaves 2, ...

Você precisa analisar seu ambiente para decidir se os grupos de arquivos ajudarão nas necessidades de crescimento, uso e desempenho do SQL Server.

Alguns indicadores-chave para migrar para vários grupos de arquivos (de este artigo ):

• Quando o enfileiramento de disco está causando problemas no aplicativo e na experiência do usuário
  • Se esse for o caso, considere utilizar unidades de disco adicionais com novos grupos de arquivos que hospedam tabelas intensivas de E / S
• Quando tabelas específicas são 10% ou mais do banco de dados
  • Se esse for o caso, considere mover essas tabelas particularmente grandes para separar grupos de arquivos em unidades de disco subjacentes separadas
  • Dependendo do tamanho da tabela em proporção ao restante das tabelas, considere a possibilidade de criar um grupo de arquivos para tabela (s) individual (is)
• Quando o índice e o espaço de dados não agrupados são iguais em tabelas grandes
  • Se esse for o caso, considere a divisão dos dados e do índice clusterizado dos índices não clusterizados
• Quando existe uma porcentagem quase igual de dados somente leitura e de leitura / gravação no banco de dados
  • Se esse for o caso, considere a divisão dos dados somente leitura em um grupo de arquivos separado como os dados de leitura / gravação
• Quando há tempo insuficiente disponível para executar a manutenção do banco de dados
  • Se este for o caso, considere dividir as tabelas grandes em grupos de arquivos separados em discos subjacentes diferentes e executar a manutenção em paralelo
• Quando a empresa ou o aplicativo estiver mudando significativamente e os dados vão crescer a uma taxa muito maior
  • Se esse for o caso, considere trabalhar com os usuários para entender o possível crescimento
• Quando os dados arquivados residem no mesmo banco de dados que os dados de produção
  • Se esse for o caso, considere grupos de arquivos separados ou uma ou mais das técnicas nesta dica - Arquivando dados no SQL Server

Se você achar que grupos de arquivos podem melhorar o desempenho do seu banco de dados, escreva o código e teste o processo em um ambiente de preparação antes de implementar as alterações em seus servidores de produção. Prepare algumas medidas antes de implementar as alterações e compare-as antes / depois. Como esses processos podem ser muito intensivos em recursos e demorados, execute esses procedimentos durante um período de manutenção.

Não se esqueça de que ao criar novos objetos (tabelas e índices), os objetos devem ser criados no grupo de arquivos correto para garantir o desempenho esperado e validar periodicamente os objetos do banco de dados nos grupos de arquivos corretos e corrigir conforme necessário.