Unexpected estimated rows in query execution plan (Sql Server 2000)?

SELECT lt. User FROM Users you INNER JOIN largeTable lt ON u. User = lt.

User WHERE u. ExternalId = 100 Please see this: subqueries-vs-joins.

Yes, and, rewriting as a join has the same problem! – Querylous Dec 2 '10 at 15:39.

The optimizer does the best it can, but statistics and row count estimations only go so far (as you're seeing). I'm assuming that your more complex query can't easily be rewritten as a join without a subquery. If it can be, you should attempt that first.

Failing that, it's time for you to use your additional knowledge about the nature of your data to help out the optimizer with hints. Specifically look at the forceseek option in the index hints. Note that this can be bad if your data changes later, so be aware.

Forceseek is 2008 only. The OP is on 2000. – Martin Smith Dec 1 '10 at 15:40 Ah, didn't realize that.

Migrated off 2000 a while ago so I didn't have anything to test it against. :( – Donnie Dec 1 '10 at 15:43 Thanks Donnie; I get the same effect when I rewrite as a join. I don't think SS 2000 has the forceseek, but, I'll check on that.. – Querylous Dec 2 '10 at 15:37 Oops, see someone agrees with me.

I fixed this problem by just doing an addnl query to get subquery result, then dropping in to main query. But, it's just really weird to me why optimizer did this! – Querylous Dec 2 '10 at 15:39 @Querylous - On page 226 of "Inside Microsoft SQL Server 2005: Query Tuning and Optimization" this method is specifically mentioned if you have a copy... – Martin Smith Sep 11 at 12:47.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.