Lesson Learned #531: Scalar UDF vs Parallelism

Last week I worked on a support case where our customer reported that the exact same query, executed against two identical databases with the same resources, was taking significantly longer on one of them. Both databases had the same number of rows, up-to-date statistics, and identical indexes. 

We started by collecting the execution plans, and I’d like to share what we found.

Comparing both execution plans, in the XML of the execution plan that is taking more time, we found the following line in <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="TSQLUserDefinedFunctionsNotParallelizable">

However in the XML of execution plan that is taking less time we found <QueryPlan DegreeOfParallelism="1" ContainsInlineScalarTsqlUdfs="true">

So, based on this difference, it is clear that the query is using a Scalar UDF but in one of the database, based on the definition of this Scalar UDF function is not possible to run the query in parallel. But in the other database even using Scalar UDF it is possible. 

As both databases are using the same compatibility level of 160, we started to analyze what is different on both that leads to this behavior, sharing with you an example. 

DROP TABLE IF EXISTS dbo.TestData;
GO
CREATE TABLE dbo.TestData (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Value1 INT,
    Value2 INT
);
INSERT INTO dbo.TestData (Value1, Value2)
SELECT 
    ABS(CHECKSUM(NEWID()) % 10000),
    ABS(CHECKSUM(NEWID()) % 10000)
FROM sys.all_objects a CROSS JOIN sys.all_objects b
WHERE a.object_id < 150 AND b.object_id < 150;

Let's create the Scalar function that blocks the parallel execution.

CREATE OR ALTER FUNCTION dbo.fn_BlockParallel (@v1 INT)
RETURNS INT
AS
BEGIN
    DECLARE @x INT;
    SELECT @x = DATEDIFF(MILLISECOND, GETDATE(), SYSDATETIME());
    RETURN ISNULL(@x, 0);
END;

When I executed the following query I see in the XML file the following - <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="TSQLUserDefinedFunctionsNotParallelizable" CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="216">

SELECT 
    ID,
    dbo.fn_BlockParallel(Value1)
FROM dbo.TestData
WHERE Value1 > 100
OPTION (MAXDOP 4); 
GO

If I modified the code for a new Scalar UDF, I see: <QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="272" ContainsInlineScalarTsqlUdfs="true">

CREATE OR ALTER FUNCTION dbo.fn_BlockParallel (@v1 INT)
RETURNS INT
AS
BEGIN
    DECLARE @x INT;
    SELECT @x = v1 * 2;
    RETURN @x;
END;

So, even when using compatibility level 160, certain constructs inside scalar UDFs can prevent inlining, which in turn blocks query parallelism. When performance varies between environments, one of the things to check is whether scalar UDFs are involved, and if they are eligible for inlining.

To detect the issue quickly, look at the execution plan XML and check the attributes DegreeOfParallelism, ContainsInlineScalarTsqlUdfs, and NonParallelPlanReason.