Agregado de fluxo para classificação?

Para responder sua primeira pergunta:

Por que há um Stream Aggregate na consulta quando não tenho um grupo por. Eu acho que tem algo a ver com a junção sendo mesclada e está fazendo uma classificação?

O SQL Server está efetivamente reescrevendo sua consulta. Esta consulta:

select Object1.Column1
FROM Object2 Object3 WITH (ROWLOCK)
INNER JOIN Object4 Object1 WITH (ROWLOCK) ON Object3.Column2 = Object1.Column1
WHERE Object3.Column2 in (SELECT Column2 FROM Object5)
or Object3.Column3 in (SELECT Column2 FROM Object5);

Também pode ser escrito assim:

select Object1.Column1
FROM
(
select Object3.Column2
FROM Object2 Object3 WITH (ROWLOCK)
WHERE Object3.Column2 in (SELECT Column2 FROM Object5)

UNION

select Object3.Column2
FROM Object2 Object3 WITH (ROWLOCK)
WHERE Object3.Column3 in (SELECT Column2 FROM Object5)
) Object3 
INNER JOIN Object4 Object1 WITH (ROWLOCK) ON Object3.Column2 = Object1.Column1;

É por isso que você tem dois métodos de acesso diferentes Object2no plano de consulta. A Merge Join (Concatenation)operação não é realmente uma junção de mesclagem. É apenas implementar um UNION ALLe combinar os resultados. O agregado de fluxo que você mencionou agrupa por Object3.Column2. Isso remove duplicatas do Merge Join (Concatenation)e classifica os dados para que possam ser usados ​​a seguir MERGE JOINpara Object4.

Respondendo sua segunda pergunta:

Em segundo lugar, e mais importante, por que uma estimativa de 13 entrando no agregado do fluxo resulta em uma estimativa de 24.595.900? Isso está causando o problema secundário do Object4 obter o índice clusterizado verificado em vez de um loop aninhado. Eu tive que dividir a consulta em duas consultas em vez de usar um OR e a junção se transforma em um loop aninhado e busca em Object4.

Parece um bug. Depois de alguma pesquisa, encontrei um artigo de Paul White explicando o assunto. O bug é relatado ao Connect se você quiser votar nele ou adicionar a si mesmo como afetado.

Resumindo, a estimativa de cardinalidade é 24595900 porque 27328800 (cardinalidade da tabela de Object2) * 0,9 = 24595900 após o arredondamento. Você está no SQL Server 2008, portanto, o cálculo do CE herdado é usado:

Um problema semelhante ocorre com o estimador de cardinalidade pré-2014, mas a estimativa final é fixada em 90% da entrada de semijunção estimada (por razões divertidas relacionadas a uma estimativa de predicado fixa invertida de 10% que é muito desvio para obter em).

Recomendo a leitura de todo o artigo. A questão é um pouco difícil de resumir, mas tentarei fazê-lo citando parte do texto perto do final:

O estimador de cardinalidade usa a calculadora Fixed Join com 100% de seletividade. Como consequência, a cardinalidade de saída estimada da semijunção é a mesma de sua entrada, o que significa que todas as 113.443 linhas da tabela de histórico devem se qualificar.

A natureza exata do bug é que o cálculo da seletividade de semijunção perde todos os predicados posicionados além de uma união na árvore de entrada. Na ilustração abaixo, a falta de predicados na própria semijunção significa que todas as linhas serão qualificadas; ele ignora o efeito dos predicados abaixo da concatenação (união todos).

Um problema semelhante ocorre com o estimador de cardinalidade pré-2014, mas a estimativa final é fixada em 90% da entrada de semijunção estimada (por razões divertidas relacionadas a uma estimativa de predicado fixa invertida de 10% que é muito desvio para obter em).

Dividir a consulta como você fez deve evitar o problema. Você também pode ter alguma sorte reescrevendo a consulta usando UNION(veja o Exemplo 4 no artigo).