Scalar Functions Execution Plan: Why SQL Goes Serial

5 min read

A scalar user-defined function can force a serial execution plan because the per-row function call is opaque to the optimiser unless the function is inlineable. Check the actual plan for a Compute Scalar or user-defined-function operator, then read sys.sql_modules.is_inlineable and the database compatibility level.

The examples below run on SQL Server 2019 CU18 against AdventureWorks. They use SET STATISTICS XML, SET STATISTICS TIME, SET STATISTICS IO, and sys.sql_modules.

On this page
  1. What the execution plan shows for a scalar UDF
  2. Why a scalar UDF forces a serial plan
  3. Reproducible demo: capture the plan and read the nodes
  4. Before/after benchmark: UDF vs inline vs TVF/join
  5. Compatibility level and is_inlineable matrix
  6. Diagnosis checklist when a plan ignores your expectations
  7. FAQ
  8. Related reading
  9. Sources

What the execution plan shows for a scalar UDF

The scalar UDF execution plan usually shows two distinct signals: an invocation operator and an expression the optimiser could not cost properly. An actual execution plan captures what the server did; an estimated execution plan captures what the optimiser predicted. Both are useful, but a scalar UDF muddies the estimate because the scalar operator has no proper cost at optimisation time. In the showplan operator reference, Compute Scalar is an operator that evaluates an expression, and a scalar UDF appears through a user-defined function operator. The actual plan adds row counts and execution counts, but it still does not break down the per-row cost of the function.

When you compare estimated rows with actual rows on the Compute Scalar or UDF operator, a mismatch often points to a cardinality estimate problem. Do not assume a serial plan is the same as a bad plan. First record whether the mismatch comes from the function shape or from missing statistics on the columns the function reads. Then check the function definition before you test any MAXDOP setting.

Why a scalar UDF forces a serial plan

A scalar UDF runs once per qualifying row. The engine invokes it iteratively, and it does not cost scalar operators during optimisation. The function also executes as a batch of statements, statement by statement, so the query optimiser cannot see inside the function body. Those two behaviours make the function largely opaque at plan selection time. The stricter behaviour is serial execution: SQL Server does not allow intra-query parallelism in queries that invoke UDFs. That is the SQL Server scalar function parallel plan problem: a query that could use a parallel plan becomes serial because of one function call.

This is why a query with MAXDOP 8 can still run single-threaded. SQLPerformance.com notes that user-defined functions help on very few occasions. Do not use OPTION (MAXDOP 1) as a universal solution. Inlining changes the picture: the function body becomes a scalar expression or scalar subquery, the UDF operator disappears from the plan, and the query optimiser can cost the work and consider a parallel plan.

Reproducible demo: capture the plan and read the nodes

Run this demo on SQL Server 2019 CU18 against an AdventureWorks copy.

USE AdventureWorks2019;
GO

CREATE OR ALTER FUNCTION dbo.LineTotalScalar
(
    @UnitPrice MONEY,
    @OrderQty  SMALLINT,
    @UnitPriceDiscount MONEY
)
RETURNS MONEY
AS
BEGIN
    RETURN @UnitPrice * @OrderQty * (1.0 - @UnitPriceDiscount);
END;
GO

SET STATISTICS XML ON;

SELECT
    sod.SalesOrderID,
    dbo.LineTotalScalar(sod.UnitPrice, sod.OrderQty, sod.UnitPriceDiscount) AS ExtendedLineTotal
FROM Sales.SalesOrderDetail AS sod
WHERE sod.SalesOrderID = 43659;

SET STATISTICS XML OFF;

Open the XML output in SQL Server Management Studio and view it as a graphical plan. Read the plan from the rightmost leaf to the leftmost root. Look for a Compute Scalar node and the user-defined function operator. The actual plan includes ActualRows and ActualExecutions; when the function runs once per row, the UDF operator shows repeated executions. Compare that count with the estimated number of rows. The estimated operator cost is often a small fraction of the actual row-level CPU, so the plan alone understates the function.

The same query without the UDF shows the expression evaluated inside a relational operator. That simpler plan is the baseline you will use in the next section.

Before/after benchmark: UDF vs inline vs TVF/join

