max degree of parallelism - When to change it
'max degree of parallelism' by default is 0, which means the optimizer will use all available processors if it feels a query would benefit from parallelism.That sounds great, but consider the following scenario:
A server is used mainly for OLTP with a few reports being run during the day, some of which seem to take longer than expected and hog resources while they run. In my experience the situation I am about to outline occurs mainly when the server is highly stressed, and when running SQL Server 2000. This may occur with SQL Server 2005, but I have not yet observed it.
What happens is that the optimizer decides that the reporting queries would have a lower cost if they used parallelism, when this quite often is not the case. A long running query using all CPUs has a detrimental effect on the OLTP queries and updates due to the way the User Mode Scheduler is implemented and the OLTP queries and updates slow down.
My preferred solution is to set 'max degree of parallelism' to 1. The effect of this can be measured against specific queries beforehand by running them in Query Analyzer as is, then again with OPTION (MAXDOP 1) appended to the query.
You can change the value using sp_configure, or through Enterprise Manager, and it takes effect immediately. Be aware that cached plans will have to be recompiled, so there may be a small performance hit initially.
Please remember that for each system where this improves performance there will be another 10 where it makes things worse. Don't automatically make the change just because you have read it here; always test changes in your own environment.
Not found what you're looking for?
Use this search box. It is tuned for SQL Server searches. Try it and see!