Computed Column UDFs: Why Every Query on the Table Goes Serial

Computed column UDFs can keep every query on a table from using a parallel plan, even queries that never read the computed column. The hidden function call travels with the table.

The slow query that reads plain columns

Imagine a report that sums a plain integer column on a big table. It is slow, and the plan is serial. You raise MAXDOP. Nothing changes. You lower the cost threshold. Nothing changes. Then someone notices a computed column on the table that calls a scalar function. The report never touches that column. It does not matter.

Let me build that table and look at the plan reason.

Build the table with a hidden function

The demo uses a database called SqlAuthorityDemo and drops it at the end. The function adds one. The computed column Adjusted calls it. The table has two rows for now.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
CREATE FUNCTION dbo.AddOne (@v int) RETURNS int WITH SCHEMABINDING AS
BEGIN
    RETURN @v + 1;
END;
GO
CREATE TABLE dbo.UdfDemo (
    Id       int PRIMARY KEY,
    Value    int,
    Adjusted AS dbo.AddOne(Value)
);
INSERT dbo.UdfDemo (Id, Value) VALUES (1, 10), (2, 20);

Read the plan reason

Now sum the plain Value column. I turn on STATISTICS XML so SSMS gives you a clickable plan. In the plan, open the SELECT operator and look at its properties.

SET STATISTICS XML ON;
SELECT SUM(Value) AS ValueTotal FROM dbo.UdfDemo;
SET STATISTICS XML OFF;

SELECT is_inlineable
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(N'dbo.AddOne');

ValueTotal is 30. The properties say Degree of Parallelism 0 and NonParallelPlanReason TSQLUserDefinedFunctionsNotParallelizable. The second result says is_inlineable is 1. That means the function is eligible for inlining. It did not stop the restriction, so eligibility alone is not the cure.

Clicking through properties is slow. Here is a small view that reads the same facts from the plan cache, so you can check several queries at once.

CREATE VIEW dbo.PlanReasons AS
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT CASE WHEN st.text LIKE N'%dbo.PlainDemo%' THEN N'PlainDemo' ELSE N'UdfDemo' END AS TableName,
       CASE WHEN st.text LIKE N'%PARALLEL_PLAN_PREFERENCE%' THEN 1 ELSE 0 END AS Hinted,
       qp.query_plan.exist('//RelOp[@Parallel="1"]') AS IsParallel,
       qp.query_plan.value('(//QueryPlan/@NonParallelPlanReason)[1]', 'varchar(100)') AS NonParallelPlanReason
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE qp.dbid = DB_ID()
  AND st.text LIKE N'%SUM(Value)%'
  AND st.text NOT LIKE N'%PlanReasons%';

SELECT TableName, IsParallel, NonParallelPlanReason
FROM dbo.PlanReasons;

One row: UdfDemo, IsParallel 0, and the same reason. A tiny table is serial anyway, so this alone proves little. Let me make the test fair.

Compare with a table that has no function

I add 100,000 rows to UdfDemo and build PlainDemo, a twin without the computed column.

CREATE TABLE dbo.PlainDemo (Id int PRIMARY KEY, Value int);

INSERT dbo.UdfDemo (Id, Value)
SELECT value + 2, value % 100 FROM GENERATE_SERIES(1, 100000);

INSERT dbo.PlainDemo (Id, Value)
SELECT value, value % 100 FROM GENERATE_SERIES(1, 100000);

Now run the same grouped query on both. The hint asks SQL Server to prefer a parallel plan when it can. Your normal queries choose by cost, but this makes the comparison honest.

SELECT Value % 10 AS Bucket, SUM(Value) AS ValueTotal
FROM dbo.UdfDemo
GROUP BY Value % 10
ORDER BY Bucket
OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));
GO
SELECT Value % 10 AS Bucket, SUM(Value) AS ValueTotal
FROM dbo.PlainDemo
GROUP BY Value % 10
ORDER BY Bucket
OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

SELECT TableName, IsParallel, NonParallelPlanReason
FROM dbo.PlanReasons
WHERE Hinted = 1
ORDER BY TableName;

PlainDemo says IsParallel 1 with no reason. UdfDemo says IsParallel 0 with TSQLUserDefinedFunctionsNotParallelizable. The two queries read the same plain column. The only difference is the function inside the table definition.

Replace the function with a direct expression

If the logic is simple, write it directly. I drop the computed column and add it back as Value + 1, then rerun the test.

ALTER TABLE dbo.UdfDemo DROP COLUMN Adjusted;
ALTER TABLE dbo.UdfDemo ADD Adjusted AS Value + 1;
GO
SELECT Value % 10 AS Bucket, SUM(Value) AS ValueTotal
FROM dbo.UdfDemo
GROUP BY Value % 10
ORDER BY Bucket
OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));
GO
SELECT TableName, IsParallel, NonParallelPlanReason
FROM dbo.PlanReasons
WHERE Hinted = 1
ORDER BY TableName;

Now UdfDemo is parallel too, and the reason is gone. Changing a computed column is a design change, so try it on a copy first. Keep the input, indexes and settings the same, and compare results and plans. Also keep NULL and overflow behavior the same as the old function. A plain expression can behave differently at the edges.

Here is the cleanup.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Next time a query will not go parallel, read the plan reason before you touch any setting.

A computed column is not free metadata, it is a function call the plan must respect.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.