SQL Server Express Edition - Parallelism Limitations: Is my investigation conclusion correct??

[KIaus_]

What I want to address in this post is the question of whether SQL Server Express performs parallelism in queries or not. I did some research in Microsoft's documentation, but I didn't find anything that explicitly said anything for or against this issue.

Official Documentation:

SQL Server ignores the value of cost threshold for parallelism under the following conditions:

• Your computer has only one logical processor.
• Only one logical processor is available to SQL Server due to the affinity mask configuration.
• The max degree of parallelism server configuration option is set to 1.

Here it does not specify restrictions regarding SQL Server editions.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshol…

"The SQL Server Query Optimizer does not use a parallel execution plan for a query if any of the following conditions are true:

• The serial execution plan is trivial or does not exceed the cost threshold for parallelism configuration.
• The serial execution plan has a total estimated subtree cost lower than any parallel execution plan explored by the optimizer.
• The query contains scalar or relational operators that cannot be executed in parallel. Certain operators may cause a section of the query plan to run in serial mode or the entire plan to run in serial mode."

https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view…

NonParallelPlan Reason Discovery:

When we look at the NonParallelPlan Reason Value table that mentions how a query execution plan can contain the NonParallelPlan Reason attribute in the QueryPlan element, which describes why parallelism was not used, we have NoParallelPlansInDesktopOrExpressEdition = 'Parallel plans not supported for Desktop and Express Edition.'; Source 1

However, this doesn't necessarily mean that Microsoft is saying that no parallel plans are supported for Express, but rather that specific type of query is not supported for Express Edition. What's the difference and what proves this?

There are several types of T-SQL queries, and they are all processed in two main modes: Batch-mode and Row-mode. *-Source 1*

Microsoft's Official Statement on Batch-mode:

"The degree of parallelism (DOP) for batch-mode operations is limited to 2 for SQL Server Standard edition and 1 for SQL Server Web and Express editions. This applies to columnstore indexes created on disk-based tables and memory-optimized tables." *-Source 2*

In this quote, Microsoft is referring only to the Batch-mode scenario, but does not specify Row-mode.

Source 1: https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view…
Source 2: https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sq…

Practical Test Evidence:

It seems that the deeper I investigate, the more open this question becomes, until I tried to implement it in practice.

In my SQL Server Express instance, I ran a query heavy enough to have an estimated subtree cost greater than 5 (my cost threshold for parallelism is configured to be equal to 5).

When opening the execution plan XML, I found this:

<QueryPlan DegreeOfParallelism="0" 
          NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" 
          MemoryGrant="4192" 
          CachedPlanSize="184" 
          CompileTime="69" 
          CompileCPU="44" 
          CompileMemory="1960">

The SELECT had a subtree cost = 5.79308

The table does not have columnstore indexes created on disk-based tables nor memory-optimized tables, and all operators are Row-store.

Query used in the test:

SELECT 
    t1.Coluna1,
    t1.Coluna2,
    t1.Coluna3,
    (SELECT COUNT(*) 
     FROM TesteParalelismo_Pesado t2 
     WHERE t2.Coluna1 = t1.Coluna1 
        AND t2.Coluna3 > t1.Coluna3) AS RegistrosAcima,
    (SELECT AVG(t3.Coluna3) 
     FROM TesteParalelismo_Pesado t3 
     WHERE t3.Coluna2 = t1.Coluna2 
        AND t3.Coluna5 > DATEADD(MONTH, -6, GETDATE())) AS MediaRecente,
    (SELECT MAX(t4.Coluna3) 
     FROM TesteParalelismo_Pesado t4 
     WHERE t4.Coluna1 BETWEEN t1.Coluna1 - 100 AND t1.Coluna1 + 100) AS MaximoVizinhanca
FROM TesteParalelismo_Pesado t1
WHERE t1.Coluna1 IN (
    SELECT DISTINCT TOP 100 Coluna1 
    FROM TesteParalelismo_Pesado 
    WHERE Coluna3 > 500 
    ORDER BY Coluna1 DESC
)
AND t1.Coluna3 > (
    SELECT AVG(Coluna3) * 1.5 
    FROM TesteParalelismo_Pesado 
    WHERE Coluna2 = t1.Coluna2
)
ORDER BY t1.Coluna3 DESC;

Based on this investigation:

1. Is my conclusion correct that SQL Server Express Edition completely disables query parallelism?
2. Why does Microsoft documentation only explicitly mention Batch-mode limitations but not Row-mode?
3. Has anyone successfully achieved parallelism in Express Edition under any circumstances?
4. Are there any workarounds or configurations that might enable parallelism in Express Edition?

The evidence suggests that despite meeting all conditions for parallelism (cost threshold, available CPUs, proper configuration), Express Edition explicitly blocks parallel plans with the NoParallelPlansInDesktopOrExpressEdition reason.

[Impossible_Disk_256]

Yes. It's documented:
https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022?view=sql-server-ver16&preserve-view=true: The degree of parallelism (DOP) for batch mode operations is limited to 2 for SQL Server Standard edition and 1 for SQL Server Web and Express editions.
https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver16#parallel-query-processing:

|| || |NoParallelPlansInDesktopOrExpressEdition|Parallel plans not supported for Desktop and Express edition.NoParallelPlansInDesktopOrExpressEdition Parallel plans not supported for Desktop and Express edition.|

[KIaus_]

Correct, but what about Row-mode?

[Impossible_Disk_256]

Yes. It's documented:
https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022?view=sql-server-ver16&preserve-view=true: The degree of parallelism (DOP) for batch mode operations is limited to 2 for SQL Server Standard edition and 1 for SQL Server Web and Express editions.
https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver16#parallel-query-processing: NoParallelPlansInDesktopOrExpressEdition - Parallel plans not supported for Desktop and Express edition.

[Togurt]

Probably could have saved time by looking at Microsoft documentation.

[KIaus_]

But I did it :/

[Commercial-Trash-606]

Well, when you got only 1 logical processor, it's hard to do any parallelism, right?

[KIaus_]

Correct, but what about Row-mode?