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.
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:
- SQL SERVER – DBCC DBREINDEX and MAXDOP Not Possible
- SQL SERVER – Fill Factor – Instance Level or Index Level
- List All Sessions – SQL in Sixty Seconds #148
- SQL SERVER – Attach an In-Memory Database with T-SQL
- SQL SERVER – Attach a Database with T-SQL
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
1 Comment. Leave new
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
—===========================================
SELECT
invoice.companyid,
Invoice.InvoiceID,
services.IsRecurring,
services.IsUnlimited,
CategoryID,
ISNULL(ServiceStatus,’ServiceStatus’) AS ServiceStatus,
ISNULL(services.ModiyFromDailyTransactionRpt,0) AS ModiyFromDailyTransactionRpt,
Invoice.[status]
–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')
AND
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