SQL Server 2014/2016 – Does ActualLogicalReads in Query Execution Plan Include Scalar UDF Reads?

sql server 2014sql-server-2016

Sql Server 2016 and 2014 SP2 introduced new performance information on query execution plan nodes. MSDN: https://blogs.msdn.microsoft.com/sql_server_team/added-per-operator-level-performance-stats-for-query-processing/

One of introduced performance statistics is ActualLogicalReads. This is information which was available earlier with SET STATISTICS IO option. Unfortunately SET STATISTICS IO doesn't take into account reads generated by
user defined scalar functions (link).

I would like to know:

  • if SET STATISTICS IO and information included in query execution plan returns the same values?
  • if reads generated by user defined scalar functions are reported in logical reads presented in query execution plan?

Best Answer

Like Martin said, this is easy enough to test out. Let's use the Stack Overflow data dump as an example. If you want to download your own copy, head over here.

If we hit the Badges table with a scalar valued function with this definition:

CREATE FUNCTION dbo.ScalarFunction ( @uid INT )
RETURNS BIGINT
    WITH RETURNS NULL ON NULL INPUT,
         SCHEMABINDING
AS
    BEGIN
        DECLARE @BCount BIGINT;
        SELECT  @BCount = COUNT_BIG(*)
        FROM    dbo.Badges AS b
        WHERE   b.UserId = @uid
        GROUP BY b.UserId;
        RETURN @BCount;
    END;
GO

And then run a query that uses the scalar valued function:

SELECT TOP 1000 u.DisplayName,
       dbo.ScalarFunction(u.Id)
FROM   dbo.Users AS u;

The query plan looks like this.

And the STATS IO output looks like this:

Table 'Users'. Scan count 1, logical reads 76, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And the plan XML looks the same:

 <RunTimeCountersPerThread Thread="0" ActualRows="1000" ActualRowsRead="1000" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="76" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />

So even though we know our function hits the Badges table and does a bunch of work, none of it registers either in STATS IO or in the query plan. Scalar valued functions (and to a large degree MSTVFs) continue to black out performance counters in modern versions of SQL Server.

To compare, if we just run the function body like this for a single UserId

SELECT  COUNT_BIG(*)
FROM    dbo.Badges AS b
WHERE   b.UserId = 22656
GROUP BY b.UserId;

The STATS IO output looks like this.

Table 'Badges'. Scan count 7, logical reads 107872, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The plan looks like this.

And the plan XML looks like this.

 <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="1" ActualRows="2291" ActualRowsRead="2835393" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="93" ActualCPUms="93" ActualScans="1" ActualLogicalReads="17810" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="2" ActualRows="2193" ActualRowsRead="2839900" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="94" ActualCPUms="93" ActualScans="1" ActualLogicalReads="17810" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="3" ActualRows="2330" ActualRowsRead="2884732" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="94" ActualCPUms="94" ActualScans="1" ActualLogicalReads="18084" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="4" ActualRows="2134" ActualRowsRead="2835817" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="93" ActualCPUms="93" ActualScans="1" ActualLogicalReads="17810" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="5" ActualRows="2180" ActualRowsRead="2846565" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="93" ActualCPUms="93" ActualScans="1" ActualLogicalReads="17810" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
 <RunTimeCountersPerThread Thread="6" ActualRows="2472" ActualRowsRead="2964762" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="95" ActualCPUms="95" ActualScans="1" ActualLogicalReads="18546" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />

Since we know that scalar valued functions run once per row, we can safely assume that none of the IO used by the scalar function show up in the query plan for the calling query. This goes for CPU and other resources, as well.

Related Question