Cross post with http://aka.ms/sqlserverteam

As I mentioned on yesterday’s post, with the recent release of SQL Server 2017 CU3, we released yet more showplan enhancements: you can see other posts related to showplan enhancements here.

In this article I’ll talk about the second showplan improvement we worked on, to assist in the discoverability of UDF usage impact on query execution.

The scenario is that if a query uses one or more user-defined scalar functions (such as T-SQL functions or CLR functions), a significant portion of query execution time may be spent inside those functions, depending on the Function definition. And this may be not immediately obvious by looking at the query execution plan.

Recently, we added information on overall query CPU and elapsed time tracking for statistics showplan xml (both in ms), found in the root node of an actual plan (on which I blogged about here). We now added two new attributes: UdfCpuTime and UdfElapsedTime. These provide the total CPU and elapsed time (again, both in ms) that is spent inside all scalar user-defined functions, during the execution of a query.

Below, observe how the UDF is responsible for most of the query's CPU usage and almost half of the execution time.

These will also be available in the upcoming SQL Server 2016 SP2.

For the metrics above I used the following example:

-- Create UDF
CREATE FUNCTION ufn_CategorizePrice(@Price money)
RETURNS NVARCHAR(50)
AS
BEGIN
    DECLARE @PriceCategory NVARCHAR(50)

    IF @Price 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

-- Execute
SELECT dbo.ufn_CategorizePrice(UnitPrice), 
    SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, 
    OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, 
    LineTotal, rowguid, ModifiedDate 
FROM Sales.SalesOrderDetail
GO

Pedro Lopes (@sqlpto) – Senior Program Manager