SQL Server MAX DEGREE OF PARALLELISM
The check for MAX DEGREE OF PARALLELISM has be added to the Database Health Monitor – Quick 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:
- Database Health Monitor
- Database Health Monitor Download
- Quick Scan Report
- MAX DEGREE OF PARALLELISM
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
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.
Yes that is correct, Cost Threshold For Parallelism goes hand in hand with the Max Degree of Parallelism. I added another post for the Cost Threshold for Parallelism, and added a link to in in the posting above.
http://stevestedman.com/2016/03/sql-server-cost-threshold-parallelism/
-Steve