Estimated No of Rows Vs Actual No of Rows

Question

Estimated No of Rows Vs Actual No of Rows

sqlnaive

SSCoach

Points: 17437
More actions
June 22, 2011 at 11:35 am

#238329

Hello Fellas, In my execution plan I can see clustered index seek for one table with only showing Object and Seek Predicates. Statistics are updated. Still it shows estimated number of rows as 1 and actual number of rows in thousands.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Post the plan

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlnaive SSCoach Points: 17437 More actions · Answer 2

Hello Gail. Somehow it's not possible for me to post execution plan. 🙁

Though i know it is hard to predict but I will be thankful if you could tell me few possible reasons for this behaviour.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If I had to guess, your version of management studio is pre SP2 and what you think is a clustered index scan is a key lookup. Key lookups are always done a row at a time (that's why they're so bad).

And this: http://sqlinthewild.co.za/index.php/2009/09/22/estimated-rows-actual-rows-and-execution-count/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlnaive SSCoach Points: 17437 More actions · Answer 4

I am not sure about pre SP2 thing but definitely it is clustered index seek and not scan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I'm almost willing to take a bet that if you check the version of SSMS (not the server) it'll be 2005 RTM. So many people forget they have to patch the client tools as well as the servers.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlnaive SSCoach Points: 17437 More actions · Answer 6

Sorry if it sounds stupid gail bu how can I check if SSMS will be 2005 RTM

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Help->About (as with most Windows apps)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlnaive SSCoach Points: 17437 More actions · Answer 8

It shows 9.00.1399.00 for SQL Server 2005 Management Studio.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

2005 RTM. (I should have taken the bet)

Patch it to the same level as your servers, then you'll see (likely) a key lookup, not a clustered index seek (which is what that really is)

There will still be the difference between actual and estimated rows, go read the blog post I referenced to see why (it's correct, not a misestimate)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398904 More actions · Answer 10

Is the table a part of a join? Is it the bottom part of a Loop join perhaps? How many executions are there? Does that equal the number of rows?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning