2017-06-08

Number of Rows Read in Actual Execution Plan

There's a new piece of diagnostic information in SQL 2012 SP3, SQL 2014 SP2, and SQL 2016, Number of Rows Read, inside the actual execution plan.
Before these releases, we can only know the number of rows returned from an operator, but we cannot know the actual number of rows that the operator processed/read. In case of "residual predicate pushdown", there's a hidden filter, aka Predicate or Residual Predicate which being evaluated for each row output by the table/index scan operator. In this case, the number of rows returned and the number of rows that the operator processed/read can have a big difference.
Let's see an example as below:

SET NOCOUNT ON;
GO

IF OBJECT_ID(N'ActualRowTbl') IS NOT NULL
    DROP TABLE ActualRowTbl;
GO

CREATE TABLE ActualRowTbl (
    id INT IDENTITY NOT NULL PRIMARY KEY,
    firstName varchar(100) DEFAULT ('Ken'),
    lastName varchar(100) DEFAULT ('Lam')
)
GO

INSERT ActualRowTbl DEFAULT VALUES;
GO 999

INSERT ActualRowTbl VALUES ('Jeremy','Po');
GO

CREATE INDEX IX_ActualRowTbl_1 ON ActualRowTbl (firstName, lastName);
GO

SELECT firstName FROM ActualRowTbl WHERE lastName = 'Po'
GO


By checking the actual execution plan of the last select statement, you can see there's a big difference between the number of rows returned (Actual Number of Rows) and Number of Rows Read, inside the Index Scan operator.

No comments:

Post a Comment