I have an issue where by a particular SP when executed, takes more than 5 minutes to execute on a Production system. Interestingly, the same SP when executed in an UAT environment takes less than 5 seconds to complete.
The servers are similar in terms of set-up/infrastructure. I've inspected the execution plan for both and can see that Production system uses Parallelism to execute the query, UAT doesn't.
Instance settings for Parallelism on both servers are the same (default).
Just to add on the server where I'm seeing the issue a high number of waits on CXPACKET are seen, where there all in a 'suspended' state, I have queried sys.dm_os_waiting_tasks for the session_id that's executing the SP (that has the CXPACKET waits), to try and establish the non wait_type of CXPACKET, to see what is being waited on, but this returns nothing.
Could someone suggest please what the probable causes could be here? as to why I'm seeing such a high number of CXPACKETS in a suspended state? I can't see what they're waiting on!