In most coding languages, functions are often-used blocks of code that can be reused from multiple locations, leading to less code – and cleaner code. SQL Server also lets us create functions that can be used the same way. They are reusable blocks of code that can be called from multiple locations. So, if you need to format phone numbers a certain way, or parse for specific characters, you can do so using a function.
The question is, how much work is SQL Server doing when you call a function? If it’s the SELECT clause, is it called once – processing all rows – or once for each row in the result set, regardless if that’s 1 row or 100,00? What if it’s in the WHERE clause?
I’ll let you in on a little secret: if a function is used in the SELECT or WHERE, the function can be called many, many times. If the function is very resource-intensive, it could be causing your query to be very slow – and you would never see the execution of the function within the execution plan of the calling query.
Yep, SQL Server’s execution plans can be a bit vague when it comes to functions – and by “a bit vague”, I mean, “They don’t show up at all”. You need to dig deeper!
I’m going to run a few demos against the AdventureWorks2012 sample database in a SQL Server 2014 instance to show this!
First, I create a scalar-value function that will return the five left-most letters of a LastName.
CREATE FUNCTION [dbo].[ParseLastName](@LastName VARCHAR(50))
-- Returns the 5 left characters of the last name
DECLARE @ret VARCHAR(5);
SET @ret =
Then, I create an Extended Events session to track statement completion. (Note: I have only tested this on SQL Server 2014, no lower versions.) (Using SQL Server 2008 R2 or earlier? You could create a server-side trace to capture sp_statement_completed and sql_statement_completed, but it won’t give you some functionality I’ll show later.)
CREATE EVENT SESSION [CaptureFunctionExecutions] ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
ADD EVENT sqlserver.sql_statement_completed(
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
I start the Extended Events session, and then turn on actual execution plans.
I start with a simple query, which returns 19,972 rows.
The execution plan shows an index scan and has a cost of 0.10451.
Looking at the details of the index scan, I see Estimated Number of Executions is 1, and Number of Executions is 1.
Let’s look at the same query when it performs the same calculation as the function – LEFT(LastName, 5).
SELECT LastName, LEFT(LastName, 5)
There’s now an additional operator – a compute scalar. The cost has risen slightly to 0.106508.
Now, I will modify the query to call the function from the SELECT clause.
SELECT LastName, dbo.ParseLastName(LastName)
Looking at the execution plan, I see an index scan and a compute scalar. The cost is the same as before – 0.106508.
Expanding the properties for the compute scalar, I see the function, but it says there is only one execution.
A quick glance at my Extended Events live feed tells a different story.
If I add grouping by statement, I can see the function was actually executed 19,972 times – once for each row in the result set.
That’s a lot more work than advertised!
Does the same thing happen if the function is in the WHERE clause?
SELECT FirstName, LastName
WHERE dbo.ParseLastName(LastName) = 'McCar';
Two rows are returned. The execution plan now has an index scan, a compute scalar, and a filter. The cost is 0.118091.
The Extended Events session again shows 19,972 executions – once for each row in the index.
The data isn’t filtered out until after the function is called, so it is executed once for each row.
These examples prove that whether one or many rows are returned as the query result set, if a function is used in the SELECT or WHERE, the function can be called many, many times. It could be one of the top resource-consuming queries in your server!
How can you see if a function is bringing your server’s performance down? Look at the top queries in your plan cache using our sp_BlitzCache tool, by total CPU and by number of executions, to see if this is happening to you.