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