Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Jan 29 '13 at 3:53

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer 1

up vote 5 down vote accepted

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 SQL Sentry 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

Disclaimer: I work for SQL Sentry.

share|improve this answer
1  
Thanks Aaron! It seems like SSMS did a great job of obscuring that fact... I like the way SQL Sentry implemented it much better. I'm curious also as to how it even gets the estimate of 1 row per execution? It seems more like an assumption than based on statistics. Any idea? –  JohnnyM Jan 29 '13 at 18:39
    
@JohnnyM I think it's more like it assumes one execution for the operator, as opposed to one execution per row for the operator. –  Aaron Bertrand Jan 29 '13 at 18:45
    
Not sure I understand. Just to clarify, when SSMS says "Estimated Number of Rows = 1" where does it get the 1 from? –  JohnnyM Jan 29 '13 at 18:55
    
I think you are right, that this is just a poor assumption, not based on actual statistics. And it fails to account for the fact that it will iterate for each row, not just for one. –  Aaron Bertrand Jan 29 '13 at 19:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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