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