Design de tabela grande SQL

"Esta tabela tem os indecis apropriados, mas está se tornando um grande problema ao executar consultas"

O particionamento sozinho não ajuda no desempenho da consulta, a menos que o SQL Server seja capaz de eliminar as partições ao executar uma consulta. Sua cláusula WHERE precisa estar alinhada com a maneira como você particiona. Obtemos apenas um campo para usar como um campo de particionamento, portanto, se esse campo não estiver incluído em sua cláusula WHERE, é provável que você verifique a tabela inteira, apesar de ter partições.

"e apenas por causa de seu tamanho."

O particionamento pode facilitar certas operações de manutenção, mas ainda há coisas que não podemos fazer partindo por partição. Se a manutenção do índice e as atualizações de estatísticas estiverem causando problemas, é melhor dividir o design em uma tabela de arquivo e uma tabela atualizada ao vivo. Quando você precisa mover dados periodicamente da tabela ao vivo para a tabela de arquivo, faça isso, reconstrua os índices com fator de preenchimento de 100%, atualize as estatísticas com varredura completa e, em seguida, defina seu grupo de arquivos como somente leitura. O particionamento pode ajudar com as cargas da tabela de arquivo - mas o particionamento da tabela ao vivo pode não. (Estou lançando vários conceitos avançados aqui como se fosse rápido e simples, mas estou apenas esboçando alguns antecedentes aqui.)

"Parece que usar o particionamento exigiria menos alterações de código."

Mais ou menos - parece assim à primeira vista, mas quanto mais você se aprofunda, você tem opções como exibições particionadas. Você pode renomear a tabela existente, colocar uma exibição em seu lugar e, em seguida, fazer suas próprias alterações nas tabelas subjacentes (e adicionar várias tabelas) sem alterar seu aplicativo.

Eu escrevi mais sobre as armadilhas do particionamento aqui:

http://www.brentozar.com/archive/2008/06/sql-server-partitioning-not-the-answer-to-everything/

O particionamento isolado pode ser suficiente, mas você pode obter melhores resultados combinando com exibições particionadas e várias tabelas. Depende muito do padrão de consulta e crescimento.

A limitação atual com o particionamento é que as estatísticas de coluna são mantidas apenas em uma tabela, em vez de nível de partição. Se você tiver um padrão de consulta que se beneficie de estatísticas mais precisas, a combinação de particionamento de tabela com exibições particionadas pode gerar benefícios de desempenho significativos.

Onde a natureza de seus dados varia de mês a mês, ano a ano, as exibições particionadas também podem ajudar. Imagine um varejista que mudou suas linhas de produtos continuamente, de forma que há pouca consistência nas faixas de Product.ProductId em uso de ano para ano. Com uma única tabela de detalhes do pedido/pedido e, portanto, um único histograma de estatísticas, as estatísticas oferecerão pouco ao otimizador de consulta. Uma tabela por ano (Order_2010, Order_2011, OrderLine_2010, OrderLine_2011) particionada por mês e combinada com exibições particionadas (Order, OrderLine) fornecerá estatísticas mais granulares e potencialmente úteis para o otimizador.

Você pode introduzir o particionamento de tabela com relativamente pouco esforço, então comece por aí, meça o impacto e depois avalie se as exibições particionadas valeriam o esforço adicional.

Kimberly Tripp publicou muitas orientações e white papers sobre particionamento que geralmente são considerados leitura obrigatória sobre o assunto. Kendra Little também tem um bom material e uma lista de referência útil de outros artigos

O desempenho geralmente é o motivo número 1 para as pessoas procurarem o particionamento. Pessoalmente, vejo as melhorias no tempo de recuperação como um benefício igual ou maior com um VLDB. Reserve algum tempo para entender a disponibilidade parcial e a restauração fragmentada antes de começar, pois isso pode influenciar a abordagem adotada.

Se você tiver o processo não ideal, mas não incomum, de enviar backups pela rede, poderá estar procurando um tempo de restauração de 3 horas para seus 600 GB atuais. Em um ano em que você ultrapassou 1,5 TB, você tem um problema.

Como você disse, você tem duas opções aqui:

1. Utilizar várias tabelas
2. Utilizar o particionamento

Com 1, você pode criar uma VIEW que une todas essas tabelas e apenas atualizá-la para incluir tabelas recém-criadas. Eu considero isso realmente uma maneira de emular o particionamento. As vantagens desse método incluem não exigir a Enterprise Edition do SQL Server.

Com 2, você pode alinhar seus índices às suas partições e alinhar suas partições a diferentes armazenamentos. Depois de configurar sua função de partição e esquema de partição, isso é feito para você quando você divide ou mescla partições. As vantagens desse método incluem não ser necessário mover registros manualmente para uma nova tabela. Já que a função de partição e o esquema de partição cuidam disso para você. Além disso, como você disse, há pouca ou nenhuma alteração de código necessária para acessar os dados.

Se você tiver o Enterprise Edition, eu definitivamente daria uma olhada no particionamento. Apesar de parecer complexo, não é tão ruim assim. Caso contrário, o particionamento nem é uma opção para você.

Criando Tabelas Particionadas

Modificando Tabelas Particionadas

Projetando partições para gerenciar subconjuntos de dados

Espero que isto ajude,

Matt

Pela sua pergunta, você parece estar armazenando dados históricos (logs) e sua limitação parece vir da velocidade da consulta, não de problemas de armazenamento. Para mim, a partição não ajudará.

Quando você diz que tem índices adequados, isso inclui um índice no campo de data? Tive bons resultados usando index on trunc(timestamp, day) com Postgres. Em seguida, você deve garantir que todas as consultas sejam selecionadas no dia anterior a qualquer outra manipulação. Tenha cuidado, um timestamp com campo de fuso horário não é indexável (porque ele "se move" dependendo do fuso horário), então você precisa de um timestamp "fixo" para ser indexado.