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.

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.

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)

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

Related Posts

1 Comment. Leave new

  • Sahil Jaiswal
    August 19, 2021 12:43 am

    In execution plan the index was in Seek mode still Number of rows read Actual Number of rows for All execution.
    Number of Rows Read 2070610.
    Actual Number of rows for All execution=897.

    Can you please help me out why there is lot of difference?

    Query 1st

    ISNULL(ServiceStatus,’ServiceStatus’) AS ServiceStatus,
    ISNULL(services.ModiyFromDailyTransactionRpt,0) AS ModiyFromDailyTransactionRpt,
    –INTO #Temp_WashInvoiceExtra
    FROM Wash services WITH(NOLOCK)
    JOIN Company service WITH(NOLOCK) on services.ExtraWashId=service.ServiceId
    JOIN WashInv invoice WITH(NOLOCK) on services.invoiceId=invoice.invoiceId
    LEFT JOIN #Temp_Location TL ON TL.LocationID=invoice.LocationId
    WHERE Invoice.WashDate>=@StartDate AND Invoice.WashDate=@StartDate AND Invoice.WashDate<=@EndDate
    and invoice.companyid=@companyId AND
    invoice.[Status] IN( 'Completed' ,'PartiallyRefunded','PartiallyRefunded')
    Invoice.LocationId IN (0,@locationId).

    so when it's means Second query is efficient compare to 1st query.
    but number of row read is very high in second query.
    so sir please help me out which one is better


Leave a ReplyCancel reply

Exit mobile version