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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE FUNCTION [dbo].[ParseLastName](@LastName VARCHAR(50)) RETURNS VARCHAR(5) AS -- Returns the 5 left characters of the last name BEGIN DECLARE @ret VARCHAR(5); SET @ret = LEFT(@LastName, 5) RETURN @ret END; |
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.)
|
1 2 3 4 5 6 7 8 9 |
CREATE EVENT SESSION [CaptureFunctionExecutions] ON SERVER ADD EVENT sqlserver.sp_statement_completed( ACTION(sqlserver.sql_text,sqlserver.tsql_stack)), ADD EVENT sqlserver.sql_statement_completed( ACTION(sqlserver.sql_text,sqlserver.tsql_stack)) 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) GO |
I start the Extended Events session, and then turn on actual execution plans.
I start with a simple query, which returns 19,972 rows.
|
1 2 |
SELECT LastName FROM Person.Person; |
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).
|
1 2 |
SELECT LastName, LEFT(LastName, 5) FROM Person.Person; |
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.
|
1 2 |
SELECT LastName, dbo.ParseLastName(LastName) FROM Person.Person; |
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?
|
1 2 3 |
SELECT FirstName, LastName FROM Person.Person 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.
Conclusion
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.
18 Comments. Leave new
I do understand Scalar functions can be very expensive depending on how you use it and how many rows it is returning.
But what if we modify the same function to return as a table and use it OUTER APPLY.
Then how will it perform considering the same resultselt ? I am curious to know your views on this.
EG:
–Modified Function
CREATE FUNCTION [dbo].[ParseLastNameT](@LastName VARCHAR(50))
RETURNS TABLE
AS
RETURN
(
SELECT ‘LastName’=CAST(LEFT(@LastName, 5) AS VARCHAR(5))
)
–Query
SELECT A.LastName,fnParseLastName.LastName
FROM
Person.Person A
OUTER APPLY
dbo.ParseLastNameT(A.LastName) AS fnParseLastName
Ranjeet – I’m a big fan of running experiments. If you wonder what performance of an approach will be, why not try experiments on your own system? You’ll get first-hand experience learning how things work in your own environment. Enjoy!
Thanks Brent , I will definately try as this has increased my curosity and will find out but I just wanted to know your views. But I do appreciate your articles and it has taught me lot. 🙂
Great, glad I could help!
Thanks for the hint,
in just making ONE call it saved me from 30 to 1 sec execution time
Jess, will there be a followup to this article? Specifically: alternatives to the inline scalar function, or tips and tricks for performance tuning them?
Dave, if you’re looking for more info on functions, you should check out this excellent article Jeremiah wrote for simple-talk a while back: https://www.simple-talk.com/sql/t-sql-programming/sql-server-functions-the-basics/.
I’m not surprised by this at all. In fact, to me this is as obvious a result as it can be. Now if SQL called your function more times than you had rows via WHERE, then it’d be more interesting.
This is good to know and quite interesting. But I feel like you’ve shown me a problem without suggesting an answer.
Here’s one possible solution:
http://sqlblog.com/blogs/adam_machanic/archive/2006/08/04/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post.aspx
Hi there. As Daniil S noticed it’s not at all surprising that a scalar function is called as many times as there are rows flowing. In what other way would SQL ensure that a value is being calculated for the rows? THERE IS NONE OTHER WAY. It’s true for any function that has to CALCULATE (not retrieve) a value based on the values in any particular row.
Being a SQL purist, I do not want user functions at all. This is more than the awful performance, but the basic model for a declarative language. In SQL/PSM, I can declare a module to be DETERMINISTIC, modifies SQL data, etc, so the SQL engine can “do stuff” with it. I do not have that in SQL Server so I am screwed and have to assume the worst.
Joe – that’s an interesting take. Out of curiosity, what other things would you remove from the SQL Server engine if you could?
Just came across this. I know it was posted some time back, but I was wondering what happens if you have a scenario like:
CREATE FUNCTION dbo.test (@date AS DATETIME, @days INT) RETURNS DATETIME
AS
BEGIN
RETURN
(
SELECT DATEADD(DD, @days, @date)
)
END
and use that in a WHERE clause
SELECT …
FROM …
WHERE ship_date > dbo.test(getdate(), 10)
Nice write up.. But there should also some workaround provided to remove the scalar valued functions.
I understand that there is no easy way to remove the iterative code to set based code but would be great to know any approach.
Amit – sure! Click Training at the top of our site, and check out our T-SQL Level Up training.
Brent, is this still applicable in SQL Server 2019 or has it been improved?
Paresh – yes, it’s still applicable. 2019 added a feature called scalar function inlining, but sadly it rarely works.