2

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?
Improve this question
1
  • 4
    Should be easy to set up a test. Create a UDF that reads a large table and see what is reported.
    – Martin Smith
    May 28, 2017 at 17:03

1 Answer 1

Reset to default
3

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.