SQL: actual number of rows difference [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

How does SQL server work out the 开发者_运维问答estimated number of rows?

I was just looking through SQL Server Graphical Execution plan and I encountered the following two information:

1. actual number of rows-2385
2. estimated number of rows-180

I was wondering why this difference was coming. Can you explain me what is the difference between them. It will be helpful if you can explain with some example.

Thanks.

---

Row estimates are based on statistics. Inaccurate estimates can come from a number of factors:

• Low cardinality in your data or stats
• Out of date or incomplete stats
• Inefficient execution plan
• Concurrency issues (changes in data from creation of execution plan to execution time)

As a rule, don't worry about it until you have issues.

I my experience, it's most often based on the cardinality in your stats.

If you are selecting based on two fields, both of which have indexes, the row estimate will be based on a product of the likelihood of the values in the respective indexes, times the total number of rows.

---

I don't know SQL Server too much, so this is an educated guess: There is a discrepancy between the statistical data kept about the table and the actual data in the table.

Databases keep statistical data about tables, which can be used by the optimizer to find an optimal (least expensive) query plan for the SQL that you want to execute. Those statistics include for instance the total number of rows and the distribution of data across columns (for instance that the field 'gender' contains 60% values 'm' and 40% values 'f'). What your likely seeing here, is that the statistics are outdated and the calculations done by the optimizer hence inacurate: The optimizer assumed the result to contain 180 rows, in reality though (when executing the query) it returns 2385. Try updating the statistics (don't know the exact syntax in SQL server) and see if that changes the numbers.