SQL prompt query processor failed to start the necessary thread resources for executing a parallel query

Recently, SQL frequently prompts that the "query processor failed to start the necessary thread resources for executing a parallel query" and was slow to respond.

The SQL environment is a failover cluster for two servers, one master, 48 cores, 64G memory, 24 cores, 64G memory.

The check discovery service has been transferred to an alternate node (for the reason of the transfer), because this problem has not previously occurred, the failure is locked in the computer hardware, the SQL configuration problem: Our server CPU is 4 cores (48 logical CPUs), the operating system will group these logical CPUs. Each grouping has a number of logical CPUs that are determined by the operating system. (The number of each restart of the computer group may change) assuming that 12 logical CPUs in a group, if your "maximum degree of parallelism" is 12 then this SQL only needs to be executed in this set of logical CPUs, the 12 logical CPUs share the same block of memory space. That is, if you set the maximum degree of parallelism to exceed 12, for example, set to 13, a cross-NUMA issue occurs.

When the second group is working well, when a group is working well, if the other two logical CPUs of the second group are not yet well-done, then it is necessary to wait for B, or the second group to have the result, but because of the thread switching, or the server is not busy.

Check the server's CPU settings first

Properties--processor--expand

650) this.width=650; "title=" 1.jpg "alt=" wkiom1ryeszd7-9_aahpc7q-odi246.jpg "src=" http://s3.51cto.com/wyfs02/M01/ 4d/bb/wkiom1ryeszd7-9_aahpc7q-odi246.jpg "/>

Each time the CPU restarts will be grouped, and the number of each group may be different, look at the backup on the SQL Settings CPU divided into 4 groups, 6 cores per group, so we set the maximum number of concurrent numbers to try not to exceed 6, otherwise there will be queries across NUMA issues

Workaround:

The maximum degree of parallelism is changed to 5, click on the running value (after clicking, the maximum degree of parallelism will show gray, no tube), click OK, do not need to restart, wait more than 10 seconds to take effect.

650) this.width=650; "title=" 2.jpg "alt=" wkiom1ryfo2w6-ukaakmgrxl9-o083.jpg "src=" http://s3.51cto.com/wyfs02/M01/ 4d/bb/wkiom1ryfo2w6-ukaakmgrxl9-o083.jpg "/>

Note: Concurrency is not the same as parallelism, not the same thing. An operator of an SQL statement/query that requires multi-threaded execution, SQL Server will turn on multithreading to handle this operator. This is determined by SQL Server if you do not set the degree of parallelism, assuming that your CPU is four threads then SQL Server will use 4 threads to perform if you set the degree of parallelism to 2 then SQL Server will only use 2 threads to execute your query and 2 additional resources to the other software/ The operating system uses parallelism only as a switch, and the decision is determined by SQL Server – see which SQL threads belong to which processor group SELECT [Thread_address],[started_by_sqlservr],[creation_ Time],[processor_group],[scheduler_address] from Sys.dm_os_threads

This article is from the "Lixiangqian" blog, make sure to keep this source http://lixiangqian.blog.51cto.com/2848430/1571714

SQL prompt query processor failed to start the necessary thread resources for executing a parallel query