Eu tenho uma tabela no MS SQL Server.
- Tamanho da mesa: 806 GB
- Linhas: 1,2 bilhão
- Espaço de índice: 1,2 GB
Uso da tabela: Log das chamadas do Web Service 99,9% é o uso do log, os desenvolvedores raramente consultam essa tabela no Prod (somente quando um problema é relatado ou pesquisado).
Chave Primária : Com base no "ID" que é do tipo de dados "INT". Há um índice clusterizado com base nessa coluna "ID".
Minhas intenções para esta mudança : Quer gerenciar esta tabela (como ela tem 10 anos de dados) e daqui para frente (devido a um novo requisito), existe a possibilidade de desenvolvedores / analistas aprofundarem essa tabela (apenas por alguns meses) e não quero criar uma nova tabela para o mesmo propósito.
Minhas perguntas :
[Pergunta principal] Posso particionar esta tabela com base em "DateCreated" (coluna DATETIME, NOT NULL), sem causar problemas (logicamente / em termos de desempenho).
[É bom saber] Quanto tempo ( eu entendo que depende do espaço do banco de dados/memória do servidor e outros detalhes, mas uma estimativa # seria bom ) seria necessário para particionar essa tabela enorme (se estiver tudo bem ter partição baseada na data) . Fazendo esta pergunta, pois esta é uma tabela de produção e as linhas são inseridas com frequência (agora ~ 350 registros/min).
[Não é exatamente uma pergunta, mas uma recomendação] Existe um plano melhor para gerenciar esta tabela (não deseja manter mais de 3 anos de dados em produção, o plano é mencionado abaixo )?
Plano atual (sou novo no MS SQL, então foi isso que eu criei):
- Mantenha 3 meses de dados em cada partição.
- Sistema para criar partições automaticamente antes de cada trimestre.
- Mantenha apenas 3 anos de partições na tabela ativa.
- Mova outras partições para a tabela OLD/ARCHIEVE (precisa criar isso). Dados realmente ANTIGOS a serem eliminados.
Resposta curta: não , você não pode fazer isso. De acordo com os documentos ...
Isso significa que, para particionar
[DateCreated]
, você também deve agrupar[DateCreated]
.Isso faz um pouco mais de sentido quando você pensa sobre o que o particionamento e o clustering realmente estão sob o capô.
Um índice clusterizado é a ordenação lógica de seus dados
O particionamento é uma técnica para gerenciar o armazenamento físico de seus dados com base em uma função
Essas duas coisas podem ser usadas juntas, desde que não entrem em conflito. Se você tentar fazer com que eles entrem em conflito, você vai se dar mal . Para responder seus itens ponto a ponto:
[DateCreated]
a menos que elimine e recrie o índice clusterizado para reconstruir a tabela nessa coluna. Isso é possível, mas provavelmente é um projeto maior do que você planejou quando fez a pergunta.Se você decidir fazer isso de uma só vez, é provável que seja limitado pela velocidade do disco.
Uma operação comparável seria fazer backups. Backups lêem um monte de bytes daqui e escrevem um monte de bytes ali . Assim como a reconstrução da sua tabela. Portanto, se seu banco de dados tiver, digamos, 1,6 TB e um backup levar 2 horas, provavelmente a reconstrução de uma tabela de 800 GB levará cerca de 1 hora (metade do tamanho, metade do tempo).
É claro que existem muitas diferenças entre essas duas tarefas. As unidades de backup podem ser diferentes das unidades de dados, cargas de trabalho simultâneas as afetarão, caminhos de rede para uma SAN, índices secundários, yadda yadda. Portanto, os tempos serão diferentes, mas não precisam ser ordens de magnitude diferentes.
Em vez de um big bang, você pode lidar melhor com esse caso de uso de cada vez, em sequência cronológica inversa.
Exclua os dados mais antigos dos quais você deseja se livrar. Como são 10 anos e você quer apenas 3 o problema instantaneamente fica 70% mais fácil! Isso é feito melhor excluindo pedaços de cada vez, em vez de todos em uma única consulta. Se ele não puder ser excluído imediatamente, que tal movê-lo para um servidor OLAP ou despejá-lo em um formato de arquivo compactado (parquet, ORC e outros)?
Crie uma nova tabela vazia com o esquema de particionamento desejado e partições definidas para todos os 3 anos de história e alguns no futuro. Mova os dados mais antigos primeiro, em partes, inserindo na nova tabela e excluindo da atual. Suponho que a coluna ID seja uma IDENTIDADE, portanto, deve se correlacionar bem com as partições de data. Enquanto isso está acontecendo, se alguém quiser pesquisar esses dados antigos, poderá fazê-lo a partir desta nova tabela particionada.
Eventualmente, você chegará a um estágio em que os únicos dados na tabela não particionada atual são para o intervalo atual de 3 meses ou em que ter dados divididos em duas tabelas está causando problemas reais aos usuários. Este é o momento de ter uma interrupção. Mova o último dos dados para a nova tabela particionada, elimine a tabela atual e renomeie a tabela particionada.
Você pode particionar na
DateCreated
coluna depois de torná-la um índice clusterizado. Mesmo se você conseguir torná-la uma chave de índice clusterizado, haverá um problema de desempenho - seus índices consumirão mais espaço (armazenamento/memória) do que antes (quando o ID INT foi clusterizado).Como INT tem 4 bytes e tem valores únicos, mas sua
DateCreated
coluna tem 8 bytes e provavelmente não é única, então um unificador adicional de 4 bytes será adicionado no topo, tornando o índice clusterizado mais pesado até certo ponto. Como a chave de índice clusterizado é adicionada a todos os índices não clusterizados, eles também se tornarão mais pesadosIsso depende inteiramente da potência do seu servidor
Não use partições. Use uma tabela histórica separada com a mesma estrutura. Crie um trabalho diário que mova (insira na tabela histórica / exclua na tabela pai) todas as linhas com mais de 3 anos, com base na
DateCreated
coluna.Mova não todas as linhas de uma vez (com mais de 3 anos) diariamente, mas em blocos de N linhas com intervalo de X segundos (ou ms) entre os blocos. N é um número de linhas por 1 exclusão de bloco. Certifique-se de que N não causará o escalonamento de bloqueio de tabela exclusivo na tabela pai (descubra o número experimentalmente)
Algo a não esquecer é que, com 1,2 bilhão de linhas, você está chegando mais perto do que pensa de atingir o teto para o tipo de dados int. Portanto, acho que sua alteração também deve planejar resolver essa limitação, provavelmente alterando para um Bigint.
Como apontado anteriormente, 70% dos seus dados são tão antigos que você não precisa mais retê-los, mas em vez de excluir 70% em lote, eu consideraria inserir / selecionar os 30% que você deseja reter e depois truncar a tabela original. Você pode copiar as linhas de volta ou renomear a tabela.
O ponto final é que sua coluna Id provavelmente é adicionada em ordem crescente, de modo que as linhas < id x sejam anteriores e > id x sejam posteriores para que você possa criar uma tabela de mapeamento que mapeie ids em datas e depois particione diretamente em id ?