Thursday, November 10, 2016

SQL Server 2016 - “Number of Rows Read” on Execution plan


One of the coolest things to release with SQL Server 2016 was “Number of Rows Read” information in execution plan. If you are wondering, what it is look at the picture below




What “Number of Rows Read” operator actually talks about is the total number of rows read by the query plan operator. This is not the same as the total number of rows returned (as output) from the operator.  Total number of rows returned is provided by “Actual Number of Rows”  on the query plan.

For ex:  Let’s say we have the following query


SELECT  *
FROM [AdventureWorks2012].[Production].[TransactionHistory]
WHERE  Quantity = 2 

 Assume the above query does a table scan and filters the data. Then the “Number of Rows Read” would be total number of rows in the table.  “Actual Number of Rows” would be the total number of rows filtered by the "where" clause.

Obviously, the operators with significant differences between “Number of Rows Read” and “Actual Number of Rows” are potential areas to watch out for while query tuning. On upcoming posts, will cover scenarios where “Number of Rows Read” can be put to good use.