2

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 enter image description here

Top waits are CXPACKET and SOS_Scheduler_Yield

enter image description here

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

  1. 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

  2. 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?

Improve this question
7
  • Is this sat on a server with more than one NUMA node? Specifications of the server itself would help here. How up to date are your statistics and indexes?
    – George.Palacios
    Commented Jun 20, 2017 at 11:34
  • 3
    Parallelism waits will naturally increase with more parallel queries and parallel threads. These CXPACKET and SOS_SCHEDULER_YIELD waits themselves are not a concern, although the CPU consumed by these queries will reduce resources available to other queries. If you want to improve throughput more, address the next wait stat (ASYNC_NETWORK_IO).
    – Dan Guzman
    Commented Jun 20, 2017 at 12:04
  • @George.Palacios our serve ris in two processor Xeon ES-2670 @ 2.6ghz. 64 bit OS 133Gb RAm (max memory in SQL is 112GB. all the indexes have auto stats = true, so that would mean they are up to date, right?
    – mark1234
    Commented Jun 20, 2017 at 13:25
  • @DanGuzman. for this test I ran locally so the this particular one shouldn;t be afffected by network. That said what's best way to reduce it? We have 10gps connection a the switch
    – mark1234
    Commented Jun 20, 2017 at 13:28
  • 1
    @mark1234 It may be worth looking at setting your MAXDOP server level setting to the number of cores on an individual NUMA node - I've seen this cause slowdowns. In addition you should check statistics on the relevant tables - this can throw the engine off and cause unnecessary CXPACKET waits - ensure these statistics are up to date.
    – George.Palacios
    Commented Jun 20, 2017 at 14:35

1 Answer 1

Reset to default
-2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.