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 THRESOLD OF 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.