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

DECLARE @pCurrentMAXDOP SQL_VARIANT
DECLARE @hyperthreadingRatio BIT
DECLARE @logicalCPUs INT
DECLARE @HTEnabled INT
DECLARE @physicalCPU INT
DECLARE @SOCKET INT
DECLARE @logicalCPUPerNuma INT
DECLARE @NoOfNUMA INT

SELECT  @logicalCPUs = cpu_count -- [Logical CPU Count]
        ,
        @hyperthreadingRatio = hyperthread_ratio --  [Hyperthread Ratio]
        ,
        @physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count]
        ,
        @HTEnabled = CASE WHEN cpu_count > hyperthread_ratio THEN 1
                          ELSE 0
                     END -- HTEnabled
FROM    sys.dm_os_sys_info


SELECT  @logicalCPUPerNuma = COUNT(parent_node_id) -- [NumberOfLogicalProcessorsPerNuma]
FROM    sys.dm_os_schedulers
WHERE   [status] = 'VISIBLE ONLINE'
        AND parent_node_id < 64
GROUP BY parent_node_id


SELECT  @NoOfNUMA = COUNT(DISTINCT parent_node_id)
FROM    sys.dm_os_schedulers -- find NO OF NUMA Nodes
WHERE   [status] = 'VISIBLE ONLINE'
        AND parent_node_id < 64

SELECT  @pCurrentMAXDOP = value
FROM    sys.configurations
WHERE   name = 'max degree of parallelism'

SELECT  @logicalCPUs AS [Logical CPU Count] ,
        @hyperthreadingRatio AS [Hyperthread Ratio] ,
        @physicalCPU AS [Physical CPU Count] ,
        @HTEnabled AS HTEnabled ,
        @NoOfNUMA NumberofNUMA_Nodes ,
        @logicalCPUPerNuma [NumberOfLogicalProcessorsPerNumaNode] ,
        @pCurrentMAXDOP Current_MAXDOP_Value


-- MAXDOP Recommendations Based on Simple Calculation witout Considering Workload Patterns
SELECT
    --- 8 or less processors and NO HT enabled
        CASE WHEN @logicalCPUs < 8 AND @HTEnabled = 0 THEN 'Recommended MAXDOP setting is : ' + CAST(@logicalCPUs AS VARCHAR(3)) --- 8 or more processors and NO HT enabled WHEN @logicalCPUs >= 8
                  AND @HTEnabled = 0 THEN 'MAXDOP setting should be : 8'
                --- 8 or more processors and HT enabled and NO NUMA
             WHEN @logicalCPUs >= 8
                  AND @HTEnabled = 1
                  AND @NoOfNUMA = 1
             THEN 'Recommended MAXDOP setting is : '
                  + CAST(@logicalCPUPerNuma / @physicalCPU AS VARCHAR(3))
                --- 8 or more processors and HT enabled and NUMA
             WHEN @logicalCPUs >= 8
                  AND @HTEnabled = 1
                  AND @NoOfNUMA > 1
             THEN 'Recommended MAXDOP setting is :  ' + CAST(8 AS VARCHAR(3))
             ELSE ''
        END AS Recommendations

List of Stored Procedures using Parallelism:

SELECT TOP 50
        OBJECT_NAME(p.objectid, p.dbid) AS [object_name] ,
        ps.total_worker_time / ps.execution_count AS avg_worker_time ,
        ps.execution_count ,
        ps.total_worker_time ,
        ps.total_logical_reads ,
        ps.total_elapsed_time ,
        p.query_plan ,
        q.text ,
        cp.plan_handle
FROM    sys.dm_exec_procedure_stats ps
        INNER JOIN sys.dm_exec_cached_plans cp ON ps.plan_handle = cp.plan_handle
        CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
        CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q
WHERE   cp.cacheobjtype = 'Compiled Plan'
        AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)',
                               'float') >0
ORDER BY ps.execution_count

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/