Understanding Parallelism in SQL Server
When SQL Server runs on a server with more than one processor or CPU, it detects to use all available CPU’s during query execution. SQL Server’s 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 (KB# 2806535).
Max Degree of Parallelism (MAXDOP) value should be equal to number of online schedulers per node. For example, if your SQL Server runs on 4 sockets/8 core CPU that provides hyperthreading, MAXDOP value should be set to 8.
By default, MAXDOP value sets to “0” which means that SQL Server will use all processors if a query runs in parallel. Also this is not a recomended value. If you didn’t start to play with max degree of parallelism yet, changing the value to “1” will be the best choice for the start up and OLTP workloads. Before you change the value of MAXDOP, you should select to sys.dm_os_schedulers to ensure that count of CPU’s per Numa Nodes.
|
|
SELECT [parent_node_id] AS [NumaNode] , COUNT(scheduler_address) AS [CPUCount] FROM [sys].[dm_os_schedulers] WHERE [status] = 'VISIBLE ONLINE' GROUP BY [parent_node_id] |
Btw, you can also use SSMS GUI to look at your online schedulers (Instance → Properties → Processors). So, if you ask what values should I use for max degree of parallelism (MAXDOP). The answer will be; “it depends“. Depends on your workload type (OLTP, OLAP etc…), CPU’s and Cores.
Max degree of parallelism configuration option does not limit the number of processors that SQL Server uses. To configure the number of processors that SQL Server uses, use the affinity mask configuration option.
KB#2806535 - Recommendations and guidelines for the max degree of parallelism configuration option in SQL Server
I/O completion threads and lazy writer threads are the biggest bottleneck on servers that have many CPUs and don’t have hardware NUMA. On this case, we have to think that configure Soft-NUMA to reducing performance bottlenecks. So what is exactly NUMA? NUMA is a kind of machine memory architecture that used under multiprocessing. By the way, here is CSS SQL Server Engineers’ detailed explanation about tricky details of Sof-Numa.
You cannot create a soft-NUMA that includes CPUs from different hardware NUMA nodes. For example, if your hardware has eight CPUs (0..7) and you have two hardware NUMA nodes (0-3 and 4-7), you can create soft-NUMA by combining CPU(0,1) and CPU(2,3). You cannot create soft-NUMA using CPU (1, 5), but you can use CPU affinity to affinitize an instance of SQL Server to CPUs from different NUMA nodes. So in the previous example, if SQL Server uses CPUs 0-3, you will have one I/O thread and one lazy writer thread. If, in the previous example SQL Server uses CPUs 1, 2, 5, and 6, you will access two NUMA nodes and have two I/O threads and two lazy writer threads.
To be continued…
Post Details
