setting maxdopt and cost_threshold

jmurry 2015-02-12 20:13:52

Good afternoon,

We are having an issue with performance on one of our servers. Under “sql server waits” parallel queries shows 30,000 – 60,000 ms waits. We have two eight NUMA
Nodes and 48 gig of ram for sql. We have the maxdopt set to 4 and cost_threshold set to 50. Do I need to raise maxdopt to 8 so that the queries that are running parallel
Can finish quicker. These values will go down after the next refresh of SQL Sentry, so they are not staying at those high waits all the time, but when I run some of the
Performance reports, parallel queries and cxpacket waits are at about 73% for both. This is a purchased package and we have the vendor looking at the queries/indexes
To see if they can be tuned, but I was wanting to try and get the maxdopt and cost_threshold parameters set correctly and any suggestions would be appreciated.