No SQL Server, quando você deve dividir seu PRIMARY Data FileGroup em arquivos de dados secundários?

Question

Atualmente, nosso banco de dados possui apenas um grupo de arquivos, PRIMARY, que contém aproximadamente 8 GB de dados (linhas de tabelas, índices, catálogo de texto completo).

Quando é um bom momento para dividir isso em arquivos de dados secundários? Quais são alguns critérios que eu deveria estar ciente?

    

Answer 1

Há duas partes nessa pergunta: quando adicionar um novo FILEGROUP e quando adicionar um novo FILE em um grupo de arquivos. Primeiro vamos falar teoria:

Mark está certo sobre o principal motivo de desempenho.

O motivo secundário é a recuperação de desastres. Com o SQL Server 2005 e mais recente, você pode fazer restaurações de grupo de arquivos. Quando ocorre um desastre, você pode restaurar apenas o primeiro grupo de arquivos primário e colocar o banco de dados parcialmente online para consultas. Os usuários podem executar consultas enquanto você restaura outros grupos de arquivos. Isso é útil para bancos de dados com uma grande quantidade de dados históricos que podem não ser necessários imediatamente, ou armazéns de dados que precisam carregar dados em tabelas atuais sem precisar de dados históricos para acesso.

Outro motivo é o perfil de leitura / gravação de grupos de dados. Se você tiver alguns dados que são constantemente gravados, e outros dados que recebem uma atividade de leitura pesada, você pode criar diferentes tipos de armazenamento para acomodar essas necessidades. Você poderia colocar o material de escrita pesada no ataque 10 e deixar as coisas com preconceito de leitura no ataque mais barato 5.

Agora, vamos falar de arquivos versus grupos de arquivos. Quando você coloca objetos no SQL Server, é necessário colocá-los no nível de grupo de arquivos. Você pode colocar uma tabela ou um índice em um grupo de arquivos, mas não pode escolher um arquivo específico. Então, tudo que discutimos até agora foi sobre quando adicionar um grupo de arquivos - mas quando você adiciona um arquivo?

Se você está projetando armazenamento e tem 80 discos rígidos, há algumas maneiras de dividi-lo:

• Um pool de 80 unidades
• Dois conjuntos de 40 unidades
• Quatro pools de 20 drives, etc ...

Diferentes subsistemas de armazenamento possuem diferentes perfis de desempenho. Eu trabalhei com algumas SANs que tiveram o melhor desempenho com matrizes de drive de 12 a 16, e qualquer coisa maior do que isso não teve uma melhoria de desempenho. Outro exemplo é SANs com múltiplos caminhos: se você tiver vários HBAs conectando seu servidor ao seu armazenamento, e se o seu software de múltiplos caminhos não for real / ativo, você pode precisar de um array por caminho para distribuir a carga. Quatro caminhos, quatro conjuntos de unidades terão melhor desempenho nesses tipos de unidades.

Nesses casos, você acaba com quatro matrizes diferentes, quatro unidades diferentes no Windows (a menos que você use pontos de montagem e, mesmo assim, são pastas diferentes) e precisará de quatro arquivos separados no SQL Server. Esses arquivos separados podem estar no mesmo grupo de arquivos.

    

Answer 2

O principal motivo é o desempenho. Quando ficar sem capacidade de IOPS na unidade de disco de grupo de arquivos principal, você precisará expandir para um segundo grupo de arquivos para dividir IOPS em vários discos / LUNs, dependendo da configuração de armazenamento.

EDITAR: Brad Wilson fez um bom comentário sobre os SSDs. Se você estiver usando um sistema de armazenamento SSD / SATA / FC composto, convém ter grupos de arquivos diferentes em diferentes tipos de armazenamento. Você pode, então, colocar suas tabelas de requisitos de IOPS extremas no SSD filegropus, enquanto as tabelas de histórico / estatísticas podem ser armazenadas em grupos de arquivos SATA baratos.

    

Answer 3

Também gostaria de salientar que há um aspecto de possibilidade de recuperação / disponibilidade de dados para essa questão também. Usando vários grupos de arquivos e não colocando nenhum objeto definido pelo usuário no grupo de arquivos principal, você tem mais flexibilidade para ativar as restaurações on-line. isso permite a restauração fragmentada no nível do grupo de arquivos.

A restauração on-line está disponível nas edições Enterprise e Developer do SQL Server posteriores a 2005

Outro pensamento que vem à mente é separar os dados de referência estáticos somente leitura dos dados transacionais. Para bancos de dados maiores, isso pode reduzir a quantidade de tempo e / ou espaço necessário para realizar um backup.