Plano Real do SQL Server. Estimativas de linha incorretas

A pergunta é difícil de responder porque a consulta não corresponde ao plano fornecido e a consulta usada para gerar o plano não tem sentido:

SELECT TOP 50 Id AS [User Link],
(
 SELECT top 1 1
 FROM Posts
 WHERE PostTypeId = 1 AND 
    LastEditorUserId = Users.Id AND 
    OwnerUserId != Users.Id
) AS QuestionEdits,
(
 SELECT top 1 1
 FROM Posts
 WHERE PostTypeId = 2 AND 
    LastEditorUserId = Users.Id AND 
    OwnerUserId != Users.Id
) AS AnswerEdits,
(
 SELECT top 1 1
 FROM Posts
 WHERE LastEditorUserId = Users.Id AND 
    OwnerUserId != Users.Id
) AS TotalEdits
FROM Users
ORDER BY TotalEdits DESC

No entanto:

1. A que se refere o operador 'Verificação constante'?

Um Constant Scan lê a partir de uma tabela na memória de valores constantes. Pode ter uma ou mais linhas e zero ou mais colunas. Sim, um Constant Scan pode ter zero colunas; é frequentemente introduzido pelo otimizador simplesmente como uma fonte de linhas (vazias), às quais as colunas podem ser adicionadas por operadores posteriores (normalmente Compute Scalars).

Em grande parte, Constant Scans geralmente são artefatos puramente arquitetônicos necessários para a operação correta da consulta ou como parte de uma otimização.

Quando Constant Scans e Compute Scalars ficam abaixo de um Merge Interval, isso é um sinal claro de que esses elementos foram introduzidos após a otimização para calcular e recolher vários intervalos lógicos em um único intervalo adequado para conduzir uma Index Seek nesse intervalo.

Neste caso em particular, as Constant Scans representam a expansão de OwnerUserId <> Users.Id:

• OwnerUserId < Users.Id;OR
• OwnerUserId > Users.Id

A reescrita pós-otimização na subárvore Merge Interval é acionada por esse x < y OR x > ypredicado. O mecanismo testa os intervalos para sobreposições e reduz o "intervalo mesclado" resultante em pontos inicial e final que podem ser usados ​​com uma busca de índice.

O exemplo mínimo a seguir mostra o SQL Server reescrevendo o predicado dessa maneira para que uma busca de índice possa ser gerada:

CREATE TABLE #U (u integer NOT NULL);
CREATE TABLE #P (u integer NOT NULL PRIMARY KEY);

INSERT #U VALUES (1);
INSERT #P VALUES (0), (2);

SELECT 
    U.u,
    (
        SELECT TOP (1) 1
        FROM #P AS P
            WITH (FORCESEEK)
        WHERE P.u <> U.u
    )
FROM #U AS U;

DROP TABLE #U, #P

A Busca de Índice Agrupado resultante tem as propriedades:

O resultado final é varrer o índice para frente até o ponto final (excluindo o valor de destino) e reiniciar a varredura logo após o valor de destino até o final do índice. [Expr1011]e[Expr1012] são os pontos inicial e final calculados do intervalo excluído.

Você pode ler mais sobre isso no meu artigo Buscas dinâmicas e conversões implícitas ocultas .

2. Por que as estimativas de linha podem estar incorretas nos blocos a seguir

O SSMS mostra estimativas no lado interno de uma junção de loops aninhados por iteração , enquanto as contagens de linhas reais são o total de todas as iterações . Isso é confuso para muitas pessoas e é o resultado de uma decisão de design questionável .

Postado pela Microsoft em 14/10/2009 às 14h47
A diferença entre estimado e real que você se refere não é realmente um bug no otimizador; é uma decisão de design questionável na forma como apresentamos as informações no showplan. Essa tabela está no lado interno de uma junção de loop e, nessa posição, os números que relatamos para linhas reais e esperadas não significam a mesma coisa: a contagem real de linhas inclui todas as execuções, enquanto a contagem estimada de linhas é por execução.

Você pode obter uma comparação mais natural visualizando o plano no Sentry One Plan Explorer . Ele faz os cálculos por iteração para você, para que você possa comparar estimativas e reais mais diretamente. Por exemplo, esta é uma visualização de uma das subárvores Merge Interval visualizadas no Plan Explorer:

3. O que fazer para tornar as estimativas mais precisas? Que índice/técnica considerar para melhorar o desempenho da consulta?

A diferença na exibição do plano de exibição é responsável pela primeira pergunta. A resposta de David abordou a reescrita da consulta para um melhor desempenho (e resultados significativos).

Para verificações constantes, há um artigo aqui que pode ajudar a explicar Verificação constante em planos de execução .

O número estimado de linhas é apenas isso, uma estimativa. O Query Optimizer tenta usar execuções anteriores para adivinhar quantas linhas serão retornadas por qualquer operação. Se você estiver usando algo como uma restauração para praticar o ajuste de consulta, é provável que, após a operação de restauração, você não tenha realizado nenhuma manutenção de banco de dados. As estatísticas ajudam a determinar coisas como linhas estimadas, e a manutenção do índice ajudará a consultar os dados de maneira inteligente e fornecer melhores informações para essas estatísticas.

Se a atualização das estatísticas não funcionar, provavelmente é porque não há muitas consultas executadas no banco de dados. Executar essa consulta algumas vezes fornecerá informações melhores, mas se o tempo de execução for excessivo, outras opções precisam ser consideradas para ajudar o otimizador.

Para melhorar o desempenho dessa consulta, você não precisa ir além da tabela de trabalho nas estatísticas de E/S que você retornou. O IO Statistics é uma ótima opção para ter uma ideia de quanto processamento você está realmente fazendo em seus dados. A tabela de trabalho mostrada aqui significa que o otimizador está salvando o conjunto de dados e revisando-o várias vezes. Isso pode ser reduzido drasticamente condensando as três subconsultas mostradas aqui em uma instrução como segue.

SELECT
    SUM(CASE
        WHEN PostType = 1
        THEN 1 ELSE 0 END) AS QuestionEdits,
    SUM(CASE
        WHEN PostTypeId = 2
        THEN 1 ELSE 0 END) AS AnswerEdits,
    COUNT(*) AS TotalEdits
FROM Posts P
INNER JOIN Users U ON
    U.Id = P.LastEditorUserId
    AND U.Id <> P.OwnerUserId
ORDER BY 3 DESC

Observe que não testei o acima porque não tenho o esquema ou o banco de dados, portanto, leve-o com um grão de sal (pode nem compilar).

Com base no que David diz, a nova consulta (um pouco modificada) na verdade obtém um desempenho muito melhor - cardinalidade e tudo mais parece bem. Davi, muito obrigado. Aqui está a consulta otimizada:

SELECT TOP 50 U.Id AS [User Link],
              SUM(CASE
                      WHEN PostTypeId = 1
                      THEN 1
                      ELSE 0
                  END) AS QuestionEdits,
              SUM(CASE
                      WHEN PostTypeId = 2
                      THEN 1
                      ELSE 0
                  END) AS AnswerEdits,
              COUNT(*) AS TotalEdits
FROM Users AS U
     INNER JOIN Posts AS P ON U.Id = P.LastEditorUserId
                              AND U.Id <> P.OwnerUserId
GROUP BY U.Id
ORDER BY TotalEdits DESC;

Estatísticas de execução:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 10 ms, elapsed time = 10 ms.

(50 row(s) affected)
Table 'Posts'. Scan count 1, logical reads 98579, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 112295, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 8750 ms,  elapsed time = 8888 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.