Are SQL Server Functions Dragging Your Query Down?

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.

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.)

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.

function 1

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).

There’s now an additional operator – a compute scalar. The cost has risen slightly to 0.106508.

function 2

Now, I will modify the query to call the function from the SELECT clause.

Looking at the execution plan, I see an index scan and a compute scalar. The cost is the same as before –  0.106508.

function 3

Expanding the properties for the compute scalar, I see the function, but it says there is only one execution.

function 4

A quick glance at my Extended Events live feed tells a different story.

function 5

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.

function 6

That’s a lot more work than advertised!

Does the same thing happen if the function is in the WHERE clause?

Two rows are returned. The execution plan now has an index scan, a compute scalar, and a filter. The cost is 0.118091.

function 7

The Extended Events session again shows 19,972 executions – once for each row in the index.

function 8

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.

Previous Post
Announcing kCura Relativity 9, Data Grid, Elasticsearch, and SQL Server
Next Post
Introduction to the Oracle Data Dictionary

15 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

    Reply
    • 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!

      Reply
  • Jess, will there be a followup to this article? Specifically: alternatives to the inline scalar function, or tips and tricks for performance tuning them?

    Reply
  • 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.

    Reply
  • This is good to know and quite interesting. But I feel like you’ve shown me a problem without suggesting an answer.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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)

    Reply
  • Amit Khanna
    July 27, 2016 5:39 am

    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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}