Cost threshold of parallelism recommendations

Last Post 19 Jan 2007 09:14 AM by frivas. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
mddba
New Member
New Member

--
10 Nov 2006 05:57 AM
I have a SQL 2K server that runs both large and small queries. The most common Wait Type is CXPACKET. Both MAX DOP and the Cost Threshold for Parallelism are default ( 0 and 5). I am wondering if increasing the cost threshold to 600 will improve performance.

Does anyone have any recommendations on what I can monitor, aside from the query peroformance itself, to determine if this would be a good idea?

How can I determine which queries are using parallel processing plans?

Does anyone out there have any recomendations on either MAX DOP or the cost threshold?

Any help is appreciated...

THANKS!
StatisticsIO.com
New Member
New Member

--
19 Jan 2007 07:21 AM
I would not adjust the Cost Threshold for Parallelism setting yet but start off cutting the maxdop to have the number of processors and then monitor. If you still see excessive wait then drop it to one.
frivas
New Member
New Member

--
19 Jan 2007 09:14 AM
I used to have issues with the paralelism, so i always disabled it, give it a try the setting is taking on the fly and you won't need downtime, check the performance before and after and check the results.

Hope this helps.

FR.
You are not authorized to post a reply.

Acceptable Use Policy