[FEATURE] : alert in plan analysis when Standard Edition + ColumnStore = DOP limited to 2

[rferraton]

Which component(s) does this affect?

• Desktop App
• CLI Tool
• SSMS Extension
• Plan Analysis Rules
• Documentation

Problem Statement

It took me a while to understand a query where the DOP was limited to 2. This stemmed from a SQL Server Standard license limitation and the use of a columnstore, resulting in a 2-thread limit per license. Should this be included in the plan analysis, along with the reasons for the DOP limitation of 2?

Proposed Solution

Add this alert if detected : ColumnStore in use in the plan + DOP = 2 + Standard Edition = License Limitation

Use Case

Analysing Queries on a SE

Alternatives Considered

No response

[erikdarlingdata]

@rferraton this is a good general idea. We could only say for sure if it's a licensing limitation if the query has a connection to the server, of course. Otherwise, there could be any number of peripheral reasons for the DOP of 2. I would maybe extend this to Batch Mode operators generally, though. If this something you'd like to work on, or do you want me to pick it up?

[rferraton]

@erikdarlingdata Absolutely, even without a columnstore directly involved in the plan but indirectly leading to batch mode, the parallel query reaches a degree of parallelism (DOP) of 2 in Standard Edition. It's still surprising!

I thought we could mention this limitation, even without a server connection, with a message like:

"The DOP is 2 and batch mode is used in the plan. If your query originates from a SQL Server Standard Edition instance, you may be limited to 2 threads in batch mode."

I can take the feature

[erikdarlingdata]

@rferraton Sounds good! Thanks for taking it on.