quote:

---

Originally posted by tkizer

Why do you have both OLAP and OLTP databases on the same server? Generally they do better with different MAXDOP settings.
Good question... That's how it was set up long before I started here (3 months ago).

I wouldn't say that MAXDOP=4 is the best practice here. Do you have an article that shows that?
Here's a reply by Jonathan Kehayias to an article of Dave Pinal, where Dave was suggesting setting MAXDOP to 2 for a mixed environment and cost of threshold to 25.

http://blog.sqlauthority.com/2011/02/06/sql-server-cxpacket-parallelism-usual-solution-wait-type-day-6-of-28/

reply:
CXPACKET waits aren’t necessarily bad, or even a sign of a problem, they are normal and expected if queries are executing using parallelism. I wouldn’t recommend that CXPACKET alone ever be considered a reason to reduce or change the configuration of the ‘max degree of parallelism’ configuration option. Most newer servers are going to be NUMA based systems, and the best practice recommendation for this configuration option is to set it to the number of processor cores in a single NUMA node. Generally speaking this best practice is not followed, and most recommendations show changing the ‘max degree of parallelism’ configuration option to 1 or 2 whenever the topic of CXPACKET comes up.

My recommendation, especially on newer NUMA based systems is always to first set it following best practices to the number of physical cores in a single NUMA node and then monitor. You can find the number of schedulers in each NUMA node by querying the online_scheduler_count from sys.dm_os_nodes. Even for data warehouse environments, this should be set initially following this practice unless testing has shown that leaving it at 0 is actually best, which is possible.

After making the change monitor not just wait types, but also for how the system is performing. Look for queries that run under parallelism and test them manually using different levels of DOP using the OPTION(MAXDOP n) query hint to see if reducing parallelism actually improves or harms performance. You might find that reducing it for one query improves performance while the rest of the workload shows a performance decrease from that same tested reduction. In that case putting the query hint in, either as a plan guide for the individual query, or by changing the code if you have access, would yield better returns.

Raising the cost of threshold to 25 has definitely helped, I don't have any more problems with sp's, only once in a while a Cognos-generated query hangs for hours in a CXPACKET wait. I guess I'll wait to see what teh wait statitics show tomorrow, I have just reset them this morning.


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

quote:

---

Originally posted by jackv

CXPPACKET is normal in a multi core environment. The tkizer observation is a good one re: OLTP\DSS . You've switched the maxdop which has solved some problems , but maybe caused others.
No, maxdop is the same, I raised the cost of threshld to 25 and it has helped, but it has not solved the problem entirely.

Try: 1) identify which queries are running in parallelism and why?
Are they frequent?
No, they are not frequent any more, I only caught one yesterday. Identifying them is almost impossible, as those are Cognos-generated (users drag and drop columns in Cognos)

2)OLTP normally works better with lower parallelism setting, consider lowering parallelism - having first identified \ adjsuted (?) some queries

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

---

quote:

---

Originally posted by jackv

Marius , analyse the cached plans for queries using parallelism

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

---