Eu tenho uma tabela relativamente grande (para mim) com 40 milhões de linhas que deve crescer para 80 a 120 milhões de linhas em um período de duas semanas (durante uma campanha).
Tip
--------------
Id int (clustered index)
UserId int
TipIndex smallint
Value binary(8)
LastChanged datetime2(3)
- cada usuário tem entre 1 a 400 pontas que serão inseridas aleatoriamente durante este período
- UserId + TipIndex é único
- Eu nunca consulto dicas diretamente na chave de identidade (id)
- 99% das vezes que consulto no UserId
- Eu preciso de todas as colunas
- Costumo consultar 1 UserId (por visualização de página), às vezes em lotes de 10.000s para estatísticas
- este é um site de alto tráfego durante esse período e deve ser capaz de lidar com 30.000 consultas no UserId por minuto
- Id é no momento meu Clustered Index, porque li que leva à menor fragmentação.
Então estou hospedando no SQL Azure e o Azure já recomendo adicionar um Index com colunas inclusas. Sempre hesitei em usar UserId,TipIndex como um índice clusterizado, pois as dicas serão adicionadas aleatoriamente. O que significa que tenho medo de grandes problemas de fragmentação etc.
Minhas perguntas:
- Um índice com colunas incluídas não tem exatamente o mesmo problema?
- Uma tabela com colunas incluídas não é exatamente o mesmo que uma "tabela sombra" com os mesmos problemas de fragmentação?
- Devo migrar para usar UserId, TipIndex como ClusteredIndex em vez de Id?
- Como evitar a fragmentação?
Eu sei que no final a resposta é sempre "depende" ou que eu deveria medir. Mas como sou um desenvolvedor solo e não tenho muitos recursos, espero alguém com mais experiência que tenha um pressentimento para isso, então minhas primeiras tentativas têm uma chance maior de ir na direção certa.
Suas perguntas:
Um índice com colunas incluídas não tem exatamente o mesmo problema?
Sim.
Uma tabela com colunas incluídas não é exatamente o mesmo que uma "tabela sombra" com os mesmos problemas de fragmentação?
Sim
Devo migrar para usar UserId, TipIndex como ClusteredIndex em vez de Id?
Eu faria, sim.
Como evitar a fragmentação?
Existem alguns tipos diferentes de fragmentação a serem considerados. Uma é quando você tem apenas parte de suas páginas sendo usadas porque você teve divisões de página. Se você tiver muitas inserções, isso acontecerá. Não se estresse muito. A outra é quando você tem páginas em que a página subsequente está em uma extensão diferente. Novamente, eu não me preocuparia muito. Se seus dados estiverem principalmente no cache de buffer, não importa se eles se movem entre as extensões.
Então... não se preocupe muito com isso. Mas não se preocupe em ter uma cópia completa dos dados de uma forma que você não vai realmente consultá-los.
Parece que você está muito preocupado com a fragmentação, desde que você continue atualizando as estatísticas regularmente, a fragmentação não deve incomodá-lo muito para o desempenho. Você pode ler mais detalhes sobre isso em um vídeo compartilhado pelo Sr. Brent Ozar e também em outra página aqui . Deixe-me tentar responder sua pergunta uma a uma:
Índice com colunas no include ou seja uma coluna chave, não difere muito. As colunas-chave farão parte da árvore B, enquanto as colunas de inclusão não são, no entanto, quando você executa qualquer operação de inserção/atualização/exclusão, isso terá a mesma despesa, pois essas colunas precisam ser atualizadas/inseridas/excluídas.
Não tenho muita certeza do que você quis dizer com tabela de sombra, se você quis dizer com tabela base aqui, sim, você teria o mesmo problema no que diz respeito à fragmentação.
De acordo com sua declaração - "99% das vezes que consulto no UserId", este é um bom candidato para coluna clusterizada de chave primária. Como você não usará a coluna Id com muita frequência, não vejo nenhum problema em usar a chave agrupada primária composta na forma de UserId e TipIndex. Em termos de tamanho do índice, é tão bom quanto Id(int --> 4 byte) mais uma coluna adicional do tipo tinyint(1 byte).
Por favor, entenda que a chave clusterizada nada mais é do que a ordem dos dados armazenados logicamente e não tem nenhuma existência física ao contrário da chave não clusterizada.
Eu diria que a atualização das estatísticas deve ser considerada com prioridade do que a fragmentação. Você pode usar o script de manutenção usado por muitos DBAs em todo o mundo de Ola Hallengren. Você pode agendá-lo semanalmente ou quinzenalmente conforme sua necessidade.
Espero que isto ajude.