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)
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
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?