É uma boa ideia particionar uma tabela (em MS SQL) com base na data, quando há um índice clusterizado presente no ID (INT)

Resposta curta: não , você não pode fazer isso. De acordo com os documentos ...

Ao particionar um índice clusterizado, a chave de clustering deve conter a coluna de particionamento.

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:

1. Você não pode particionar [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.
2. Ninguém pode responder isso para você. Você precisa testá-lo por si mesmo. Vários anos atrás, quando larguei e reconstruí um índice clusterizado em uma tabela OLTP de alto tráfego com uma ordem de magnitude menos espaço de dados e índice usado por GB do que você descreve, levou vários meses de benchmarking quais abordagens seriam mais seguras/mais rápidas e, em seguida, cerca de 10 horas de trabalho fora do expediente entre 2 DBAs trabalhando para concluir a reconstrução sem reduzir o tempo de inatividade. A adição de um esquema de particionamento não fazia parte desse projeto, mas não imagino que simplifique as coisas.
3. Se for realmente " uma tabela de log com a qual ninguém costuma se importar ", então talvez você possa fazer tudo isso sem se preocupar em manter a tabela online. Quase certamente você não conseguirá fazer isso sem criar pelo menos uma tabela extra (e provavelmente vários outros objetos) também. Convido você a experimentar várias técnicas em uma cópia restaurada do banco de dados. Suspeito que você acabará renomeando a tabela atual e recriando-a para que os logs possam continuar sendo gravados em uma nova tabela vazia (presumivelmente com o esquema de partição) enquanto arquiva as centenas de gigs de dados antigos usando qualquer abordagem que você escolher

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.

[Pergunta principal] Posso particionar esta tabela com base em "DateCreated" (coluna DATETIME, NOT NULL), sem causar problemas (logicamente / em termos de desempenho).

Você pode particionar na DateCreatedcoluna 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 DateCreatedcoluna 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 pesados

[É bom saber] Quanto tempo (eu entendo que depende do espaço do banco de dados/memória do servidor e outros detalhes, mas um número aproximado 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).

Isso depende inteiramente da potência do seu servidor

[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)?

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 DateCreatedcoluna.

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 ?