MSSQLServer degree of parallelism

The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that the parallel plan uses to execute the query. This option determines the compute and thread resources used to perform the work parallel query plan operators. Depending on whether SQL Server has a symmetric multi-processing (SMP) computer set up, a non-uniform memory access (NUMA) computer, or a Hyper-threading-enabled processor, you must configure the maximum degree of parallelism option appropriately. This article discusses the general principles that can be used to configure the maximum degree of parallelism option for SQL Server when you use the sp_configure system stored procedure.
If the query specifies this option, the option (MAXDOP) Transact-SQL query hint can override the maximum degree of parallelism option in the sp_configure value. In SQL Server 2000, overrides do not take effect until the value specified in the hint is less than or equal to the sp_configure value. In SQL Server 2005 and later versions, this override always works. In SQL Server 2008 and later versions, if the MAXDOP value exceeds the value that is configured by using Resource Manager, the database engine uses the resource Governor MAXDOP value. When you use the MAXDOP query hint, all semantic rules that are used with the max degree of parallelism option apply. The other two options you can override or affect MAXDOP settings are as follows:

Note the maximum degree of parallelism configuration option can be done without limiting the number of processors that SQL Server uses. To configure the number of processors that SQL Server uses, use the relationship mask configuration option.

OLTP systems:

On a pure OLTP system, it has a short transaction and a short SQL query time, but is very frequent. Set "Maximum degree of Parallelism" (MAXDOP) to 1. Doing so ensures that the query never has to run in parallel and does not result in more database engine overhead.

OLAP system:

Data-warehousing/reporting server: Because the query execution time is generally longer, it is recommended to set "Maximum degree of Parallelism" (MAXDOP) to 0.

This way, most queries will take advantage of parallel processing, and queries that take longer to execute will also benefit from multiprocessor and improve performance.

Hybrid Systems

Mixed System (OLTP & OLAP): This environment can be a challenge and must find the right balance point. Generally, a very simple method is taken. Setting "Maximum degree of

Parallelism "(MAXDOP) is 2, which means that the query still uses parallel operations but only 2 CPUs are used. and set the "Parallel query threshold" (cost threshold for parallelism) to a higher value, so that not all queries are eligible to use parallelism, except for those queries with higher cost ...

The cost threshold for parallelism option can be set to any value between 0 and 32767. The default value is 5.

Individuals tend to prefer the second (max degree of parallelism) setting guideline, hybrid system setup max degree of parallelism, which is recommended in conjunction with the first recommended setting with the second, and then combined with the case of waiting events Cxpacket, Make the appropriate adjustments. Do not expect a static recommendation setting, which is unreasonable. Can only be based on the comparison of the recommendations of the public settings, and then combined with the specific circumstances of their own system to make appropriate adjustments, optimization.

For areas where NUMA and SMP are not very clear, you can refer to the following information.

Traditional multicore operations Use the SMP (symmetric multi-processor) mode: Connect multiple processors to a centralized memory and I/O bus. All processors can only access the same physical memory, so SMP systems are sometimes referred to as consistent memory access (UMA) architectures, and consistency means that the processor can only hold or share a unique value for each data in memory at any time. Obviously, the disadvantage of SMP is that scalability is limited because increasing the processor does not achieve higher performance when the memory and I/O interfaces are saturated.

Numa mode is a kind of distributed memory access mode, the processor can access different memory address at the same time, greatly improve the parallelism. In NUMA mode, the processor is divided into multiple "nodes", and each node is assigned a local memory space. The processors in all nodes can access all of the system physical memory, but it takes much less time to access the memory within the node than to access the memory in some remote nodes.

MSSQLServer degree of parallelism