Actual “Number of Rows Read” in SQL Server Execution Plans

Execution plans got a cool new piece of diagnostic information in SQL Server 2012 SP3, SQL Server 2014 SP2, and SQL Server 2016: “Number of Rows Read”.

In fancy language, this is “better diagnostics” when a query plan has “residual predicate pushdown” (KB 3107397).

In human language, SQL Server will now tell you “How many rows did I really have to read, even if I have a hidden filter in here?”

This appears in actual execution plans only. Sorry, there is no such thing as “Estimated Number of Rows Read” that I can find.

Let’s take a look and see it in action.

Create our simple test index

We’re making a table with 1000 rows. 999 rows have firstname=’Kendar’ and lastname=’Little’. Only one row has firstname=’Jeremiah’ and lastname=’Peschka’.

SET NOCOUNT ON;
GO

IF OBJECT_ID(N'actualrowstest') IS NOT NULL
    DROP TABLE dbo.actualrowstest;
GO

CREATE TABLE dbo.actualrowstest (
    imakeywhatever INT IDENTITY not null,
    firstname varchar(256) DEFAULT ('Kendar'),
    lastname varchar(256) DEFAULT ('Little'),
    CONSTRAINT pk_actualrowstest PRIMARY KEY CLUSTERED (imakeywhatever)
)
GO

INSERT dbo.actualrowstest DEFAULT VALUES;
GO 999

INSERT dbo.actualrowstest VALUES ('Jeremiah','Peschka');
GO

All our tests are going to use one nonclustered index, which is on firstname, lastname:

CREATE INDEX ix_actualrowstest_firstname_lastname ON dbo.actualrowstest (firstname, lastname);
GO

Here’s where you see “Number of Rows Read”

We run a query that can use the nonclustered index, but it’s not perfect for it. We want to find every row where lastname=’Peschka’:

SELECT firstname
FROM dbo.actualrowstest
WHERE lastname='Peschka'
GO

Our index looks like this:

index-firstname-500px

 

Everything is sorted by firstname, then by lastname. Every column that the query wants is here, but the optimizer doesn’t know if perhaps there’s a row at the end of this table for “Kendar, Peschka”. It has to check all 1000 rows in a scan to find that single row.

index-firstname-scan-500px

This Filter Was Difficult to Measure in Actual Execution Plans Before

Here’s what the execution plan for our query looks like. We can see that it’s a scan, but that narrow line coming out of the scan is misleading.

Actual-Plan-Index-Scan

If we hover over that line we can see that it estimated it’d return 1 row (and it was right). But before this improvement, we couldn’t see how many rows it actually READ to find that data. One row isn’t so bad, is it?

Actual-Plan-Index-Scan-Actual-Rows

Here’s the New Magic: Actual Number of Rows Read

If you hover over the properties of the scan itself, you can now see the number of rows read to output that single row. That’s awesome, because in some cases we might have scanned a million rows. Or a hundred million rows. We couldn’t tell from just the actual plan, before this!

Actual-Plan-Index-Scan-Number-Rows-Read

This Only Appears When you Have a “Predicate”, not Just a “Seek Predicate”

Look at the bottom of that tooltip. See how it says “Predicate”, and says [IndexTest].[dbo].[actualrowstest].[lastname]=[@1]?

That’s a hidden filter. Or in those fancy terms in the KB, “residual predicate pushdown” has occurred — instead of having the scan dump all the rows into a separate filter operator, it snuck it in here.

If our query were looking for firstname=’Jeremiah’, then we would have an Index Seek operator only, and the plan would show a “Seek Predicate”. In that case, “Number of Rows Read” doesn’t appear in the plan because the value would just be the same as “Actual Number of Rows.” In other words, you’ll only see this in your plans if you have a plain old “Predicate” (aka hidden filter) on an operator.

6 Comments. Leave new

  • Nice breakdown. I saw the announcement and I wasn’t quite sure what was meant by residual predicate pushdown, this clarifies that well.

    Before this, I would have to just infer this occurring by comparing stats io…ie., looks like a small scan from the output, but shows 10 bajillion logical reads…

    Reply
  • […] Kendra Little shows us something new, the Number of Rows Read tag in an execution plan: […]

    Reply
  • My company is just on 2008R2, but I guess I can look forward to the future. As a side note, I did not know you could put a number after a GO statement to invoke a number of executions. Works on 2008R2 – I wonder how long it’s been around?

    Reply
  • Hello,
    I have a strange thing with that. I have several big partitionned table and so am working on different partition. When I look at the plan, I see some warnings for the table for the clustered index scan where it reads more rows than it finally uses… In that case I don’t have a special filter, but I see in the plan that for the seek predicate sql server uses the partition elimination function : rangepartitionNew but I see in the seek that the operators look for the range >= and = and strict <.
    So it just looks like sql reads a partition too much and removes it afterwards… The problem of course is that in my case we have millions rows in each partitions…

    Reply

Share a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu
%d bloggers like this: