SQL Server not creating parallel plan with OPTION(MAXDOP 20)

Question

We have a UAT3 server hosted on VM with 8 sockets and 20 processer, we have similar UAT2 server hosted on the same VM with same configuration.

We are running a below query on both the server

select recid from Table1 where nation='AE'

both the server has same data and same structure.

UAT2 and UAT3 has default setting cost threshold for parallelism 5 and max degree of parallelism 0.

IN UAT2 server parallel processing is happening. And it is taking 10 sec to complete, but UAT3 serial processing is happening because of that it is taking 3 mins 30 sec.

We compare both the UAT2 and UAT3 server configuration both are same. Not sure why SQL Server is picking parallel execution in UAT2 but not in UAT3.

Below is the table definition

CREATE TABLE [dbo].[FKMB_CUSTOMER]( 
    [RECID] [nvarchar](64) NOT NULL, 
    [XMLRECORD] [xml] NULL, 
    [ALT_CUSTOMER] AS 
        ([dbo].[IX_CUSTOMER_ALT_CUSTOMER]([XMLRECORD])) 
        PERSISTED, 
    [SMS] AS 
        ([dbo].[IX_CUSTOMER_SMS_1]([XMLRECORD])) 
        PERSISTED, 
    CONSTRAINT [PK__FKMB_CUS__A9A5B3BB8FBCAAED] 
        PRIMARY KEY CLUSTERED ( [RECID] ASC )) 

And below is the view

CREATE VIEW V_FKMB_CUSTOMER as 
SELECT 
    a.RECID, 
    a.XMLRECORD "THE_RECORD" ,
    a.XMLRECORD.value
        ('data(/row/c28)[1]', 'nvarchar(max)') "NATIONALITY" ,
    a.XMLRECORD.value
        ('data(/row/c189)[1]', 'nvarchar(max)') "AUDIT_DATE_TIME" 
FROM "FKMB_CUSTOMER" a  

This is the plan.

We tried using OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) but no luck. Still the optimizer is picking the serial execution plan.

Answer 1

The execution plan you shared from UAT3 has this in it:

NonParallelPlanReason="CouldNotGenerateValidParallelPlan"

This means that the optimizer is prevented from creating a parallel execution plan for this query.

If you're getting a parallel plan on UAT2 with the same query, then something must be different between those environments. Some of the reasons for parallelism being inhibited are listed here:

Query Processing Architecture Guide - Parallel Query Processing

I would take a closer look to see what's different between the two environments (database scoped configurations, server level config, T-SQL code and table / object definitions, etc).

Answer 2

Your table definition includes scalar T-SQL functions in computed columns. These functions prevent parallelism unless trace flag 176 is enabled.

See Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?

The instance with a parallel plan either does not have those scalar functions, or has TF 176.

See also:

• A Computed Column Defined with a User-Defined Function Might Impact Query Performance
• Using Triggers to Replace Scalar UDFs on Computed Columns

Answer 3

There can be many reasons, why you are not getting the same plan on both the machines. Sometimes, using OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) hint instead of MAXDOP has helped to enable parallel plans.