SQL Server - Using MAXDOP and other processor settings - Part 1

After discussing about memory settings in my previous post, comes next set of important settings available which are related to processor.

Just like memory settings SQL Server has provided several options, which we can tweak to get an optimal performance from the processor(s) hooked to a SQL based Server.

In first part of this topic I would write about the most important option MAXDOP, which is short for "Max(imum) Degree of Parallelism". This parameters takes a numeric value, which is equivalent to the number of processor(s) SQL Server can use in parallel in order to process a resource heavy query. 

The default value for this option is "0" which means SQL Server will use all available CPUs (or CPU Cores) in case it chooses to go for parallelism. Yes, you read that right "if SQL Server chooses to". Some of you may get surprised to know this but SQL Server doesn't always go for parallelism for each query. There is a threshold value defined for this option and every query which crosses this threshold value qualifies to go for parallelism. This option is known as "Cost Threshold for parallelism" and it also takes numeric values which is equivalent to number of seconds with default value as 5 seconds. 

So, if go by this option and the default value provided, SQL Server will evaluate each query before its execution and all queries which it thinks is going to take more than 5 seconds to execute, will go for parallelism. Users\DBAs can change this values if required and depending upon the type of usage of database you should as well.

Now, let us talk about the best practices for using MAXDOP values. As I mentioned above, changing MAXDOP can have an impact on performance of queries. Following are the recommendations from Microsoft for using MAXDOP in general:

1. If you have more than 8 CPUs (or CPU cores) than keep this value at 8.
2. If you have 8 or less than 8 then you can keep it between 0-n, where "n" is the maximum number of CPUs available.
3. In case of hyper threaded CPU keep MAXDOP should not exceed number of actual physical cores.
4. In case of NUMA modes keep MAXDOP should not exceed number of actual physical cores assigned to the given node.

Yes, reducing MAXDOP value can improve your performance. Keeping it at default "0" means in case a heavy query comes and it goes for parallelism it will try to consume all available cores making other queries to wait. Also, in case of a very heavy query which needs to read a lot of data (let say in GBs), then there will be a lot of context switches which will happen in order to read page headers, process the data and all other similar operations increasing execution time of the query itself. Though parallelism is good thing but cases like these shows that a high number of MAXDOP doesn't always guarantee a smaller execution time and hence these recommendations were formed. So, if you're seeing a lot of CXPACKET waits you can consider changing these options.

Although some of the options can be followed safely, however options like setting MAXDOP between 2-8 (if you have CPU less than 8) or changing value for Cost of threshold for parallelism should be tested first in pre-production environment to avoid any surprises. Also keeping the MAXDOP at 1 ignore the threshold value because simply it isn't going for any parallelism here.

MAXDOP values which we set at instance levels are default values and these can be overridden at runtime by either using MAXDOP query hint or resource governor. However, for a query falling in any of the Resource Governor groups then the MAXDOP query hint values cannot cross the CPU restriction implied by Resource Governor and the query will be bound to use that many CPUs only as mentioned in the Group settings.

Stay tuned for part-2 of this topic which is coming in very soon.

For more on Microsoft's recommendations on MAXDOP usage please follow this link.