Forced serialization on inline table valued function

Question

Apologies if this is a stupid question. We are attempting to convert our multi-statement table valued functions (MSTVFs) to inline table valued functions (ITVFs), to avoid the forced serialization of queries.

I have the following ITVF function (watered down), but the Execution Plan still says it has a Degree of Parallelism of 1. The actual function in question has three basic SELECT statements separated by UNION ALLs.

Have I missed something? How can I avoid forced serialization here?

CREATE FUNCTION dbo.Test (@i int)
RETURNS TABLE
AS
RETURN
SELECT @i as [i];
GO

SELECT * FROM dbo.Test (2);
GO

https://www.brentozar.com/pastetheplan/?id=Hyn8o50D7

The instance has he following settings:

• Cost Threshold for Parallelism = 5
• Max Degree of Parallelism = 0

Answer 1

Simply, your query doesn't have a high enough estimated cost.

SQL Server estimates how much effort a query will take, and reveals this unitless measure as estimated cost. If it's below your Cost Threshold for Parallelism, then SQL Server won't bother with a parallel plan. Your plan has an estimated cost of .129, which, since it is less than 5, means no parallelism.

Also in your plan XML is the detail that this query only took 1 millisecond. You have no need to go parallel. There's not enough work to justify the overhead of distributing effort across multiple threads.

As an aside for an earlier version of this question, even queries that would be TRIVIAL will go parallel if their estimated cost is high enough, and all the other conditions for parallelism are met.

A way to prove that something is preventing parallelism is NonParallelPlanReason="CouldNotGenerateValidParallelPlan" in the plan XML.

Another way to test (I'm assuming you're on SQL Server 2014, because 2016 has an actual hint to use) is to add OPTION(QUERYTRACEON 8649) to your query. Please just use that for testing though, because adding undocumented trace flags to production code is really stupid not a good thing.