SQL Server 2000 - What really is the "Actual Number of Rows" ?

Question

Hello,

I have an SQL Server 2000 query which performs an clustered index scan and displays a very high number of rows. For a table where I have 260.000 records, the actual number of rows displayed in the plan execution is ... 34.000.000.

Does this makes sense? What am I misunderstanding?

Thanks.

Answer 1

The values displayed on the execution plan are estimates based on statistics. Normal queries like:

SELECT COUNT(*) FROM Table

are 100% accurate for your transaction*.

Here's a related question.

*Edge cases may vary depending on transaction isolation level.

---

More information on stats:

• Updating statistics
• How often and how (maintenance plans++)

Answer 2

If your row counts are off from the query plan, you'll need to update the statistics or else the query optimizer will possibly be choosing the wrong plan. Also, a clustered index scan is almost like a table scan... try to fix up the indexes so you get a clustered index seek or at least an index seek.

Answer 3

But ... If it's the "Actual Number of Rows" ... why is that based on statistics?

I assumed that the Estimated Number of Rows is used when building the query plan (and colected from statistics at that time) and the Actual Number of Rows is some extra info added after the query execution, for user debug and tunning purposes?

Isn't this right?