SQL Server MAX DEGREE OF PARALLELISM

Download PDF

The check for MAX DEGREE OF PARALLELISM has be added to the Database Health MonitorQuick Scan Report.

The max degree of parallelism setting in SQL Server is used to control the maximum number parallel threads available to process your query. The default setting of 0 means use as many as possible. The problem with this default is that you may end up with way more parallel threads working on your query than you have processors available. When this happens you can end up with thrash as these multiple tasks are being swapped between your processors or cores.

If your server has 4 cores, you could use the following script to set max degree of parallelism to match your 4 cores.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 4;
GO
RECONFIGURE WITH OVERRIDE;
GO

The recommendation is to set this to match the number of cores on your system, or the maximum number of cores in any NUMA node.

Setting this to 1 tells SQL Server to disable parallel processing, and is generally not recommended.

 

If you are adjusting Max Degree of Parallelism you may also want to take a look at adjusting the Cost Threshold for Parallelism at the same time.

If you haven’t tried Database Health Monitor, now might be a good time to give it a try. Its a free application created by StedmanSolutions, LLC, and available at http://DatabaseHealth.com

Related Links:

Tagged with: , , , , ,
2 comments on “SQL Server MAX DEGREE OF PARALLELISM
  1. Also important is the “Cost Treshold for Parallelism”, which is the minimum value for switching to parallel processing. This value is 5 by default, but this was determined in 1991! With the current hardware, I recommend to start with a value 50 to get a grip on parallelism. You can measure if parallelism is hurting your performance by looking at the famous CXPACKET waitstat. Note: parallelism is NOT a bad thing, but it should only happen on complex queries.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.