Points
------------------
PK QuestionId int (+30.000.000 distinct values)
PK EventId int (large batches where 80.000 rows have the same EventId)
Value smallint
A tabela tem cerca de 40 milhões de linhas e apresenta problemas de desempenho.
Existem duas dúvidas principais:
Em QuestionId :
- cerca de 30 milhões de
QuestionIdvalores diferentes (muita variação) - muitas consultas durante os horários de pico (vários milhares por minuto)
Em EventId :
- haverá atualizações em lote de +150.000 linhas
where EventId=Xpara definirValue=NULLdurante os momentos de muito movimento.
Portanto, meu primeiro pensamento para obter o melhor desempenho foi fazer EventId,QuestionIdo ClusteredIndex para que a atualização em lote possa encontrar facilmente todos os EventId próximos uns dos outros e atualizar diretamente os valores.
Meu segundo pensamento foi adicionar um índice com uma QuestionIdcoluna incluída Value para que ele possa ler o valor diretamente do índice ( EventIdé irrelevante neste caso).
Mas então pensei: o Índice Agrupado importaria? Como o Valor da coluna Incluído do Índice também precisa ser atualizado durante a atualização do lote.
- Embora não negocie o desempenho da consulta - é possível obter a atualização do lote rapidamente (alguns segundos) ou tenho que aceitar que esse processo sempre será lento sem atualizar o hardware.
- Quaisquer outros pensamentos qual seria a melhor maneira de definir o ClusteredIndex / Indexes?
Eu sei que, em teoria, eu deveria testar tudo e medi-lo, mas o site está ativo e é muito usado.
Sou um desenvolvedor solo e não tenho recursos para contratar alguém. Quaisquer suposições e pensamentos estimados sobre isso seriam realmente úteis, pois isso já me dá a direção certa!
Portanto, se seu caminho de acesso principal for por pergunta , o índice clusterizado exclusivo que fizer mais sentido será
(QuestionId, EventId).Adicionar um segundo índice
EventIdpode não ser útil, pois o índice pode não ser seletivo o suficiente e o mecanismo de consulta decidirá que é mais rápido ler a tabela inteira em vez de fazer muito trabalho para ler uma grande parte dela.Como alternativa, se você sempre consulta com base total ou parcialmente em
EventId, o índice clusterizado de(EventId,Questionid)é mais adequado e tem o benefício adicional de fazer suas atualizações baseadas emEventIdexigir menos E/S para serem concluídas.Eu não incluiria
Valueem um índice adicional, pois isso basicamente copiará a tabela inteira (apenas agrupada em colunas diferentes) e suas atualizações levarão ainda mais tempo, poisValuedevem ser mantidas sincronizadas no índice clusterizado e no índice adicional.Em um certo ponto, não há almoço grátis, e a solução correta é provavelmente escolher o índice clusterizado com a coluna principal que suporta a maioria dos casos de uso e, em seguida, adicionar RAM/CPU/armazenamento mais rápido para lidar com casos em que a tabela inteira (ou grande pedaços dele) devem ser lidos ou gravados. Com 40 milhões de linhas e uma tabela tão estreita, não consigo imaginar isso sendo algo que mais RAM não resolveria.
Dependendo da sua edição do SQL Server, você também pode verificar se a compactação de página reduziria significativamente o tamanho da tabela, pois isso reduziria o número de leituras/gravações no disco (a sobrecarga adicional da CPU é compensada por menos operações de disco). Meu palpite no seu caso é que não, mas é um trabalho olhando.
Somente se o uso principal for retornar específico
QuestionIdssem considerarEventId. Você pode experimentar o índice adicional emEventId, mas pode descobrir que ele não está sendo usado com frequência (ou de todo) para atualizações (ou as atualizações ainda demoram mais do que você gostaria), dependendo de como os EventIds são distribuídos por meio de seus dados em relação paraQuestionId.Você também precisa determinar o que é mais importante para você em geral - selecionar o desempenho ou atualizar o desempenho. Se a atualização for o ponto problemático,
(EventId,QuestionId)sem dúvida será a melhor escolha. Dado o número de valores exclusivos paraQuestionId, adicionar um índice nessa coluna pode ser útil para oSELECTdesempenho, mas isso dependerá de comoQuestionIdé distribuído e de quantos você está procurando por vez.Manter as estatísticas atualizadas será crucial em ambos os casos.
Um exemplo extremamente simples (por uma questão de completude):
Digamos que temos um DBMS que mantém um índice clusterizado e armazena 4 linhas por página. Temos uma tabela com uma chave primária de
(QuestionId, EventId)e uma coluna adicional,Value.Se criarmos o índice clusterizado como
(QuestionId, EventId), os dados em nosso DBMS imaginário são (grosseiramente falando) armazenados assim:Portanto, se eu precisar realizar uma operação baseada em
QuestionId, o mecanismo não precisará ler mais páginas do que o necessário.No entanto, se eu precisar realizar uma operação baseada em
EventId, terei que ler a tabela inteira (varredura de índice clusterizado), a menos que eu adicione um índice adicional, que ficará assim (e exigirá quatro páginas):Este índice pode ser seletivo para alguns
EventIds, mas no caso extremo (EventId = 2) a tabela inteira ainda precisaria ser lida, e para alguns casos (EventId = 6) nosso otimizador pode decidir que pesquisar o índice e ler a tabela é mais caro do que apenas ler o índice inteiro tabela.Se, em vez disso, agruparmos em
EventId, QuestionIdnossa tabela se parece com isso:Qualquer operação baseada em
EventIdlerá apenas as partes necessárias da tabela e, como nossa primeira instância, qualquer operação baseada emQuestionIdexigirá uma varredura sem um índice adicional. Se criarmos um índice emQuestionId, o índice será:Assim como no primeiro exemplo, este índice será mais útil para algumas questões e menos útil para outras. Pois
QuestionId = 1o otimizador provavelmente dirá que o custo de ler metade do índice e procurar metade da tabela não vale o custo e apenas lerá a tabela inteira em vez de utilizar o índice.Se incluirmos
Valueno índice, agora temos que alterar a tabela E o índice dentro da mesma transação. Na melhor das hipóteses, isso duplica o trabalho para qualquer operação. Na pior das hipóteses, isso requer que toda a tabela ou índice (que é apenas uma cópia da tabela) seja lido e possivelmente bloqueado.Agora, com seus dados reais, é possível adicionar um índice adicional
QuestionIdouEventIdfornecer muitos benefícios. Mas não vai resolver todos os problemas, e pode não valer a pena a sobrecarga imposta em inserções/atualizações/exclusões.