SQL Server Do User-Defined Scalar Valued Functions still prevent parallelism?

2jcobegt  于 2023-08-02  发布在  Scala
关注(0)|答案(1)|浏览(105)

I'm currently reading a book about SQL Server 2014. It claims that User-Defined Scalar Valued Functions prevent parallelism for the entire plan that they appear in. Is this still true in later versions?

niknxzdl

niknxzdl1#

If the function is not inlined it still prevents parallelism.

TSQLUserDefinedFunctionsNotParallelizablestill exists as a NonParallelPlanReason in the execution plan for 2022.

If the function is inlined the query can go parallel.

There is an example of that here
Here are some key observations from the above plan ... SQL Server is now using parallelism across all operators.

Scalar function inlining has been available since SQL Server 2019 but only some functions meet the criteria for this and there have been some issues with the implementation of the feature meaning that the criteria for what can be inlined has become more strict .

It is still the case that if a computed column in a table references a scalar UDF then this blocks paralellism - irrespective of whether or not the UDF can be inlined however.

DROP TABLE IF EXISTS dbo.T

GO

CREATE OR ALTER FUNCTION dbo.F1()
RETURNS INT
WITH INLINE = ON
AS
BEGIN
    RETURN 1;
END;

GO

CREATE TABLE dbo.T
(
A INT
)

GO

--Parallel plan
SELECT SUM(dbo.F1())
from dbo.T t1 join dbo.T t2 ON t1.A = t2.A
option (use hint('enable_parallel_plan_preference'))

GO

--Add computed column referencing the function
ALTER TABLE dbo.T ADD B AS dbo.F1() 

GO

--Rerun the query. Now serial with "CouldNotGenerateValidParallelPlan" reason in 2019 and "TSQLUserDefinedFunctionsNotParallelizable" in 2022
SELECT SUM(dbo.F1())
from dbo.T t1 join dbo.T t2 ON t1.A = t2.A
option (use hint('enable_parallel_plan_preference'))

相关问题