Sql-server – Is “Estimated Number of Rows” always per execution within SSMS execution plans

sql serverssms

When I look at execution plans, I often compare "Estimated Number of Rows" with "Actual Number of Rows", looking for discrepancies. For example, when I hover over the bottom arrow in the picture below, the estimate is 1 and the actual is 200, so at a glance it appears to be a terrible estimate.

enter image description here

However, I've come to wonder if this is actually the estimate for just one execution. When I hover over the seek to get more detail, I see that there are 200 executions, and it estimated 199.98. So if my hunch is correct, the estimate is actually dead on.

enter image description here

Can anyone confirm for me if this is the case? And if so, am I the only one to think this is incredibly misleading (especially when hovering over the arrows)? If it helps, here is the script to set up and reproduce the above plan:

-- Setup: Create 2 tables with 100K rows each.

CREATE TABLE T1(Id int NOT NULL PRIMARY KEY, X INT NOT NULL) 
CREATE TABLE T2(Id int NOT NULL PRIMARY KEY) 
CREATE INDEX Ix ON T1 (X, Id)

;WITH  
    Pass0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows  
    Pass1 AS (SELECT 1 AS C FROM Pass0 AS A, Pass0 AS B),--4 rows  
    Pass2 AS (SELECT 1 AS C FROM Pass1 AS A ,Pass1 AS B),--16 rows 
    Pass3 AS (SELECT 1 AS C FROM Pass2 AS A ,Pass2 AS B),--256 rows 
    Pass4 AS (SELECT 1 AS C FROM Pass3 AS A ,Pass3 AS B),--65536 rows 
    Pass5 AS (SELECT 1 AS C FROM Pass4 AS A ,Pass2 AS B),--1048576 rows 
    Tally AS (SELECT  ROW_NUMBER() OVER (ORDER BY C) AS Number FROM   Pass5)
INSERT INTO T1
SELECT Number, Number
FROM  Tally
WHERE Number <= 100000

INSERT INTO T2
SELECT Id
FROM T1

--Now join them

SELECT *
FROM T1
JOIN T2 ON T2.Id = T1.Id
WHERE T1.X <= 200

Best Answer

Yes, this is the case - SSMS does not always show you the estimated rows * number of executions, and this can obviously lead to some inaccurate assumptions if you don't dig deeper as you did. But technically the answer to your question is no. Sometimes SSMS will get this estimate right, depending on the operator and other semantics.

In SentryOne Plan Explorer we try to compensate for this by showing SQL Server's rows * number of executions in several cases instead of just blindly copying the row count from the plan XML. Your plan, for example:

enter image description here

Related Question