Max Degree of Parallelism is an important setting in a multi processor environment. A value of 0 indicates that all processors will be involved in the query execution process of large queries which consume a lot of cpu. This can sometimes cause issues on the server and result in degraded performance.
This setting can be changed at the server level by setting the value to 1, which will prevent parallel execution of all queries fired.
Parallel execution does result in execution plans which cause queries to run faster. So ,turning it off at the server is not advisable in most scenarios.Parallel Execution also covers up for poorly indexed databases.
The main disadvantage of parallel execution is it increases cpu utilization.
However we can always control parallel execution for specific queries by using the MAXDOP hint . For example :
SELECT pk_id, employee_name
ORDER BY pk_id, employee_name
OPTION (MAXDOP 1)
Recently there was a scenario where we migrated from windows 2003 x32 server to windows 2008 x64 server. Although the later is a powerful machine we had performance problems for a sql job which was running on it. This job was basically executing a stored procedure which had a complex query with large number of UNIONs.
The query was somehow taking abnormal amount of time to execute on the new server.
The Activity Monitor inside SQL server showed high wait time during the execution of this job.
The work around for this was using the MAXDOP hint. Using OPTION (MAXDOP 1) the execution time was drastically reduced on the new server and the wait time problem was eliminated.