Questions raised by the sys.dm_os_waiting_tasks of SQL Server Applications (middle) _mssql

Through the article to introduce the SQL Server application of the Sys.dm_os_waiting_tasks raised questions (on), said Sys.dm_exec_requests and sys.dm_os_waiting_tasks When we get a different result from the parallel wait, we talk about my second question: Why a parallel plan (4 threads) has been waiting for so many times, and how SQL parallelism actually executes!!!!

First, the following sys.dm_os_waiting_tasks results are posted:

We analyze the results of the task_address can see that the removal of duplicates in fact only 9, that is, a parallel (4 threads, configuration different, the situation is different) there will be 9 tasks. It's a thread, a task, and worker,schedulers what are these? This is necessary to say first, because this blog before I also disorderly.

Scheduler

For each logical cpu,sqlserver there is a scheduler corresponding to the CPU object on the SQL level, and only the task worker who gets the scheduler ownership can run on this logical CPU

The logical CPU is the number of CPUs that SQL Server sees from the Windows level, and if it is a dual-core CPU, then a physical CPU is two logical CPUs in SQL. If the system also uses

Hyper-Threading hyper-threaded, which is 4 logical CPUs for SQL Server

Rule: The maximum number of workers on each scheduler equals the maximum number of threads in SQL Server divided by the number of scheduler, at the same point in time, only one worker with scheduler is running. All other worker must be in a waiting state. This reduces the number of running threads on each logical CPU, lowers the context switch, and provides scalability scheduler is a logical concept of SQL Server that does not bind to the physical CPU. In other words, a scheduler can be arranged by Windows for a while on the CPU, while on that CPU.

However, if the CPU affinity mask is set in the sp_configure, then scheduler is fixed on a particular CPU

Worker

Each worker corresponds to a thread (or fiber fiber), which is the executing unit of the SQL Server task. Instead of scheduling threads/fibers directly, SQL Server schedules the worker so that SQL Server can control

Task scheduling

Rule: Each worker is fixed to represent a thread (or fiber) and is bound to a scheduler. If the scheduler is fixed on a CPU (by setting the CPU affinity mask), then the worker is also fixed on a CPU, each scheduler has a worker's upper-bound value, And you can create or release a worker based on the SQL Server workload, each time a worker runs a complete task. Do not quit until the task is finished, unless the task enters the standby state actively.

Scheduler creates a new worker only if a new task is running and there is currently no idle worker.

A worker is idle for more than 15 minutes, and scheduler may delete this worker and its corresponding thread.                        When SQL Server encounters memory pressure, a large number of idle workers are also deleted to save multi-page memory overhead The maximum number of worker threads configured automatically by various CPUs and SQL Server versions 32-bit computers 64-bit computers
<=4 256 512
8 288 576
16 352 704
32 480 960

Task

The smallest task unit that runs on a worker. The simplest task is a simple batch. For example, the customer sends the following request:

SELECT @ @SERVERNAME
Go
SELECT GETDATE ()
Go

So the two batch were two tasks. SQL Server assigns first batch (SELECT @ @servername) A worker, returns the result to the client, and assigns a second batch

(select GETDATE ()) a worker. These two worker may be different worker, even if a task begins to run on a different scheduler, he will not be removed from this worker. For example, if a SELECT statement is blocked by another connection, the worker cannot continue to run and can only enter the wait state. But this select task does not release the worker and let him do other tasks. So the result is that the worker's thread will enter the waiting state.

Yielding

The core of Sqlos's task scheduling algorithm is that all the worker who runs on the logical scheduler is a non preemptive (non-preemptive). The worker always runs on the scheduler until the end of his run, or scheduler to the other worker. This "let out" scheduler action, we call yieding each scheduler will have a runnable list, all waiting for the CPU to run the worker will be queued in this list, with the FIFO algorithm, Wait for SQL to assign to him scheduler running SQL Server defines a number of yieding rules that constrain the time a task runs in scheduler. If a task is complex and cannot be completed quickly, it will ensure that the task is yieding at the right point in time and does not occupy scheduler too much time.

Common points of Time:

1, when the worker to read the data page every time, SQL Server will check the worker has been on the scheduler for how long, if already more than 4ms, do yielding

2, every 64KB result set sorting, will do a yielding

3, in the process of making statements compiled compile (this process compared to the CPU resources), there will often be yieding

4, if the client can not timely take the result set away, the worker will do yieding

5, a batch every word done, will do a yieding

Normally, even if a task to do for a long time, he used the worker will often do yieding, not for a long time to occupy the CPU. If there are a lot of worker to run at the same time on a scheduler, SQL Server dispatches the concurrency run automatically yielding by the worker. This is more efficient than Windows use context switches

Attach another hand drawing

Also recommend a SQL SERVER Sqlos Task Scheduler Microsoft Asia-Pacific Official Blog

We've probably learned about the Task Scheduler for SQL SERVER Sqlos we go back to our parallel topic and look at this parallel execution schedule:

A parallel processing is assigned to 9 tasks, and 9 worker is enabled, with 4 scheduler scheduled, each scheduler by one request data and the other waiting. So the application data is understandable, what is waiting for? Personal understanding is related to the current execution plan, the operation of 4 threads to do the rollup after the data is obtained the SQL does not wait for the data acquisition to open the thread reception, but the receiving thread waits while fetching the data.

　　　　　

Has the question been solved? Because it has been marked as medium, there are still doubts about it!!!! Let's go on to the next article ....