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)', 'nvarchar(max)') "NATIONALITY" , a.XMLRECORD.value ('data(/row/c189)', '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.