March 16, 2006 at 11:05 pm
Hi All
Is it advisable to use MAXDOP parameter in SQL2000,SP4? If not, how can I set the degree of parallelism in SQL2000+SP4
Regards
Santhosh
March 17, 2006 at 3:48 am
Maxdop really depends on your application and your server setup. usually maxdop is set to use all processors and can be left on that setting.
i have come acrosss instances where queries "self block" (split up for parallelism and the 2 threads blcok each other - often the case with union queries against the same table) wqhere MAXDOP can be specified.
just to clarify there are 2 methods of using maxdop - the first is the server settig, which will be the maximum degreee of parallelism for ALL queries issued on the server - restricting thi smight not be a good idea if you have more than one database on the server as you may lose performance where other databases have large queries that benefit from a high maxdop
you can set this value in the server properties (CPU tab) in enterprise manager, or run the command (in QA) sp_configure 'max degree of parallelism',1 (to set the value to 1)
the other (my recommended solution) is to alter your query designs (if it's an in house app) so that query hints are used
for example
select b,count(a) from mytable where c='xyz' group by b OPTION (MAXDOP 1)
this would force the specific query to run in maxdop 1, but all other queries would (possibly) use the server default if the cost threshold for parallelism is reached
this is also an option that you might consider - if smaller queries are running using parallelism, but you still want to use this feature for larger queries and can't change your query code then try adjusting the query cost threshold to a higher value (doen in enterprise manager under the cpu tab) although i'd advise benchmarking performance before and after to see if there is a positive effect.
MVDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply