I have some query with
CXCONSUMER wait time when i ran
sp_whoisactive as shown in the screenshot below.
When i checked the status it is
suspended.That means it is waiting for some process to release resource.As far as my understanding
CXPACKET is the producer and
CXCONSUMER is the consumer and CXPACKET is the main culprit and action should be taken on that.But i don't see any
Since this is a parallel execution i am not sure where i should start fixing.
Can anyone suggest me how to find out the process on which my queries are waiting(because i see suspended as status)?
When i query
sys.dm_os_wait_stats ,the top two entries are CONSUMER and CXPACKETS.
- SQL Server 2016
- 128 GB RAM
- MAX DOP : 8
- Cost of threshold : 20