Sql-server – SQL Server recommended MAXDOP settings for NUMA

hardwareperformanceperformance-tuningsql serversql-server-2008-r2

I have an OLTP SQL Database running on SQL 2008 R2 Enterprise Edition that may be fairly busy (5000 batches/sec) in next couple of months after going live production. Looking into server configuration I have question about setting MAXDOP however now a days what I find as a regular reader of this forum general consensus is that to leave server wide MAXDOP setting to 0 for tasks that need parallel operation (e.g. index rebuild and change cost threshold of parallelism to a higher number (15 or 20 instead of default 5).

However, this server has NUMA nodes and based on these MS support articles
http://support.microsoft.com/kb/329204

http://support.microsoft.com/kb/2023536

to prevent costly foreign memory access it is recommended to set MAXDOP = number of cores in the physical processor.

This server has 2 physical processors with 16 logical (8 physical) cores on each socket. Does guideline suggested by that MS support KB is still good?

I know everything need to be tried and tested but my goal is to set MAXDOP to the best theoretical number and adjust it if needed later on. Based on it should I set MAXDOP setting to 8?

Best Answer

Typically I recommend to my clients leaving the MAXDOP alone. SQL Server is NUMA aware so it knows which NUMA node the processors are in and which NUMA node the memory is in and it'll do it's best to assign the work to a scheduler on the correct NUMA node for the data that you are looking for.

Looking into this sort of stuff also requires knowing how many NUMA nodes you have. Is each physical processor in its own NUMA node or are they node in the same NUMA node?

Related Solutions

Sql-server – SQL Server R2 Standard Edition MAXDOP setting

Standard edition will not generate a parallel plan that uses more than 4 processors i.e. MAXDOP 4. Setting MAXDOP to a value higher than this will not alter the limitation imposed by the edition. You get no warning or error when setting the higher value, it will be accepted.

SQL Server – NUMA Nodes, MAXDOP, and PLE Explained

SQL SERVER 2005 SP3

Your best bet first is to upgrade it to a supported SP - First Download SP4 then Download CU3 --> Build NO: 9.00.5266. Support for SQL server 2005 is ending next year (2016/04/12) even for the latest SP and CU - so better upgrade to a 2012/2014.

You can use my script to help you suggest a good MAXDOP value.

Can anyone explain to me the impact of parallel executions across NUMA nodes, is it just the same as exhausting worker threads and slower access time when using foreign memory ?

SQL server is NUMA aware. This means that SQL server knows what NUMA node the processors are in and what NUMA node the memory is in. Due to this, SQL server will assign worker thread on correct NUMA node for the data that you want to access.

Refer to :

Below are some queries that will help you find out if there is an imbalance of schedulers between the NUMA nodes - which can lead to significant performance problems under load :

SELECT 
  parent_node_id,
  scheduler_id, 
  [cpu_id], 
  is_idle, 
  current_tasks_count, 
  runnable_tasks_count, 
  active_workers_count, 
  load_factor
FROM sys.dm_os_schedulers
WHERE [status] = N'VISIBLE ONLINE';

--- check the current tasks, runnable tasks, active workers and avg load factor COUNT ...
SELECT 
  parent_node_id, 
  SUM(current_tasks_count) AS current_tasks_count, 
  SUM(runnable_tasks_count) AS runnable_tasks_count, 
  SUM(active_workers_count) AS active_workers_count, 
  AVG(load_factor) AS avg_load_factor
FROM sys.dm_os_schedulers
WHERE [status] = N'VISIBLE ONLINE'
GROUP BY parent_node_id;

You should monitor SQL Server:Buffer Node and SQL Server, Memory Node as opposed to just PLE - monitor the individual Buffer Node:Page life expectancy counters (there will be one Buffer Node performance object for each NUMA node).

exhausting worker threads

IF you set the MAXDOP setting as default (which is 0) then it could lead to worker thread starvation.

Related Question