Como acompanhamento desta pergunta sobre como aumentar o desempenho da consulta, gostaria de saber se existe uma maneira de tornar meu índice usado por padrão.
Esta consulta é executada em cerca de 2,5 segundos:
SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';
Este é executado em cerca de 33ms:
SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31'
ORDER BY [DateEntered], [DeviceID];
Há um índice clusterizado no campo [ID] (pk) e há um índice não clusterizado em [DateEntered],[DeviceID]. A primeira consulta usa o índice clusterizado, a segunda consulta usa meu índice não clusterizado. Minha pergunta é em duas partes:
- Por que, uma vez que ambas as consultas têm uma cláusula WHERE no campo [DateEntered], o servidor usa o índice clusterizado na primeira, mas não na segunda?
- Como posso fazer com que o índice não clusterizado seja usado por padrão nesta consulta mesmo sem o orderby? (Ou por que eu não iria querer esse comportamento?)
Expressar a consulta usando uma sintaxe diferente às vezes pode ajudar a comunicar seu desejo de usar um índice não clusterizado para o otimizador. Você deve encontrar o formulário abaixo que lhe dá o plano que você deseja:
Compare esse plano com o produzido quando o índice não clusterizado é forçado com uma dica:
Os planos são essencialmente os mesmos (uma Key Lookup nada mais é do que uma busca no índice clusterizado). Ambos os formulários de plano realizarão apenas uma busca no índice não clusterizado e um máximo de 1.000 pesquisas no índice clusterizado.
A diferença importante está na posição do operador Top. Posicionado entre as duas buscas, o Top impede que o otimizador substitua as duas operações de busca por uma varredura logicamente equivalente do índice clusterizado. O otimizador funciona substituindo partes de um plano lógico por operações relacionais equivalentes. Top não é um operador relacional, portanto, a regravação impede a transformação em uma varredura de índice clusterizado. Se o otimizador pudesse reposicionar o operador Top, ele ainda preferiria a varredura em vez da busca + pesquisa devido ao modo como a estimativa de custo funciona.
Custeio de scans e buscas
Em um nível muito alto, o modelo de custo do otimizador para varreduras e buscas é bastante simples: ele estima que 320 buscas aleatórias custam o mesmo que ler 1.350 páginas em uma varredura. Isso provavelmente tem pouca semelhança com os recursos de hardware de qualquer sistema de E/S moderno em particular, mas funciona razoavelmente bem como um modelo prático.
O modelo também faz várias suposições simplificadoras, sendo a principal delas que cada consulta deve começar sem dados ou páginas de índice já em cache. A implicação é que cada E/S resultará em uma E/S física - embora isso raramente seja o caso na prática. Mesmo com um cache frio, a pré-busca e a leitura antecipada significam que as páginas necessárias provavelmente estarão na memória no momento em que o processador de consulta precisar delas.
Outra consideração é que a primeira solicitação de uma linha que não está na memória fará com que a página inteira seja buscada no disco. As solicitações subsequentes de linhas na mesma página provavelmente não incorrerão em uma E/S física. O modelo de custeio contém lógica para levar em conta efeitos como esse, mas não é perfeito.
Todas essas coisas (e mais) significam que o otimizador tende a mudar para uma varredura mais cedo do que provavelmente deveria. A E/S aleatória é apenas 'muito mais cara' do que a E/S 'sequencial' se resultar de uma operação física - acessar páginas na memória é realmente muito rápido. Mesmo quando uma leitura física é necessária, uma varredura pode não resultar em leituras sequenciais devido à fragmentação, e as buscas podem ser colocadas de forma que o padrão seja essencialmente sequencial. Acrescente a isso a característica de desempenho variável dos sistemas de E/S modernos (especialmente de estado sólido) e a coisa toda começa a parecer muito instável.
Metas de linha
A presença de um operador Top em um plano modifica a abordagem de custeio. O otimizador é inteligente o suficiente para saber que encontrar 1.000 linhas usando uma varredura provavelmente não exigirá a varredura de todo o índice clusterizado - ele pode parar assim que 1.000 linhas forem encontradas. Ele define um 'objetivo de linha' de 1.000 linhas no operador Top e usa informações estatísticas para voltar a partir daí para estimar quantas linhas ele espera precisar da fonte de linha (uma varredura neste caso). Eu escrevi sobre os detalhes deste cálculo aqui .
As imagens nesta resposta foram criadas usando o SQL Sentry Plan Explorer .
a primeira consulta faz uma varredura de tabela com base no limite que expliquei anteriormente em: É possível aumentar o desempenho da consulta em uma tabela estreita com milhões de linhas?
(provavelmente sua consulta sem a
TOP 1000cláusula retornará mais de 46k linhas. ou algumas entre 35k e 46k. (a área cinza ;-))a segunda consulta, deve ser solicitada. Como seu índice NC é ordenado na ordem desejada, é mais barato para o otimizador usar esse índice e, em seguida, fazer as pesquisas de favoritos para o índice clusterizado para obter as colunas ausentes em comparação com uma verificação de índice clusterizado e, em seguida, precisar para ordenar isso.
inverta a ordem das colunas na
ORDER BYcláusula e você volta para uma varredura de índice clusterizado, pois o NC INDEX é inútil.edit esqueci a resposta para sua segunda pergunta, por que você NÃO quer isso
Usar um índice não clusterizado não abrangente significa que um rowID é pesquisado no índice NC e, em seguida, as colunas ausentes devem ser pesquisadas no índice clusterizado (o índice clusterizado contém todas as colunas de uma tabela). As E/S para pesquisar as colunas ausentes no índice clusterizado são E/S aleatórios.
A chave aqui é ALEATÓRIO. porque para cada linha encontrada no índice NC, os métodos de acesso precisam procurar uma nova página no índice clusterizado. Isso é aleatório e, portanto, muito caro.
Agora, por outro lado, o otimizador também pode fazer uma varredura de índice clusterizado. Ele pode usar os mapas de alocação para pesquisar intervalos de varredura e apenas começar a ler o índice clusterizado em grandes partes. Isso é sequencial e muito mais barato. (desde que sua tabela não esteja fragmentada :-) ) A desvantagem é que o índice clusterizado INTEIRO precisa ser lido. Isso é ruim para o seu buffer e potencialmente uma grande quantidade de E/S. mas ainda assim, E/S sequenciais.
No seu caso, o otimizador decide algo entre 35k e 46k linhas, é mais barato fazer uma varredura completa de índice clusterizado. Sim, está errado. E em muitos casos com índices não agrupados estreitos com
WHEREcláusulas não seletivas ou tabelas grandes, isso dá errado. (Sua mesa é pior, porque também é uma mesa muito estreita.)Agora, adicionar o
ORDER BYtorna mais caro verificar o índice clusterizado completo e, em seguida, ordenar os resultados. Em vez disso, o otimizador assume que é mais barato usar o índice NC já ordenado e, em seguida, pagar a penalidade de E/S aleatória para as pesquisas de favoritos.Portanto, seu pedido é um tipo de solução perfeita de "dica de consulta". MAS, em um certo ponto, uma vez que os resultados da sua consulta são tão grandes, a penalidade para os IOs aleatórios de pesquisa de favoritos será tão grande que se tornará mais lenta. Suponho que o otimizador mudará os planos de volta para a verificação de índice clusterizado antes desse ponto, mas você nunca sabe com certeza.
No seu caso, desde que suas inserções sejam ordenadas por data de entrada, conforme discutido no chat e na pergunta anterior (veja o link), é melhor criar o índice clusterizado na coluna de data de entrada.