SQL SERVER – Number of Rows Read – Execution Plan

Recently one of the clients sent me the following two images from the execution plan and his question was about the Number of Rows Read in the SQL Server Execution plan. I discussed this in detail during our Comprehensive Database Performance Health Check.

SQL SERVER - Number of Rows Read - Execution Plan Rows-Read-800x247

Before we continue the blog post let us see both the images.

Number of Rows Read != Actual Number of Rows

In this image, you can see that number of rows read is very high, and the actual number of rows is way lesser than that. This is because to find 621 rows SQL Server engine had to do the entire cluster index scan.

SQL SERVER - Number of Rows Read - Execution Plan first

Number of Rows Read != Actual Number of Rows

In this image, you can see that number of rows read is equal to the actual number of rows This is because to find 1069 rows SQL Server engine did not have to go through the entire clustered index but was able to find all the necessary information by just seeking the index.

SQL SERVER - Number of Rows Read - Execution Plan second

Summary – Number of Rows Read

In our example, it is very clear that when index seeks happened it read less number of rows and was efficient compared to the entire clustered index scan. However, please do not think that scans are slower and seek are faster. The reality is that SQL Server always finds the most efficient way to read the data.

I often see guidance on the internet that when the Number of rows read is different from an actual number of rows for all executions, you should update statistics. Honestly, I am not against statistics but I do not believe just updating the statistics will make big difference.

When you see the huge difference in both the numbers, you need to inspect the query and often have to adjust (create, modify) indexes. I will be happy to discuss this with you, do connect with me on LinkedIn.

Here are a few additional blog posts on the same subject:

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Fill Factor – Instance Level or Index Level
Next Post
SQL SERVER – Disable Rowgoal Optimizer

Related Posts

Leave a Reply

Menu