UDFs CPU and Elapsed Time

December 23, 2019

Microsoft SQL Server Management Studio (SSMS) version 17.5 added new showplan attributes UdfCpuTime and UdfElapsedTime to QueryTimeStats. These two attributes will measure the time and CPU spent on user-defined functions within a query execution hence helping to discover the impact of UDF execution within full query execution. This feature was first added in SQL Server 2017 CU3 and was backported to SQL Server 2016 SP2. Finding the execution time and CPU for UDF was always a challenge for Data professionals because the number of times a function will execute will vary.

From Microsoft Documentation:

The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. An example is a function invoked by a subquery in a where clause. The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer.

Here is a section of code you run on sample database AdventureWorks to see these two attributes. You will need a newer version of SSMS which you can always find here. Turn on the actual execution plan in SSMS.

-- Script copied form:
-- https://blogs.msdn.microsoft.com/sql_server_team/more-showplan-enhancements-udfs/
-- Run this in SQL2017
-- Download AdventureWorks backup
-- https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks
-- Create User Defined Function
USE [AdventureWorks];
GO
DROP FUNCTION IF EXISTS ufn_CategorizePrice;
GO
CREATE FUNCTION ufn_CategorizePrice(@Price money)
RETURNS NVARCHAR(50)
AS
BEGIN
    DECLARE @PriceCategory NVARCHAR(50)

    IF @Price < 100 SELECT @PriceCategory = 'Cheap'
    IF @Price BETWEEN 101 and 500 SELECT @PriceCategory =  'Mid Price'
    IF @Price BETWEEN 501 and 1000 SELECT @PriceCategory =  'Expensive'
    IF @Price > 1001 SELECT @PriceCategory =  'Unaffordable'
    RETURN @PriceCategory 
END;
GO
--Run the function
USE AdventureWorks
GO
SELECT dbo.ufn_CategorizePrice(UnitPrice), 
    SalesOrderID, SalesOrderDetailID, 
		CarrierTrackingNumber, OrderQty, 
		ProductID, SpecialOfferID, 
		UnitPrice, UnitPriceDiscount, 
    LineTotal, rowguid, ModifiedDate 
FROM Sales.SalesOrderDetail
GO
Properties of Root Node, under QueryTimeStats showing the two new attributes with UDF CPU and Elapsed time.

Exposing the CPU and Elapsed time for UDF is a huge help in terms of troubleshooting performance issues with queries including UDFs.

December 25, 2019
Based on Kapil’s comment I tested this feature on the older version. It works with SQL Server 2014 SP3 and did not work with SQL Server SP4.

3 replies on “UDFs CPU and Elapsed Time”

I tested this feature on the older version. It works with SQL Server 2014 SP3 and did not work with SQL Server SP4.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.