Estou criando um banco de dados no qual haverá cerca de 30 tabelas, com cada tabela contendo dezenas de milhões de linhas e cada tabela contendo uma única coluna importante e uma coluna de chave primária/estrangeira para maximizar a eficiência da consulta em face de pesadas atualizações e inserções e fazem uso intenso de índices clusterizados. Duas das tabelas conterão dados textuais de comprimento variável, com uma delas contendo centenas de milhões de linhas, mas o restante conterá apenas dados numéricos.
Como eu realmente quero espremer até a última gota de desempenho do hardware que tenho disponível (cerca de 64 GB de RAM, um SSD muito rápido e 16 núcleos), pensei em permitir que cada mesa tenha seu próprio arquivo para que, não importa se Estou juntando em 2, 3, 4, 5 ou mais tabelas, cada tabela sempre será lida usando um thread separado e a estrutura de cada arquivo será alinhada com o conteúdo da tabela, o que, com sorte, minimizaria a fragmentação e a tornaria mais rápida para o SQL Server adicionar ao conteúdo de qualquer tabela.
Uma ressalva, estou preso no SQL Server 2008 R2 Web Edition . O que significa que não posso usar o particionamento horizontal automático, o que descarta isso como uma melhoria de desempenho.
O uso de um arquivo por tabela realmente maximizará o desempenho ou estou ignorando as características internas do mecanismo do SQL Server que tornariam isso redundante?
Em segundo lugar, se usar um arquivo por tabela é vantajoso, por que create tablesó me dá a opção de alocar a tabela para um grupo de arquivos e não para um arquivo lógico específico? Isso exigiria que eu criasse um grupo de arquivos separado para cada arquivo em meu cenário, o que me sugere que talvez o SQL Server não esteja prevendo as vantagens que presumo viriam de fazer o que estou propondo.
Do que diabos você está falando? Não tenho certeza de onde você obteve suas informações, mas certamente deve descartar essa fonte. Nada do que você assume aqui é realmente correto.
Se você quiser ler uma boa discussão sobre desempenho de SSD para SQL Server, existem várias séries de blogs por aí. Como sempre, o de Paul Randal é o mais lido:
Brent também tem uma boa apresentação sobre o tema: SQL em SSDs: Hot and Crazy Love e tem mais por aí.
Passando por todas essas apresentações, você notará rapidamente que todas elas se concentram nas gravações , pois é aqui que o desempenho dos SSDs entra em cena. O texto do seu post é quase inteiramente sobre leituras, que é um tópico diferente. Se as leituras são o seu ponto problemático, você deve falar sobre RAM, não sobre SSDs e sobre estratégias adequadas de indexação e consulta.
Minha primeira sugestão seria não fazer suposições sobre desempenho sem fazer testes de carga em ambas as configurações.
Meu palpite de ter visto tais configurações (que fazem sentido no papel) no passado seria que ter cada tabela em um arquivo separado não teria um impacto positivo mensurável para o desempenho... e que a complexidade adicional compensaria quaisquer ganhos de desempenho mesmo que fossem mensuráveis.
Por fim, quando se trata de espremer cada gota de desempenho de um Sql Server, indico o seguinte gráfico (fornecido pela minha Microsoft):
Quaisquer otimizações potenciais que possam ser feitas a partir de uma perspectiva de aplicativo facilmente ofuscam quaisquer otimizações possíveis em um nível de configuração de hardware/banco de dados... portanto, concentre sua atenção adequadamente.
Como outros observaram, não há benefício direto de um arquivo por tabela; aqui está uma ótima sinopse de Steve Jones sobre como esse mito se originou: http://www.sqlservercentral.com/blogs/steve_jones/2009/10/13/sql-server-legend-data-files-and-threads/
Você também pode querer investigar uma exibição particionada que acredito ser suportada pelo 2008 Web Edition. Existem alguns truques para codificar em uma exibição particionada, mas você pode imitar muitas das funcionalidades das tabelas particionadas com relativa facilidade.
Acho que arquivos separados para cada tabela não trariam nenhum benefício de desempenho. Os índices corretos podem ter um aumento potencial de desempenho (leitura de disco) no servidor de banco de dados.
O SQL Server 2008 R2 suporta compactação? Se sim, ligue isso.
Corrija-me se eu estiver errado.