Optimizing SQL Server Through the Parallelism Feature
There are many default values within SQL Server’s instance level properties that allow it to function well out of the box without extensive modification. Tweaking these values to your specific environment/workload can provide optimization and a deeper understanding of your system. Two features in particular, with slight modifications, can make a significant impact on your productivity. The default values of ‘MaxDoP’ and ‘Cost Threshold for Parallelism’ are both related to a SQL Server feature called Parallelism. This blog will cover an overview of Parallelism, specifics on the settings, and include a brief summary of recommendations on how to proceed with setting these values.
Parallelism
When SQL Server receives a request from NAV or any application it compiles several possible ways to execute the request. All of these execution possibilities or “plans” are considered and weighed against one another before given a cost. The cost has no unit; a higher cost means a more complex query. The SQL Server chooses the quickest plan and executes it. When a plan executes within SQL Server it uses the processor threads to carry out the action. This is where Parallelism comes in. When a server has Parallelism enabled and the cost is high enough, the executed queries on SQL Server can use multiple logical processors. Using more than one thread can speed things up considerably.
MaxDoP
MaxDoP stands for ‘Max Degree of Parallelism.’ This translates to the maximum number of logical processors that queries triggering Parallelism can recruit. By default, SQL Server comes with MaxDoP = 0, a 0 for this value means that every processor will be used by parallel queries. This value is far from ideal for one reason: CPU. Although queries can be completed faster using Parallelism there is additional CPU load associated with splitting the query out among different threads. A MaxDoP setting of 1 means that there will be no Parallelism or each query will be limited to a single logical processor. This is fine. But to truly optimize your resources it can be better. Any MaxDoP value over 1 will control the number of processors that parallel queries can utilize. A system with 10 cores could run with Parallelism at 8 or lower. One wouldn’t want the MaxDoP to equal or exceed the number of cores, again due to CPU concerns.
We’ve covered how to control the degree of Parallelism or the amount of processors a parallel query can use. But what does SQL Server use to decide that a query should go parallel? That’s the next value we will discuss, ‘Cost Threshold for Parallelism’.
Cost Threshold of Parallelism
Cost threshold of Parallelism didn’t get the acronym treatment from Microsoft, but I’m partial to CToP, so we will use that going forward. Earlier we discussed the costs that SQL Server gives each plan before selecting the fastest one. These costs range from 0 to 1000000 possibly even more). The default CToP is 5 which is relatively low. If a query is chosen as the fastest of the available query plans for a request and its cost is above the cost specified in CToP it will go parallel and be split out to the number of cores specified by MaxDoP.
Setting up these Values for a SQL Server Running NAV
The rule of thumb is to set MaxDoP to half the number of logical processors per NUMA node up to 8. A NUMA node is a physical CPU and its local memory. If your server has a single CPU seat with 12 cores, then you have a single NUMA node and MaxDoP should be set to 6 or less. If that same CPU has Hyperthreading enabled, then MaxDoP can be up to 8. For servers with multiple CPU seats, MaxDoP should still only be set to half the number of cores of one NUMA node up to 8. SQL Server 2016 and SQL Server 2014 SP2 (with Automatic Soft NUMA enabled) will handle how parallelism works in terms of only using cores on the same physical CPU, but for earlier versions of SQL, setting NUMA nodes has to be done via registry settings. For most systems, setting MaxDoP = 2 before monitoring performance for a week would be wise. Afterwards the value could be increased up to the rule of thumb max or set back to 1 (MaxDoP shouldn’t be set to 0 for NAV).
Next is the CToP (Cost Threshold of Parallelism) which is set to 5 by default. Many requests from NAV don’t need Parallelism so splitting them out on different threads would be a waste of resources. By increasing this value to 50 only those queries that are truly ‘expensive’ may trigger Parallelism.
Utilizing these Parallelism settings can increase performance. It’s also important to remember that both settings can be throttled down or increased easily from the instance level properties. One final note on Parallelism involves maintenance plans. One should add a reconfigure step so plans always run in parallel (sp_configure ‘max degree of parallelism’, 0 RECONFIGURE GO), maintenance plans just like it better that way. Take a look at your MaxDoP and Cost Threshold of Parallelism today and consider modifying them for optimization if you run into performance concerns.
For more information regarding SQL Servers and Microsoft NAV, please see our existing catalog of blog posts, or contact us to work one-on-one with a SQL Server/NAV expert.