This post was motivated by an email question I got this week.

Imagine you have the following scalar UDF:

CREATE FUNCTION dbo.RemoveYear (@date datetime)
RETURNS datetime
AS
BEGIN
    
    DECLARE @removeyear datetime = DATEADD(year, -1, @date);
 
    RETURN(@removeyear);
END;
GO

Now – aside from the fact that this function doesn’t really need to exist in the first place since we could use the DATEADD function directly, the key point of this example is that we have a scalar UDF that doesn’t access a data source unless you decide to apply it as part of a data accessing query.

If I execute the UDF as follows, how many logical reads would you expect to see for the very first execution on the server?

SELECT dbo.RemoveYear ('12/31/1999');
GO

If I have SET STATISTICS IO ON, the answer is zero. If I’m using SQL Profiler or Extended Events in SQL Server 2012, the answer is “2”. 

image thumb Tale of the Inconsistent UDF Logical IOs

What about consecutive executions?  Let’s free the procedure cache and give it a try…

DBCC FREEPROCCACHE;
GO

SELECT dbo.RemoveYear ('12/31/1999');
GO

SELECT dbo.RemoveYear ('12/31/1999');
GO

SELECT dbo.RemoveYear ('12/31/1999');
GO

What do we see for reads? 

image thumb Tale of the Inconsistent UDF Logical IOs

We see 2 reads for the initial post-DBCC FREEPROCCACHE and then 0 reads for consecutive executions. 

If I create an Extended Events session to look at any cache lookups, I see the following:

image thumb Tale of the Inconsistent UDF Logical IOs

This was tested on SQL Server 2012, but I believe you’ll see this in earlier versions as well.  The takeaway is that we’re not just tracking data page reads here. Post DBCC FREEPROCCACHE I see two cache attempts and then 2 logical reads for the statement completion.  Consecutive calls show the attempts AND hit and then zero logical reads.

If you’ve seen similar or conflicting behavior, I’d be quite interested to hear about it in the comments of this post.  Thanks!