More Showplan enhancements – UDFs

Cross post with https://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.

Although very useful as a means to create a reusable routine, when using UDFs referenced in a query, you may be facing hidden costs that can be detrimental to performance, in a cursor like execution model.

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. Before this improvement, the internal execution of these functions was hidden from the query plan of the calling query.

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

So, what's next?

There are several option to replace UDFs, such as Inline expressions for simple functions or derived tables if possible.

For example, if you are using simple T-SQL UDFs that do not touch any tables (meaning do not access data), make sure you specify the SCHEMABINDING option during creation of the UDFs. This will make the UDFs schema-bound and ensure that the query optimizer does not generate any unnecessary spool operators for query plans involving these UDFs.

Note that for UDFs that are schema-bound, any attempt to change the underlying objects’ schema will raise an error. Using this option ensures that the UDF will not inadvertently break due to changes of an underlying object’s schema.

Anything else?

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

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