Why Execution Plan Operator Read More Rows Than Available? – Interview Question of the Week #255

Question: Why Execution Plan Operator Read More Rows Than Available?

Answer: Statistics.

Why Execution Plan Operator Read More Rows Than Available? - Interview Question of the Week #255 ExecutionPlanOperator0-800x287

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.

Solarwinds

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.

Why Execution Plan Operator Read More Rows Than Available? - Interview Question of the Week #255 ExecutionPlanOperator

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.

Why Execution Plan Operator Read More Rows Than Available? - Interview Question of the Week #255 ExecutionPlanOperator1

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)

Solarwinds
, , ,
Previous Post
What is the Priority of Database Scoped Configurations? – Interview Question of the Week #254
Next Post
Can Admin Rename SA Account in SQL Server? – Interview Question of the Week #256

Related Posts

Leave a Reply

Menu