Eu vi este aviso nos planos de execução do SQL Server 2017:
Avisos: A operação causou E/S residual [sic]. O número real de linhas lidas foi (3.321.318), mas o número de linhas retornadas foi 40.
Aqui está um trecho do SQLSentry PlanExplorer:
Para melhorar o código, adicionei um índice não clusterizado, para que o SQL Server possa acessar as linhas relevantes. Funciona bem, mas normalmente haveria muitas colunas (grandes) para incluir no índice. Se parece com isso:
Se eu adicionar apenas o índice, sem incluir colunas, fica assim, se eu forçar o uso do índice:
Obviamente, o SQL Server acha que a pesquisa de chave é muito mais cara do que a E/S residual. Eu tenho uma configuração de teste sem muitos dados de teste (ainda), mas quando o código entra em produção, ele precisa trabalhar com muito mais dados, então tenho certeza de que algum tipo de índice NonClustered é necessário.
As pesquisas de chave são realmente tão caras , quando você executa em SSDs, que eu tenho que criar índices completos (com muitas colunas de inclusão)?
Plano de execução: https://www.brentozar.com/pastetheplan/?id=SJtiRte2X Faz parte de um procedimento armazenado longo. Procure IX_BatchNo_DeviceNo_CreatedUTC.
O modelo de custo utilizado pelo otimizador é exatamente isso: um modelo . Geralmente, produz bons resultados em uma ampla variedade de cargas de trabalho, em uma ampla variedade de designs de banco de dados, em uma ampla variedade de hardware.
Em geral, você não deve presumir que as estimativas de custo individuais se correlacionarão fortemente com o desempenho do tempo de execução em uma configuração de hardware específica. O objetivo do cálculo de custos é permitir que o otimizador faça uma escolha educada entre alternativas físicas candidatas para a mesma operação lógica.
Quando você realmente entra nos detalhes, um profissional de banco de dados habilidoso (com tempo de sobra para ajustar uma consulta importante) geralmente pode se sair melhor. Nessa medida, você pode pensar na seleção do plano do otimizador como um bom ponto de partida. Na maioria dos casos, esse ponto inicial também será o ponto final, pois a solução encontrada é boa o suficiente .
Na minha experiência (e opinião), o otimizador de consulta do SQL Server custa pesquisas mais altas do que eu preferiria. Isso é em grande parte uma ressaca dos dias em que a E/S física aleatória era muito mais cara em comparação com o acesso sequencial do que geralmente é o caso hoje.
Ainda assim, as pesquisas podem ser caras mesmo em SSDs, ou mesmo ao ler exclusivamente da memória. Atravessar estruturas b-tree não é de graça. Obviamente, o custo aumenta à medida que você faz mais deles.
As colunas incluídas são ótimas para cargas de trabalho OLTP de leitura intensa, em que a compensação entre o uso do espaço de índice e o custo de atualização versus o desempenho de leitura em tempo de execução faz sentido. Há também uma compensação a considerar em relação à estabilidade do plano . Um índice de cobertura total evita a questão de quando exatamente o modelo de custo do otimizador pode fazer a transição de uma alternativa para outra.
Só você pode decidir se as trocas valem a pena no seu caso. Teste ambas as alternativas em uma amostra de dados representativa e faça uma escolha informada.
Em um comentário de pergunta você adicionou:
Não, o otimizador considera o custo de E/S residual. De fato, no que diz respeito ao otimizador, predicados não SARGable são avaliados em um Filtro separado. Esse filtro é inserido na busca ou varredura como um resíduo durante as reescritas de pós-otimização .