Cenário
Eu tenho um produto de software de middleware chamado Pro2SQL da Progress, copiando dados de um banco de dados Progress para um SQL Server em tempo real. Pro2SQL gerencia o esquema do SQL Server por meio do middleware. Os únicos índices criados no SQL Server pelo Pro2SQL são índices de ID de linha exclusivos e não agrupados. Posso ignorar completamente esse ID de linha em minhas consultas, pois ele é usado exclusivamente para simultaneidade Pro2SQL.
Algumas das tabelas contêm mais de 100 milhões de linhas, então preciso criar índices. Em cada tabela copiada do Progress para o SQL Server, existe um campo “domínio”. Este campo possui um de dois valores de três caracteres cada. Vamos chamá-los de 'ABC' ou 'XYZ'. No lado do banco de dados Progress, este domaincampo faz parte do índice.
Pergunta
Adicionar o domaincampo como parte de um índice de vários campos no lado do SQL Server agrega algum valor ou pode piorar as coisas? Por exemplo, posso ter o seguinte índice não clusterizado de vários campos em uma tabela (observe que esses campos são o que torna as linhas na tabela do banco de dados Progress exclusivas):
isb_eu_nbr(muito seletivo)isb_part(muito seletivo)isb_serial(muito seletivo)isb_ref(moderadamente seletivo)isb_domain(extremamente não seletivo, apenas 'ABC' ou 'XYZ', a fonte da minha pergunta)
Esta tabela terá cerca de 500.000+ linhas.
Uso
Todas as consultas serão consultas somente leitura. Não haverá inserções, atualizações ou exclusões. Eles são tratados pelo middleware Pro2SQL. Em todas as minhas consultas, terei que adicionar na WHEREcláusula o seguinte:
/* WHERE <tableAbbr>_domain = 'ABC' */
/* Example */
WHERE
/* Conditions 1 through N */
AND isb_domain = 'ABC'
/* Another Example, with JOINed tables */
WHERE
/* Conditions 1 through N */
AND isb_domain = 'ABC'
AND ls_domain = 'ABC'
AND pt_domain = 'ABC'
outras considerações
O fornecedor, Progress, indica que, como há atualizações em tempo real acontecendo constantemente ao longo do dia útil, todas as consultas devem ser sem bloqueio. Então eles realmente sugerem(!!!) NOLOCK. Habilitamos SNAPSHOTno banco de dados, então todas as consultas conectadas diretamente ao servidor serão executadas com SET TRANSATION ISOLATION LEVEL SNAPSHOT, e todas as consultas executadas através de servidores vinculados serão executadas com SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED(aceitando os efeitos colaterais).
ATUALIZADA
Descobri que posso criar índices agrupados e não exclusivos nas tabelas do SQL Server. Além disso, com as sugestões abaixo, posso criar índices filtrados não exclusivos e não agrupados em cluster, onde o filtro estaria no campo <abc>_domainde cada tabela. Então minha pergunta se transformou em:
Devo criar índices agrupados não exclusivos que correspondam aos campos do banco de dados Progress quanto à exclusividade, menos os <abc>_domaincampos e, em seguida, criar índices filtrados não exclusivos e não agrupados que incluam o <abc>_domaincampo em cada tabela?
Eu entendo que um sim/não rígido nem sempre é viável sem realmente ver o banco de dados e os dados. Ainda farei testes de desempenho. Estou procurando mais uma resposta "Eu começaria com ...".
em geral
Se as consultas pesquisarem regularmente em todas essas colunas, então sim, seria benéfico tê-las todas no mesmo índice, para que todos os predicados de pesquisa pudessem ser aplicados o mais cedo possível no plano.
Se você criasse um índice não clusterizado que não tivesse algumas das colunas pesquisadas, o otimizador teria uma escolha entre:
A pesquisa pode ser usada para recuperar colunas necessárias em outras partes da sua consulta, como a lista de seleção, ou para aplicar filtragem adicional de colunas na sua cláusula where.
O otimizador é um pouco inconstante nessas escolhas baseadas em custos, e os planos podem mudar ou ser reutilizados de maneira prejudicial. Como este é um número bastante pequeno de colunas, eu provavelmente seguiria o caminho mais seguro e teria todas elas disponíveis em um único índice para essas pesquisas.
É claro que #1 , quaisquer colunas adicionais na lista de seleção também podem precisar ser contabilizadas como colunas incluídas (não-chave) em seu índice não clusterizado, mas não há nenhum exemplo de consulta fornecido para fazer essa determinação.
Claro, nº 2 , se suas consultas de pesquisa usarem as três colunas seletivas iniciais para produzir pequenos conjuntos de resultados confiáveis, você terá muito menos preocupações de longo prazo com pesquisas ou reutilização de planos.
Claro #3 , só porque os valores são seletivos não significa que eles serão pesquisados de forma seletiva ou combinações. Por exemplo, você poderia ter um conjunto totalmente exclusivo de valores de data e hora em uma tabela, mas se alguém pesquisar de 1900-01-01 a 9999-12-31, esse não será um intervalo muito seletivo.
Sem índices clusterizados?... já começou mal.
É difícil dizer com certeza sem ver as consultas reais conforme elas são executadas agora e seus planos de execução antes e depois da mudança. Basicamente é preciso testar para descobrir.
Uma coisa que eu consideraria, já que
isb_domainnão é muito seletivo, se você sempre se preocupa apenas comABCe nuncaXYZ, tente criar índices filtrados . Isso teria umaWHEREcláusula no final de suas definições comoCREATE NONCLUSTERED INDEX IX_YourIndexName ON TheTable (WhateverColumnsWereAlreadyBeingIndexed) WHERE isb_domain = 'ABC';. Isso oferece alguns benefícios:Ele evita que você altere os índices que o aplicativo do fornecedor está gerando, que potencialmente serão substituídos ou causarão problemas no aplicativo do fornecedor, desacoplando-o em um índice separado.
Não acaba sendo um índice completamente redundante do índice gerado pelo aplicativo do fornecedor, facilitando um pouco a manutenção do sistema de banco de dados, já que inclui apenas metade dos dados.
Pode ser um candidato para suas consultas e melhorar o desempenho de E/S ao ter que verificar ou buscar menos registros.