Estimate rows Vs Estimated rows to be read what’s the difference

~ franklinyamamoto

Starting with SQL Server 2016 sp1 execution plans from the cache now contain “Estimated Number Of Rows to be Read”.  If you are interested in how this is different from “Estimated Rows Per Execution” and how this value can help identify a performance problem in your plan read on…

How does it differ from Estimated Rows Per Execution?

“Estimated rows per execution” are the number of rows that are expected to be returned by a particular operator/step in the plan.  “Estimated number of rows to be read” is an estimate of how many rows are expected to be read.  An analogy that can be applied here is with a phone book (yes a bit dated but I think most of you remember what a phone book is):

  • “Estimated rows per execution” is the number of entries in the phone book with a given last name and first name that you were looking for
  • “Estimated number of rows to be read” is how many pages you had to go through to find the entries you were looking for

How can this help spot a performance problem?

If you see a very high number of “Estimated Number Of Rows to be Read” relative to a very low “Estimated Rows Per Execution” within a scan (clustered index, non clustered, or heap) you should probably look at the predicate to see if you are doing anything that makes it unsargable (i.e. applying a function to the column in the table). As well as having unsargable predicates this might be an indicator of a missing index.

Note the above plan shows it expects to read over 3.6 million rows; however, it expects to only return 1 row.  That is a lot of work to just return 1 row!  If you look at the predicate you’ll see there is an ISNULL function applied to the column and as a result the query is unsargable.  See this post describing the issue with ISNULL and sargability.

If you read through the above post, you’ll see fixing the unsargable predicate renders a different plan.  This plan shows much fewer rows in the “Estimated Number Of Rows to be Read”.  This now shows 1 row in the estimate rows and 1 row for the expected rows to be read.  As a result the query is significantly faster (if you read the related post this is 9 seconds runtime vs 1 millisecond):

Final thoughts

As with most things in SQL and tuning, the effectiveness of looking in your plan and this being an indicator of a problem all depends on your situation.  From looking at a very diverse and large set of execution plans, I’ve seen that this is a pretty decent indicator that a plan has a hot spot with some tuning opportunities.