Cost Threshold for Parallelism Values??
-
Here's the scenerio....
My SAP database started having issues CXPACKET locks about 4 weeks ago on a 4 proc box. I changed the 'max degree of parallelism' from 0 to 1 (no parallelism). The results of this change have been mostly positive.
- Avg response times are down
- No CXPACKET lock problems
- CPU utilization averages stayed the same
- Number of transactions/sec went up
I am having one problem though. The large heavy queries are running noticeably longer and am getting sap dumps. I am thinking about changing the parallelism back to 0 and setting the 'cost threshold' value to something higher than the default of 5. My hopes are to not use parallelism except for those large queries.
Does anyone have any suggestions on what values, or range of values to set the cost threshold too?
Thanks,
Jeff
"Keep Your Stick On the Ice" ..Red Green -
"Keep Your Stick On the Ice" ..Red Green -
We have exactly issues after installed SQL Server service pack 3a and hot fix 844 recommednded by SAP note. Database server performance decreased largely and we have seen waittype on I/O and CXPACKET. So I decided to adjust the "cost threshold" value from 5 to 10 and database performance became much better. SAP recommedned to disable query parallel execution by setting 'max degree of parallelism' from 0 to 1. We are still seeing some of large queries running slow and have to call users to cancel their transactions/jobs sometime. We are still working on those slow transactions.
Have you contacted SAP for resolution?
-
Allen...........It's the EXACT same story here. In fact I will share your post with my Basis Admin.
I asked Tom (see Basis Admin) to contact SAP regarding the cost threshold. We like to get SAP's input/approval first. I will keep you in the loop regarding our developments and please share the same with me.
Thanks!!
Jeff
"Keep Your Stick On the Ice" ..Red Green -
Sure.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply