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:
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.
From Microsoft link :
Good article on configuring MAXDOP and Cost Threshold:
Very good article on using Parallelism: