O que há de errado com valores diretos sendo passados ​​como parte do predicado?

A diferença entre variáveis ​​e parâmetros

O otimizador usa o vetor de densidade estatística ao calcular estimativas para variáveis.

Quando valores "diretos" ou "estáticos" são incorporados na consulta diretamente, o histograma de estatísticas é usado. É por isso que você obtém estimativas diferentes e, portanto, planos diferentes.

Aqui está meu plano estimado: https://www.brentozar.com/pastetheplan/?id=SJCduTuKN

Na minha cópia de 2010 do banco de dados SO, a densidade da coluna OwnerUserId é 0,000003807058. Multiplicando isso por 3.744.192 linhas = 14,2544 linhas. Que é exatamente o número de linhas estimado para sair de IX_Posts_OwnerUserId.

Você pode obter essas informações sobre as estatísticas desse índice executando este comando DBCC:

DBCC SHOW_STATISTICS('dbo.Posts', 'IX_Posts_OwnerUserId');

Aqui está a saída (abreviada):

Name                    Updated             Rows
IX_Posts_OwnerUserId    Apr  8 2019  8:33AM 3744192

All density     Average Length  Columns
3.807058E-06    4               OwnerUserId

Como PostTypeId também faz parte da cláusula WHERE, as estatísticas também são geradas automaticamente para essa coluna. Esse vetor de densidade resulta em 0,25 x 3.744.192 linhas = 936.048 linhas.

DBCC SHOW_STATISTICS('dbo.Posts', '_WA_Sys_00000010_0519C6AF');

E a saída:

Name                        Updated             Rows
_WA_Sys_00000010_0519C6AF   Apr  8 2019  9:04AM 3744192

All density Average Length  Columns
0.25        4               PostTypeId

Como esse é um predicado "E", a estimativa usa o menor dos dois.

Quando você usa valores estáticos em vez de variáveis, ele usa o histograma de estatísticas. Isso está no terceiro conjunto de resultados desse comando SHOW_STATISTICS. Para a chave que você está usando, aqui está a entrada do histograma:

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
22656           13040       11371   305                 42.7541

É daí que vem a estimativa de 11.371 no plano de "valores estáticos".

O histograma pode ser uma estimativa melhor na maioria das vezes, pois lida um pouco melhor com casos extremos - já que geralmente haverá alguns valores discrepantes em uma tabela grande como essa.

Diferenças de custo

Nesse caso específico, o histograma produz uma estimativa exatamente correta. O custo do plano produzido é (corretamente) superior ao que utiliza o vetor densidade, pois tem que processar muito mais linhas.

O plano de "menor custo" pensa que 14 linhas serão produzidas por essa busca, quando na verdade 11.371 linhas são produzidas.

Leituras Lógicas

As leituras lógicas são um pouco maiores no plano paralelo devido à pré- busca de loops aninhados . Não parece fazer uma grande diferença na minha máquina - o tempo decorrido para as consultas estava dentro de 10 ms uma da outra.

O paralelismo na verdade não ajuda em nada, já que todas as linhas terminam em um thread (na minha máquina de qualquer maneira). Adicionar OPTION (MAXDOP 1)ajuda no tempo de execução, mas não remove as leituras lógicas extras.

Uma solução potencial para o problema de "leituras extras" para essa consulta seria evitar totalmente a pesquisa de chave adicionando PostTypeId como uma coluna incluída:

CREATE INDEX IX_Posts_OwnerUserId ON dbo.Posts (OwnerUserId) 
INCLUDE (PostTypeId) 
WITH (DROP_EXISTING = ON);