Cost threshold for parallelism and wait stats We have an Stored Proc on our prod server (SQL Server 2014) that takes 3hr 40m (on a Sunday: low server activity) to run (collection job costs on 220 jobs with each requiring 6 SP calls, so 13200 SP calls and then some aggregating)

On my laptop with cost threshold set to default of 5 it runs in 30 mins. If I set cost threshold to 50 on my laptop it goes to 1hr 10mins (1/3 of time it takes in prod server)

If i set cost threshold on prod server to 25 it drops to 1hr 30mins, but wait stat go through the roof. See attached: green is threshold of 50, yellow is threshold of 25


Top waits are CXPACKET and SOS_Scheduler_Yield



I don't have a comparable dev or staging server (CPU's, RAM and Disk), so I play too much with a bunch of settings while i replay a workload

Tow questions


Am I better off with a cost threshold of 25 and the high wait stats and 60% performanc gain. Or will the wait stats cause a lot of issues when the load increases
Any thoughts on where the bottleneck is and how to solve. my uneducated guess would be CPU (from the blue line in the charts, it's not too high (under 70%) but is it that it can't serve enough threads in parallel?
SOS_SCHEDULER_YIELD and CXPACKET indicates that you’ve got contention and that queries are spending a lot of time waiting to get access to a CPU.
Check MAXDOP and Cost Threshold for Parallelism to determine when the query should be parallelized.