Questions raised by sys. dm_ OS _waiting_tasks of SQL Server Applications (medium ),

Questions raised by sys. dm_ OS _waiting_tasks of SQL Server Applications (medium ),

The previous article introduced the SQL Server application sys. dm_ OS _waiting_tasks raises a question (I) and says sys. dm_exec_requests and sys. dm_ OS _waiting_tasks has different results when obtaining parallel waits. In this article, we will talk about my second question: why is there so many waits for a parallel plan (4 threads, how to execute SQL in parallel !!!!

First, paste the following result graph of sys. dm_ OS _waiting_tasks:

By analyzing the task_address of this result, we can see that there are only nine tasks to remove duplicates. That is to say, there will be nine parallel tasks (with four threads and different configurations. What are thread, task, worker, and schedulers? It is necessary to first talk about this, because I had a mess before this blog.

Scheduler

For each logical CPU, SQLSERVER corresponds to a scheduler, which represents the CPU object at the SQL level. Only the worker of the task that obtains the ownership of scheduler can run on this logical CPU.

The so-called logical CPU is the number of CPUs that SQL Server sees on the Windows level. If it is a dual-core CPU, then a physical CPU is two logical CPUs in SQL view. If the system still uses

Hyper-threaded, which is four logical CPUs for SQLSERVER

Rule: the maximum number of workers on each scheder is equal to the maximum number of SQLSERVER threads divided by the number of scheduler. At the same time point, only one worker with scheduler is running, other workers must be in the waiting status. This reduces the number of running threads on each logical CPU and the context switch. scalability scheduler is a logical concept of SQLSERVER, Which is not bound to the physical CPU. That is to say, a scheduler can be scheduled on this CPU for a while by Windows, and on that CPU for a while.

However, if the CPU affinity mask is set in sp_configure, schedity will be fixed on a specific CPU.

Worker

Each worker corresponds to a thread (or fiber) and is the execution unit of the SQLSERVER task. SQLSERVER does not directly schedule threads/fiber threads, but schedules worker, enabling SQLSERVER to control

Task Scheduling

Rule: each worker is fixed to a thread (or fiber) and bound to a sched. If schedity is fixed on a certain CPU (by setting CPU affinity mask), the worker will also be fixed on a certain CPU, and each scheduler has a worker upper limit, you can create or release a worker based on the workload of SQLSERVER. Each worker runs a complete task ). It will not exit until the task is completed, unless the task enters the waiting state.

Scheduler creates a new worker only when there are new jobs to run and there is no idle worker.

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

Task

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

SELECT @ SERVERNAME
GO
Select getdate ()
GO

The two batchs are two tasks respectively. SQLSERVER assigns a worker to the first batch (select @ servername), returns the result to the client, and distributes the second batch.

(Select getdate () a worker. The two workers may be different workers. Even if a task starts running on different scheduler instances, they will not be removed from the worker. For example, if a select statement is blocked by other connections, the worker cannot continue running and can only enter the waiting state. However, this select task will not release the worker and let it do other tasks. The result is that the thread corresponding to the worker enters the waiting state.

Yielding

The core of SQLOS's task scheduling algorithm is that all workers running on the logical scheduler are non-preemptive ). A worker is always running on scheduler until it is finished, or voluntarily transfers scheduler to another worker. This "let out" scheduler action, called yieding, each scheding will have a runnable list. All workers waiting for CPU running will queue in this list, using the first-in-first-out algorithm, wait until the SQL statement is assigned to scheding to run SQLSERVER. Many yieding rules are defined to constrain the time when a task runs in scheduler. If the task is complex and cannot be completed quickly, it will ensure that the task performs yieding at the appropriate time, so it will not take too much time For scheduler.

Common time points:

1. When a worker is going to read a data page, SQLSERVER will check how long the worker has been running on scheduler. If it has been more than 4 ms, yielding will be implemented.

2. For every 64 kB result set sorting, yielding is performed.

3. During the compile statement compilation process (this process occupies CPU resources), there are often yieding

4. If the client cannot remove the result set in time, the worker will perform yieding

5. After each sentence in a batch is completed, yieding will be performed once.

Normally, even if a task has to be executed for a long time, the worker he uses will often perform yieding, and will not occupy the CPU for a long time. If many workers are running on a scheder instance at the same time, SQLSERVER uses the worker automatic yielding Method to schedule and run concurrently. This is more effective than Windows context switch.

Additional hand plot

In addition, we recommend an official Microsoft Asia Pacific blog on SQL SERVER SQLOS task scheduling.

We probably learned about SQL SERVER SQLOS task scheduling. Let's go back to our parallel topic and look at the parallel execution scheduling:

One parallel processing task is assigned to nine tasks, and nine workers are also enabled, which are scheduled by four scheduler. Each scheder is requested by one request data and the other waits. So what are you waiting? My personal understanding is related to the current execution plan. The SQL statement to be summarized after the four threads obtain data does not enable thread receiving after the data is obtained, but the receiving thread waits for the data to be obtained.

　　　　　

Is the problem solved? Because it has been marked as a middle article, you still have questions !!!! Let's continue to the next article ....

Articles you may be interested in:

• Using GruntJS to build a Web program task
• Questions about SQL Server application sys. dm_ OS _waiting_tasks (I)
• Questions about SQL Server application sys. dm_ OS _waiting_tasks (below)