Question: Why Execution Plan Operator Read More Rows Than Available?
Answer: Statistics.
The short answer is it is because of statistics, however, let us learn about the detailed answer in this blog post. I received this question during my recent consultation engagement Comprehensive Database Performance Health Check.
Let us run the following query for a sample database WideWorldImporters. Make sure that you have enabled the execution plan.
SELECT * FROM WideWorldImporters.Sales.OrderLines WHERE UnitPrice = 36.00
Now let us look at the execution plan.
It is very clear from the execution plan that query operators are reading more rows than what was supplied to them. This is actually not a bug or even feature, I would rather call it a regular behavior.
The reason, the original operator is saying that there are only 21 rows because that is the information it has received from the available statistics. You can quickly validate that by looking at the pop-up information box when you mouse over the operator.
SQL Server statistics which helps query to build an execution plan actually build the execution plan thinking there are only 21 rows. However, when the actual query is executed, at that time SQL Server Engines goes through all the necessary data and displays all the rows which satisfy the query requirements. There is a total of 33 rows that satisfies the query results and the final result contains all the qualifying rows.
This is one of the reason when you see a big difference between estimated rows and actual rows, you here the general suggestion of “Update the statistics” to get a better estimation for Execution Plan Operator, however, it is not always the solution and we will talk about it some other day.
Here is another blog post which I wrote about the improvement of SQL Server statistics, you can read about it here: Persisting Sample Percentage for Statistics – PERSIST_SAMPLE_PERCENT.
Reference: Pinal Dave (https://blog.sqlauthority.com)
