1

While practicing in tuning queries on StackOverflow database I got stuck on the following one:

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

The plan is here.

Execution stats:

(50 row(s) affected)
Table 'Posts'. Scan count 43217677, logical reads 172988050, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 5, logical reads 12692, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, 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 = 701344 ms,  elapsed time = 192167 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.

I have three questions here:

  1. What does the operator 'Constant scan' refer to?
  2. Why row estimates can be incorrect in the following blocks:

a.

enter image description here

b. enter image description here

  1. What to do to make estimates more accurate? What index/technique to consider to improve the query performance?

Any hint will be deeply appreciated.

1
  • 1
    The T-SQL in your plan XML does not match what you pasted in your question, which also gives the answer to constant scans. Also, here's a PtP link from your XML: brentozar.com/pastetheplan/?id=rJM1IFqsb
    – Forrest
    Sep 28, 2017 at 14:30

3 Answers 3

4

The question is tough to answer because the query does not match the plan provided, and the query used to generate the plan is somewhat meaningless:

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

Nevertheless:

  1. What does the operator 'Constant scan' refer to?

A Constant Scan reads from an in-memory table of constant values. It may have one or more rows, and zero or more columns. Yes, a Constant Scan may have zero columns; it is often introduced by the optimizer simply as a source of (empty) rows, to which columns may be added by later operators (typically Compute Scalars).

To a large extent, Constant Scans are often purely architectural artifacts necessary for the correct operation of the query, or as part of an optimization.

When Constant Scans and Compute Scalars sit underneath a Merge Interval, this is a sure sign that these elements were introduced post-optimization to compute and collapse multiple logical intervals into a single interval suitable for driving an Index Seek on that range.

In this particular case, the Constant Scans represent the expansion of OwnerUserId <> Users.Id into:

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

The post-optimization rewrite into the Merge Interval subtree is triggered by this x < y OR x > y predicate. The machinery tests the ranges for overlaps and collapses the resulting "merged interval" into start and end points that can be used with an index seek.

The following minimal example shows SQL Server rewriting the predicate in this way so an index seek can be generated:

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

Demo plan

The resulting Clustered Index Seek there has the properties:

Seek properties

The end result is scanning the index forward until the end point (excluding the target value) then restarting the scan just after the target value to the end of the index. [Expr1011] and [Expr1012] are the computed start and end points of the excluded range.

You can read more about this in my article Dynamic Seeks and Hidden Implicit Conversions.

  1. Why row estimates can be incorrect in the following blocks

SSMS shows estimates on the inner side of a nested loops join per iteration whereas the actual row counts are the total over all iterations. This is confusing to many people, and the result of a questionable design decision.

Posted by Microsoft on 10/14/2009 at 2:47 PM
The difference between estimated and actual that you refer to is not really a bug in the optimizer; it's a questionable design decision in the way we present information in showplan. This table is on the inner side of a loop join, and in that position the numbers we report for actual and expected rows don't mean the same thing: The actual row count includes all exections, while the estimated row count is per execution.

You can get a more natural comparison by viewing the plan in Sentry One Plan Explorer. It does the per-iteration calculations for you, so you can compare estimates and actuals more directly. For example, this is a view of one of the Merge Interval subtrees viewed in Plan Explorer:

Merge Interval subtree

  1. What to do to make estimates more accurate? What index/technique to consider to improve the query performance?

The difference in showplan display accounts for the first question. David's answer has addressed rewriting the query for better performance (and meaningful results).

3

For constant scans there is an article here which may help explain Constant Scan in Execution Plans.

The estimated number of rows is just that, an estimate. The Query Optimizer attempts to use past executions in order to guess how many rows are going to be returned by any given operation. If you're using something like a restore to practice query tuning then odds are that after your restore operation you did not perform any database maintenance. Statistics helps to determine things like estimated rows, and index maintenance will help to both intelligently query the data and provide better information for those statistics.

If updating the statistics doesn't work, it's probably because there are not very many queries that have been executed against the database. Running that query a few times will give it some better information, but if the execution time is excessive other options need to be considered to help the optimizer.

For making this query perform better you need look no farther than the worktable in the I/O statistics you've returned. IO Statistics is a great option for getting an idea of how much processing you're really doing on your data. The work table shown here means that the optimizer is saving the dataset then reviewing it multiple times. This can be reduced drastically by condensing the three subqueries shown here into one statement as follows.

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

Note, I haven't tested the above because I don't have the schema or database so take it with a grain of salt (it might not even compile).

0

Based on what David tells the new query (a bit modified) actually gets much better performance - cardinality and everything else seems fine. David, thanks a lot. Here is the optimmized query:

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;

Execution statistics:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.