Aqui está minha consulta (é uma consulta do Microsoft Axapta):
(@P1 bigint)
SELECT TOP 1 T1.JOURNALNUM,T1.LINENUM,T1.ACCOUNTTYPE,T1.COMPANY,T1.TXT,
T1.AMOUNTCURDEBIT,T1.CURRENCYCODE,T1.EXCHRATE,T1.TAXGROUP,
T1.CASHDISCPERCENT,T1.QTY,T1.BANKNEGINSTRECIPIENTNAME,
-- *Snipped lots of columns in T1* --
T1.MODIFIEDDATETIME,T1.RECVERSION,T1.PARTITION,T1.RECID
FROM LEDGERJOURNALTRANS T1
WHERE (((PARTITION=123123123) AND (DATAAREAID=N'test')) AND (REVRECID=@P1))
Plano de execução atual:
Na verdade, existe um índice apropriado na tabela.
Colunas de índice: (PARTITION,DATAAREAID,REVRECID)
Fragmentação:
Eu tentei força de índice. Este plano de execução (busca de índice + pesquisa de chave) é mais rápido que o plano posterior (varredura de índice):
E tentei:
ATUALIZAR ESTATÍSTICAS
Mudou a ordem das colunas, por exemplo (REVRECID,PARTITION,DATAAREAID)
Por que o MSSQL escolhe o índice clusterizado?
Estimativas, uma enorme quantidade de colunas selecionadas e pushdown de predicado
As estimativas da consulta não levam em conta o predicado residual na varredura de um custo mais alto do que a pesquisa de busca + chave para obter todas essas colunas extras do índice clusterizado. Isso resulta na escolha da varredura de índice clusterizado + predicado residual em vez da busca de índice.
Essas estimativas de empilhamento de predicado foram aprimoradas no SQL Server 2016 SP1
Atualização para melhorar o diagnóstico para planos de execução de consulta que envolvem empilhamento de predicado residual no SQL Server 2016
Isso adiciona o
EstimatedRowsRead=""XML ao plano de consulta, no seu caso, isso seria próximo ou corresponderia ao predicado residual se a varredura for escolhida.Isso deve resolver seu problema
Exemplo de predicado residual
Lendo 1,2 milhões de linhas para retornar 0
Consulta de verificação de índice Custo total estimado
Consulta de busca de índice Custo total estimado
O que é maior do que as estimativas de varredura de índice devido à não contabilização do predicado residual, e é por isso que o plano de menor desempenho foi escolhido.
A principal solução
A principal solução seria atualizar para pelo menos o SP1 para adicionar o:
Atualização para melhorar o diagnóstico para planos de execução de consulta que envolvem empilhamento de predicado residual no SQL Server 2016
Você deve corrigir mais cedo e com mais frequência, já que o SP2 CU6 foi lançado em 19 de março de 2019 , essa seria uma escolha muito melhor.
Outra observação lateral, o SP1 para SQL Server 2016 adiciona muitos recursos adicionais , como OLTP na memória, compactação, índices Columnstore, ....
Outras soluções alternativas que podem ou não valer a pena mencionar
OPTION(QUERYTRACEON 4138)(talvez)WITH(INDEX))dicaComparação com o SQL Server 2016 SP1
ao executar uma consulta parecida com a sua, forçando o índice clusterizado a ser usado em uma versão SQL2016 SP1:
O custo de subárvore estimado é muito maior.
Onde seu custo de subárvore estimado para a varredura de índice clusterizado
É baixo
Com a principal diferença sendo
mostrado ao executar a consulta no SQL 2016 com SP1 aplicado.
E ao testar com o índice NC especificado
o EstimatedTotalSubtreeCost para a busca de índice (não total para o plano inteiro) também é baixo:
e o custo total estimado da subárvore para minha consulta de teste é muito próximo do seu