Why is this query not a parallel plan

Question

为什么不并行.xml

The max degree of parallelism of an instance is 0 and the cost threshold for parallelism is 5.

print @@VERSION：
	Microsoft SQL Server 2019 (RTM-CU27-GDR) (KB5040948) - 15.0.4382.1 (X64) 
	Jul  1 2024 20:03:23 
	Copyright (C) 2019 Microsoft Corporation
	Enterprise Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19045: )

The actual plan is serial, and the total cost is 18.2165. After adding the query hint, the found parallel plan should be used regardless of whether the cost of the found parallel plan is higher than 18.2165. I can only think of one possibility, because there's some sort of prohibition against parallel plans, and the reason would appear in the xml NonParallelPlanReason, but it doesn't appear in this plan.

May I ask why?

Because the Chinese area post always because of violation is deleted by the system, and I do not know where the violation, so use the translator to translate into English in the English area, the translation quality may not be good, please forgive me

Answer 1

My theory is that the string_agg function blocks parallelism. There is no NonParallelPlanReason in the XML, but when I try this query on a database that I have, I get a parallel plan:

 SELECT MIN(convert(nvarchar(MAX), ShipAddress)) 
 FROM Orders  
 WHERE OrderID > 170000
 OPTION (MAXDOP 32, QUERYTRACEON 8649)

but when I change it to

SELECT STRING_AGG(convert(nvarchar(MAX), ShipAddress), ',') 
 FROM Orders  
 WHERE OrderID > 170000
 OPTION (MAXDOP 32, QUERYTRACEON 8649)

I do not.

Answer 2

Hi @博雄 胡

Please check if the server-level setting for maximum degree of parallelism override query-level hints.

Refer to this tech doc: Configure the max degree of parallelism (server configuration option)

You can override the max degree of parallelism server configuration value:

• At the query level, using the MAXDOP query hint or Query Store hint.
• At the database level, using the MAXDOP database scoped configuration.
• At the workload level, using the MAX_DOP Resource Governor workload group configuration option.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".