June 13, 2012 at 9:28 am
I am trying to troubleshoot erratic query run times on this server. I started by noticing that some sp run between < 1 minute and 4-5 hours at a time. Then, I noticed CXPACKET waits. MAXDOP was set to 2, cost threshold for parallelism set to 5. 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 9:30 am
Please don't cross post. Address replies to this thread http://www.sqlservercentral.com/Forums/Topic1310604-391-1.aspx
Jared
CE - Microsoft
June 13, 2012 at 9:37 am
I just thought the previous post was more about index tuning (initially), and now my problem has turned into a performance tuning issue, that's why I posted it separately. Less to read!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply