June 13, 2012 at 10:04 am
I am posting here my questions again.. Thanks!
This is a 4 processor, NUMA based server, running both OLAP and OLTP databases (but no high volume transaction databases). I changed the threshold to 25 and ever since all sp's have behaved nicely. However, I am still experiencing queries generated by Cognos which get stuck for hours waiting for CXPACKET. Attached are the wait statistics, but, bear in mind, those numbers are mostly for the old setting, maxdop = 2 and thershold = 5. ( I just reset them).
1) CXPACKET waits are 40% of all waits, seems like that is a problem?
2) Should I start by setting parallelism settings to best practices values, that is, maxdop=4 and threshold = 5 (or should I leave it at 25, since it works for teh sp's)?
Any suggestions are appreciated.. thanks!!!
June 13, 2012 at 2:48 pm
Lets give it a shot!
Stay with the original settings. And try to repro.
Look at sys.dm_exec_requests and see what is the wait_type. If it is cx_packet, query sys.dm_os_waiting_tasks for the problematic session_id and see if you any wait types there other than CX_PACKET.
We can decide on the next course of the output based on this result.
Rojipt
June 13, 2012 at 3:23 pm
This is great post by Adam Machanic, I guess that's what I'll do, change settings and test...
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply