Troubleshoot Performance with User Defined Functions in SQL Server

By: Eric Blinn | Updated: 2023-02-13 | Comments | Related: > Functions User Defined UDF

Problem

I'm trying to tune a Microsoft SQL Server query for improved performance and not having much luck. Could the user defined function (UDF) in the SQL Server query be part of the problem? How can I determine that? If it is part of the problem, what can I do about it?

Solution

UDFs can be very appealing to new T-SQL developers, especially those from a more traditional procedural programming background. They allow for higher code reuse and can simplify the readability of code. Unfortunately, SQL Server uses set-based programming and often does not perform as well when UDFs are used. Making matters worse, many common T-SQL performance tuning methods don't accurately report the effects of UDFs on queries. This tip will explore this problem and how to work around the issues.

All the demos will be run using SQL Server Management Studio (SSMS) against an instance of SQL Server 2019. The target database is WideWorldImporters, a free sample database from GitHub.

Create a Baseline for Comparison

To start, a baseline of expected performance needs to be set. Then a UDF will be introduced to see how SQL Server reacts. Consider this simple query:

SELECT OrderID FROM Sales.Invoices WHERE OrderID = 11323;

Using traditional performance tuning methods of including statistics io, statistics time, and an actual execution plan returns these statistics. If these concepts are new to you, check out How to Make Query Execution Faster in SQL Server and SQL Server Performance Tuning with Exeuction Plans and New Indexes.

This STATISTICS IO output shows 2 reads and 0 ms.

This screenshot shows a query plan with only 2 steps.

The query needed only 2 page reads on a single index seek operation. It completed in under 1 ms (millisecond). This is about as tuned as a query can be.

Now consider this query:

SELECT MSSQLTIPS = AVG(Temperature)
FROM Warehouse.ColdRoomTemperatures_Archive
WHERE ColdRoomSensorNumber = 2;

This query runs against a much larger table and reports the need for over 9000 reads. It took about 750 ms.

This is another screenshot of STATISTICS IO output that shows 9554 reads and 785 ms.

The plan is much more involved than the previous query.

This is a very involved query plan with 12 operators.

Finally, the bad query will be turned into a UDF. The data type for sensor was chosen incorrectly on purpose to make the UDF as bad as possible.

CREATE FUNCTION dbo.GetTemp (@Sensor CHAR(12)) RETURNS DECIMAL(10,2)
AS 
BEGIN
  DECLARE @MSSQLTIPS DECIMAL(10,2);
 
  SELECT @MSSQLTIPS = AVG(Temperature)
  FROM Warehouse.ColdRoomTemperatures_Archive
  WHERE ColdRoomSensorNumber = @Sensor;
 
  RETURN @MSSQLTIPS;
END;

Missing Query Plan Information Doesn't Help Tuning Queries

It is time to combine the first demo query, which ran in under 1ms, with the UDF of the second query, which took 750ms.

SELECT OrderID, dbo.GetTemp('2') Temp FROM Sales.Invoices WHERE OrderID = 11323;

This query will read data from both tables, Invoices and ColdRoomTemperatures_Archive, yet somehow, the statistics io output reports only the Invoices table. The execution time appears to be accurate.

Despite running both queries from the earlier demo, this output still only shows 2 reads from one table.

The query plan is no help either. It shows only the seek associated with the Invoices table.

This query plan screenshot shows the same 2 operators as the earlier one despite running both queries at once.

How does one tune a query that only does two reads as part of a single index seek operation but takes almost 1 second to complete? We know another table is involved in this demo, but that won't always be the case when reviewing existing code in a real-world scenario. Imagine a scenario where the query is hundreds of lines long. The plan is long and involved. If the real problem was in one of several UDF calls, it could be incredibly difficult to diagnose the root cause of the performance issue without visibility into the execution of those UDFs.

Finding the Hidden Query Cost of UDFs Using Query Plans

The first place to look for UDF execution information is the estimated execution plan. Earlier in the demo, the actual execution plan was used. This feature is activated via a different button or keystroke operation. This screenshot shows the button and keyboard shortcut needed to get the estimated plan.

This screenshot shows where to find the button for displaying an estimated execution plan.

Unlike the actual execution plan, this plan will appear almost immediately. That is because it does not execute the query first and then display the plan; it just generates and displays a plan. The query is not executed as a part of this operation. Other scenarios where an estimated plan is useful are situations where a plan is desired, but the query cannot be executed. This could include when tuning insert, update or delete statements or when the query execution is expected to take too long, and the developer doesn't want to wait for it to finish to view the plan.

This is the output from the estimated execution plan button. It shows 2 separate plans for the 2 queries.

While the actual execution plan left out information related to the UDF, the estimated plan includes it! The UDF itself is listed by name (green highlight in the plan). It is immediately apparent that the UDF is where the performance problem is. The percentages at either end of the red line make that very clear. It even suggests how to improve the performance of the UDF in the yellow highlighted section.

Finding the Hidden Query Cost of UDFs Using Live Query Statistics

Another way to get performance details related to UDFs within a SQL query is to use Live Query Statistics. Just like the actual execution plan button, to view this information, the feature must be enabled on the query window before executing the query. The button to include Live Query Statistics is shown in the screenshot below.

This screenshot shows the placement of the "Include Live Query Statistics Button".

Executing the query again with this feature enabled brings about a new tab in SSMS that shows the query plan. This query finished in under 1 second, but if the query takes long enough to execute, the data can be viewed moving in real-time. Just like running the estimated execution plan, the output includes information about the UDF.

This screenshot of the Live Query Statistics tab in SSMS looks just like the query plan for the UDF query.

Final Thoughts

SQL Server often works better when UDFs are not part of queries. Resist the temptation to add them to the database. Instead, use common table expressions, subqueries, or views as much as possible. While there might be less code reuse, these allow for more set-based operations and will almost always perform better than their UDF counterparts.

If there is no way around using UDFs, then make sure you know how to find the slow ones so they can be tuned.

Next Steps

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-02-13

Performance Problems and Solutions when using User Defined Functions in SQL Server