none
Uso de Filegroups x Files RRS feed

  • Pergunta

  • Ola.

    Tenho um banco de dados relativamente grande (+200Gb). Algumas tabelas somam mais de 500.000.000 de linhas e estão todas particionadas. Costumo criar uma coluna identity neste tipo de tabela (LOB) que defino como minha primary key clusterizada.

    Em seguida, defino os ranges de particionamento. Supondo que eu estime que a tabela terá, por exemplo, 100.000.000 de linhas eu crio, por exemplo, 100 filegroups no banco. Algo como:

    ALTER DATABASE [Banco] ADD FILEGROUP [P_00000001_01000000];
    ALTER DATABASE [Banco] ADD FILEGROUP [P_01000001_02000000]
    ;
    ALTER DATABASE [Banco] ADD FILEGROUP [P_02000001_03000000]
    ;
    ...

    Em seguida, adiciono também 100 arquivos, 1 para cada filegroup criado:

    ALTER DATABASE [Banco] ADD FILE (NAME = N'..., FILENAME = N'...',(...)) TO FILEGROUP [P_00000001_01000000];
    ALTER DATABASE [Banco] ADD FILE (NAME = N'..., FILENAME = N'...',(...)) TO FILEGROUP [P_01000001_02000000];
    ALTER DATABASE [Banco] ADD FILE (NAME = N'..., FILENAME = N'...',(...)) TO FILEGROUP [P_02000001_03000000];

    Por último, crio uma função de particionamento com ranges de 1.000.000 em 1.000.000 e associo um schema de particionamento a função de modo que os dados vão sendo colocados no arquivos conforme o campo identity.

    Obviamente a chave primaria da tabela é criada "embaixo" do schema.

    Tudo isso tem funcionado perfeitamente, com grande performance. Costumo particionar também meus índices utilizando includes e, recentemente a cláusula "where" do SQL Server 2008.

    Minha preocupação é com o número de arquivos e Filegroups que o banco está atingindo (já passei de 1.100 arquivos/filegroups).

    Pergunta: isso é um problema? O SQL trabalha bem com grandes quantidades de arquivos associadas a um banco de dados ou estou criando uma "bomba relógio"?


    mglauco
    • Movido Gustavo Maia Aguiar terça-feira, 1 de setembro de 2009 19:10 (De:SQL Server - Desenvolvimento Geral)
    terça-feira, 1 de setembro de 2009 18:58

Respostas

  • Marcelo,

          Imaginando que você não tem 1100 discos físicos reais, pode ser que a sua performance tenha atingido o máximo de performance baseado em discos reais. Acho desnecessário você ter tantos arquivos/filegroups, mas até aí, sua analise deve ter evidenciado essa necessidade. 

          Enfim, como essa não é a sua pergunta, e sim se o número de arquivos não é excessivo, eu fiz algumas pesquisas e não encontrei muitas informações. Entretanto, acho que encontrei uma luz no fim do tunel... De qualquer forma, aguardarei também os comentários dos colegas pois posso me equivocar em alguns pontos...


           O máximo de arquivos que um diretório NTFS pode suportar é 4.294.967.295
           Baseado na view sys.database_files, que possui em sua estrutura um campo chamado File_id do tipo int, me leva a crer que o máximo de arquivos que um banco pode ter é 2.147.483.647, porém a view de compatibilidade sys.sysfiles possui o mesmo campo do tipo smallint (32.767)


    MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008
    terça-feira, 1 de setembro de 2009 20:13
    Moderador
  • Marcelo,

    Um dos principais pontos positivos em performance ao particionar uma tabela é por qual campo a função de partição está dividindo as partições, bem como, cada partição em um disco separado.

    Imagina uma tabela com 1 milhão de registros e que você particiona pelo id mas as principais consultas usa no where o campo data? Além do que, não vejo benefícios em particionar dados e usar o mesmo HD.

    Abraços
    Demétrio Silva
    terça-feira, 1 de setembro de 2009 20:18
  • Como tudo na vida o importante é encontrarmos o "equilibrio", 1100 datafiles me parece um tanto exagerado, por você ter comentado que somente algumas tabelas precisam de particionamento.

    E...o particionamento não precisa necessariamente ser feito por faixas de blocos iguais, você pode ter um bloco com mais info que outro, essa divisão dependerá da analise de acesso aos dados que foi ou deveria ter sido feita.

    Se você está criando um "mostrinho" é dificil dizer, pois vai depender do seu ambiente em geral, mas com certeza a carga de trabalho administrativo aumentou.
    Alex Rosa -- Sharing my knowledge at http://www.keep-learning.com/blog
    quarta-feira, 2 de setembro de 2009 12:37

Todas as Respostas

  • Marcelo,

          Imaginando que você não tem 1100 discos físicos reais, pode ser que a sua performance tenha atingido o máximo de performance baseado em discos reais. Acho desnecessário você ter tantos arquivos/filegroups, mas até aí, sua analise deve ter evidenciado essa necessidade. 

          Enfim, como essa não é a sua pergunta, e sim se o número de arquivos não é excessivo, eu fiz algumas pesquisas e não encontrei muitas informações. Entretanto, acho que encontrei uma luz no fim do tunel... De qualquer forma, aguardarei também os comentários dos colegas pois posso me equivocar em alguns pontos...


           O máximo de arquivos que um diretório NTFS pode suportar é 4.294.967.295
           Baseado na view sys.database_files, que possui em sua estrutura um campo chamado File_id do tipo int, me leva a crer que o máximo de arquivos que um banco pode ter é 2.147.483.647, porém a view de compatibilidade sys.sysfiles possui o mesmo campo do tipo smallint (32.767)


    MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008
    terça-feira, 1 de setembro de 2009 20:13
    Moderador
  • Marcelo,

    Um dos principais pontos positivos em performance ao particionar uma tabela é por qual campo a função de partição está dividindo as partições, bem como, cada partição em um disco separado.

    Imagina uma tabela com 1 milhão de registros e que você particiona pelo id mas as principais consultas usa no where o campo data? Além do que, não vejo benefícios em particionar dados e usar o mesmo HD.

    Abraços
    Demétrio Silva
    terça-feira, 1 de setembro de 2009 20:18
  • Como tudo na vida o importante é encontrarmos o "equilibrio", 1100 datafiles me parece um tanto exagerado, por você ter comentado que somente algumas tabelas precisam de particionamento.

    E...o particionamento não precisa necessariamente ser feito por faixas de blocos iguais, você pode ter um bloco com mais info que outro, essa divisão dependerá da analise de acesso aos dados que foi ou deveria ter sido feita.

    Se você está criando um "mostrinho" é dificil dizer, pois vai depender do seu ambiente em geral, mas com certeza a carga de trabalho administrativo aumentou.
    Alex Rosa -- Sharing my knowledge at http://www.keep-learning.com/blog
    quarta-feira, 2 de setembro de 2009 12:37
  • Marcelo,

        Também acredito que é um tanto exagerado a quantidade de arquivos e sem um indice eficiente, realmente não há ganhos na sua performance e você pode até ter uma perda na performance. O particionamente de tabelas como a sua é recomendado como uma BestPratice. Um outro atrativo são os backups que ficam mais fáceis com um banco particionado.

    sds,
    Carlos Eduardo Pieren

    quinta-feira, 3 de setembro de 2009 18:47
  • Marcelo,

    Basicamente analisando as respostas dos colegas e considerações, podemos estabelecer como consideração de comum acordo que sua quantidade arquivos  filegroup esta muito acima de normal, mesmo que o SQL Server consiga endereçar e trabalhar com um volume bem maior que este.

    Em relação a performance também entedemos que este particionamento representa uma prática muito inteligente e versátil, mas também pode se tornar um grande problema de gerenciamento do seu ambiente.

    Eu fico imaginando a sua estrutura de backup para armazenar e controlar esta quantidade de arquivos.

    No caso do particionamento horizontal de dados que você esta utilizando, também vejo com bons olhos a utilização deste recurso, mas não precisariamos utilizar o particonamento do mesmo tamanho, poderiamos criar faixa de valores maiores e dimensionar este particionamento de outra forma.

    Agora gostaria de saber qual seria a consideração sobre este cenário.
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    sexta-feira, 4 de setembro de 2009 00:32
    Moderador