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.
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.
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