Por que o SQL Server faz muitas pesquisas de chaves clusterizadas desnecessárias neste SELECT - OFFSET - FETCH?

É por causa da ordem das operações em que as cláusulas de sua consulta são executadas. A WHEREcláusula ocorre antes de suas cláusulas OFFSETe FETCH. É por isso que não apenas existem cerca de 10.000 (9.000 em seu primeiro exemplo) pesquisas de chave , mas há tantas buscas de índice . Esta é a filtragem que ocorre como resultado do seu predicado target_user_idna sua WHEREcláusula. Os Planos de Execução são lidos da direita para a esquerda por ordem dos eventos, portanto, se você seguir o plano a partir do índice busca e seguir seu caminho à esquerda, verá o operador Top que é o que representa suas cláusulas OFFSET/ FETCHe, portanto, vem após sua WHEREcláusula.

Simplificando, o SQL Server primeiro precisa localizar as linhas com base nos filtros que você aplica por meio de seus predicados (nas cláusulas WHERE, HAVINGe ). JOINEntão, quando encontrar as linhas corretas, poderá aplicar suas cláusulas OFFSET/ . FETCHSe não obtivesse todas as linhas com base em seus filtros primeiro, não saberia quais linhas precisaria OFFSET/ FETCH.

O SQL Server Engine poderia ter sido programado um pouco melhor logicamente nesse caso? Possivelmente. Eles poderiam ter projetado o mecanismo de uma maneira que percebesse que uma operação Top iria eventualmente ocorrer nos dados pesquisados ​​pelo índice e aplicá-los antes que as pesquisas de chave ocorressem para pelo menos minimizar a quantidade de pesquisas de chave , mas eu imagino que seria apenas ajudar o desempenho em certos casos e tornar as coisas programaticamente muito mais complicadas que podem não valer a pena. Por exemplo, as pesquisas de chave estão acontecendo em paralelo à operação de busca de índice (portanto, elas estão igualmente à direita nas etapas do plano). Se o topooperação ocorresse antes das pesquisas de chave para reduzir os dados, as pesquisas de chave de linhas restantes teriam que acontecer em série após a operação Top , consequentemente em série também após a operação de busca de índice , o que seria pior em termos de desempenho em alguns casos.

Em relação à sua segunda pergunta sobre o que você experimentou com o plano de execução mudando de buscas de índice para varreduras de índice clusterizado de volta para buscas de índice novamente, isso é conhecido como Ponto de Virada . Basicamente, o Query Optimizer analisa sua consulta e calcula rapidamente o custo de cada operação entre uma série de planos de execução diferentes que ele pode usar para buscar seus dados, em parte com base nas estatísticas em cache de seus dados que o SQL Server mantém. A soma total desses custos permite que o Query Optimizer escolha o plano de execução com o menor custo (idealmente o plano de execução mais rápido).

Essas estatísticas armazenadas em cache são baseadas em quantas linhas existem para cada valor em cada coluna do total de linhas na tabela e são usadas para fazer estimativas de cardinalidade em cada operação que precisa ocorrer. Em resumo, a estimativa de cardinalidade é o número de linhas que o SQL Server acredita que uma determinada operação retornará. Por exemplo, sua WHEREcláusula em seu primeiro exemplo retorna aproximadamente 9.000 linhas e o SQL Server Engine provavelmente estimou a cardinalidade próxima a esse número, o que resultou no custo de uma operação de busca de índice suficiente para que uma busca de índice fosse escolhida.

Quando você disse explicitamente ao SQL Server para retornar 10.000 linhas, ele provavelmente acionou o Tipping Point , o que o fez pensar que a quantidade de linhas que ele precisa retornar será mais eficiente com uma operação de verificação de índice , porque a cardinalidade das linhas é alta o suficiente que o SQL Engine acha que uma varredura provavelmente encontrará linhas mais contíguas com eficiência, em vez de fazer 10.000 buscas de índice . (Para encurtar a história, o Optimizer não é perfeito, especialmente quando dança tão perto do ponto de inflexão , e uma busca de índice provavelmente ainda é mais eficiente aqui.)

Por que quando você adicionou outra coluna não indexada à sua SELECTlista faz com que o Otimizador de Consultas escolha um plano que não esteja acima do ponto de inflexão , eu não poderia te dizer com certeza (especialmente sem o plano de execução real na minha frente) porque é bastante complicado o que está acontecendo nos bastidores, mas, de modo geral, as estimativas feitas, com base nas estatísticas em cache de seus dados naquele momento, resultaram no cálculo de custos que não estavam mais acima do ponto de inflexão e resultaram na escolha um plano de execução que aproveita as buscas de índice .

Concordo que o otimizador poderia ser mais inteligente sobre onde a pesquisa de chave deve ocorrer no plano com OFFSETe FETCH.

Como solução alternativa, você pode usar um CTE como abaixo.

WITH Top10Keys AS (
    SELECT
        ID, date_created
    FROM
        [dbo].[MP_Notification_Audit]
    WHERE
        [target_user_id] = 100017
    ORDER BY
        [date_created] ASC
    OFFSET 9200 ROWS
    FETCH NEXT 10 ROWS ONLY
)
SELECT
    [target_user_id], [patient_id]
FROM Top10Keys
JOIN [dbo].[MP_Notification_Audit] AS AdditionalData ON AdditionalData.id = Top10Keys.id
ORDER BY Top10Keys.date_created;

A pesquisa de chave de loop aninhada é feita após o TOPoperador no plano .