The table compares plan structure only; numeric columns stay "Not published" until you run the script. Run each variant with SET STATISTICS TIME, IO ON on SQL Server 2019 CU18 and record the measured CPU time, elapsed time, and logical reads in the numeric columns for your workload.

VariantPlan operatorParallel eligible?CPU msElapsed msLogical reads
Scalar UDF dbo.LineTotalScalarCompute Scalar plus user-defined function operatorNoNot publishedNot publishedNot published
Inline expressionCompute Scalar or relational expressionYes if the optimiser chooses itNot publishedNot publishedNot published
Single-statement TVF or joinRelational join/stream operationsYes if the optimiser chooses itNot publishedNot publishedNot published

The structural change is what matters first. With inlining, the query plan no longer has a user-defined function operator, and the optimiser sees the expression in the calling query. When you rewrite to an inline expression or a single-statement table-valued function, the work moves into relational operators that the optimiser can cost. A serial plan is not automatically wrong; the problem is a serial plan forced by an opaque function call when the workload could use a parallel plan.

If the write workload behind the query also suffers from physical row movement, review page-split behaviour in write-heavy queries before tuning the function.

Compatibility level and is_inlineable matrix

Scalar UDF inlining applies to SQL Server 2019 (15.x) and later versions, according to the Scalar UDF Inlining article. The feature transforms scalar UDFs into scalar expressions or scalar subqueries during query compilation when the function body and the calling query meet the requirements.

SQL Server versionScalar UDF inliningKey check
2008 (10.0)NoNo is_inlineable column; serial UDF behaviour applies
2016 SP1 (13.0)NoBehaviour differs before 2016 SP1; no scalar UDF inlining
2019 (15.x), compat level 150Yes, requirements metsys.sql_modules.is_inlineable = 1
2022 and 2025Yes, inherited from 2019sys.sql_modules.is_inlineable = 1

Use this query to check a function:

SELECT OBJECT_NAME(object_id) AS function_name,
       is_inlineable,
       inline_type
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.LineTotalScalar');

The sys.sql_modules view returns is_inlineable as 1 when the function can be inlined, and inline_type as 1 when inlining is turned on. If the function still cannot be inlined, check the requirements in the inlining article; a function with procedural constructs may need a manual rewrite.

Diagnosis checklist when a plan ignores your expectations

Run through these checks in order:

  1. Capture the actual XML plan and locate the user-defined function operator.
  2. Query sys.dm_exec_function_stats to see how often the function executed and how much CPU it used.
  3. Check sys.sql_modules.is_inlineable and inline_type.
  4. Confirm the database compatibility level. Scalar UDF inlining for 2019 requires compatibility level 150.
  5. Inspect the plan cache for serial plans that repeat the same UDF call.
  6. Rewrite one variant as inline expression or TVF and measure with SET STATISTICS TIME, IO.
  7. If blocked-process reports appear, collect them with event notifications for blocked-process reports before dropping the function.

FAQ

Why does my query with a scalar function run serially?

A scalar UDF is opaque to the optimiser and blocks parallelism unless it can be inlined.

How do I check whether my scalar UDF can be inlined?

SELECT OBJECT_NAME(object_id), is_inlineable FROM sys.sql_modules WHERE object_id = OBJECT_ID('dbo.MyFunction');

Does SQL Server 2019 always inline scalar UDFs?

No; only when the function meets inlining requirements and is_inlineable = 1.

What is the fastest replacement for a scalar UDF?

Usually an inline expression, a single-statement TVF, or a join/CTE; benchmark before choosing.

What does Compute Scalar mean in a UDF execution plan?

It evaluates an expression but does not expose the per-row cost of the function.

Related reading

page-split behaviour in write-heavy queries, event notifications for blocked-process reports, and sqlblogcasts.com editors.

Sources

  • Microsoft Learn — Scalar UDF inlining (SQL Server)
  • Microsoft Learn — sys.sql_modules (Transact-SQL)
  • Microsoft Learn — Logical and physical showplan operator reference
  • SQLPerformance.com
SQL blogcasts

SET_NOCOUNT_ON

Technical notes on SQL Server for DBAs and developers: why a query or job behaves as it does and how to fix it, with T-SQL that runs and the version it was tested on.

More notes

Go up