More CPU’s in SQL Server : Does it Help or Hurt? (MAXDOP)

More CPU’s in SQL Server : Does it Help or Hurt? (MAXDOP):
Common sense tells us , having more CPU’s mean more processing power , that means things would be done faster.
Hold on for a sec..Its SQL Server. Common Sense isn’t applicable here. PUN intended.

The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. This option determines the computing and thread resources that are used for the query plan operators that perform the work in parallel. Depending on whether SQL Server is set up on a symmetric multiprocessing (SMP) computer, a non-uniform memory access (NUMA) computer, or hyperthreading-enabled processors, you have to configure the max degree of parallelism option appropriately. TheĀ general guidelines that you can use to configure the max degree of parallelism option for SQL Server when you use the sp_configure system stored procedure.

This article explains what should be MAXDOP. Just like anything in life , it depends. It depends on what?.

Based on Microsoft article, it depends on
1.SQL Server is set up on a symmetric multiprocessing (SMP) computer, a non-uniform memory access (NUMA) computer
2. Hyperthreading-enabled processors
3. For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.

Form the Microsoft Article :
SQL Server 2005 and later versions
For servers that use more than eight processors, use the following configuration:
MAXDOP=8
For servers that use eight or fewer processors, use the following configuration:
MAXDOP=0 to N

Note In this configuration, N represents the number of processors.
For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
For servers that have hyperthreading enabled, the MAXDOP value should not exceed the number of physical processors.
For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.

You get some meaningful information from Microsoft and then you configure MAXDOP based on the recommendations or guidelines provided.
Then you notice some of the queries used to milliseconds takes seconds. You scratch your head and wonder what happened?.

There are projects, deadlines, deliverables and this is the last thing you want to worry about. As a member of Project Team, your team expects you figure out things and make sure things run smoothly.You ask yourself, I did my research and I configured my MAXDOP.
When it comes to figuring out MAXDOP is helping or hurting, I noticed there are two query plans for some of the stored procedures. One for executing parallel queries , one for non-parallel operation.

 

SQL Server 2005 and later versions

Server with single NUMA node Less than 8 logical processors Keep MAXDOP at or below # of logical processors
Server with single NUMA node Greater than 8 logical processors Keep MAXDOP at 8
Server with multiple NUMA nodes Less than 8 logical processors per NUMA node Keep MAXDOP at or below # of logical processors per NUMA node
Server with multiple NUMA nodes Greater than 8 logical processors per NUMA node Keep MAXDOP at 8

MAXDOP Recommendations Query

List of Stored Procedures using Parallelism:

After figuring out the most used queries, I try to execute it with different MAXDOP Options and analyze our query patterns.
Most of the times, not doing anything seems to be better than manually configuring MAXDOP options but for some stored procedures, leaving default MAXDOP really hurts the performance.

References

From Microsoft link :

http://blogs.msdn.com/b/sqlsakthi/archive/2012/05/24/wow-we-have-maxdop-calculator-for-sql-server-it-makes-my-job-easier.aspx

https://technet.microsoft.com/en-us/library/ms181007(v=sql.105).aspx

https://support.microsoft.com/en-us/kb/2806535

Good article on configuring MAXDOP and Cost Threshold:
http://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/

Very good article on using Parallelism:
https://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/