SQLServerOS task scheduling mechanism

Introduction SQLServerOS is a user-level operating system level used to serve SQLServer on Windows. It abstracts the functions of the operating system from the SQL Server engine and forms a layer to provide services for the storage engine. SQLServerOS provides task scheduling, memory allocation, Deadlock Detection, resource detection,

Introduction SQL Server OS is a user-level operating system level used to serve SQL Server on Windows. It abstracts some functions of the operating system from the entire SQL Server engine and forms a separate layer to provide services for the storage engine. SQL Server OS provides task scheduling, memory allocation, Deadlock Detection, resource detection,

Introduction

On Windows, SQL Server OS is a user-level operating system level used to serve SQL Server. It abstracts some functions of the operating system from the entire SQL Server engine and forms a separate layer to provide services for the storage engine. SQL Server OS provides multiple functions such as task scheduling, memory allocation, Deadlock Detection, resource detection, lock management, and Buffer Pool management. This article focuses on the task scheduling mechanism provided in SQL OS.

 

 

Preemptive scheduling and non-Preemptive Scheduling

The source of Database-level task scheduling is the "Operating System Support for Database Management" in ACM ". However, for Windows, adding task scheduling that supports databases at the operating system level is not as good as abstracting a layer of scheduling in SQL Server, since task scheduling at the database layer can be abstracted, why not manage the memory and I/O at this abstraction layer? This idea is the origin of SQL Server OS.

After Windows NT4, Windows Task Scheduling is preemptible, that is, Windows tasks are determined based on the task priority and time slice. If the time slice of a task is used up or a task with a higher priority is waiting, the operating system can forcibly deprive the running thread (the thread is the basic unit of task scheduling) of the CPU occupied, assign CPU resources to other threads.

However, for SQL Server, this non-cooperative time slice-based task scheduling mechanism is not suitable. If SQL Server uses the job scheduling mechanism in Windows for Job Scheduling, Windows will not optimize the job scheduling mechanism based on SQL Server, but will interrupt the thread based on the time slice and priority, this causes the following two defects:

Windows does not know the optimal breakpoint for tasks in SQL Server (that is, tasks in SQL OS, which will be discussed later in this article, this will inevitably lead to more Context switches (the price of Context switches is very high and requires conversion between the thread user State and the core State ), because Windows scheduling is not decided by the thread itself, but by Windows. Windows does not know whether the corresponding thread in the current database is performing a key task, but it will only randomly capture the CPU of the thread.

The connection to SQL Server cannot be executed all the time, and there will be a lot of idle time between each Batch. If each connection needs to occupy one thread separately, it is unwise for SQL Server to maintain these threads and consume additional resources.

For SQL Server OS, thread scheduling adopts the cooperative mode instead of the preemption mode. This is because all the tasks in these databases are within the SandBox of SQL Server. SQL Server fully believes in its threads, so unless the thread voluntarily gives up the CPU, SQL Server OS will not forcibly deprive the CPU of the thread. In this way, although the Worker switchover is still performed through the Context Switch of Windows, this cooperation mode will greatly reduce the number of Context switches required.

SQL Server determines which thread is running at which point in time. It is implemented through something called Scheduler. Let's take a look at Scheduler.

 

Scheduler

In SQL Server, each logical CPU has a corresponding Scheduler. Only tasks with the ownership of Scheduler can be executed. schedlos can be considered as a logical CPU of SQLOS. You can view all Scheduler in the system through the sys. dm_ OS _schedulers DMV, as shown in 1.

. View sys. dm_ OS _schedulers

 

My notebook is an i7 quad-core 8-thread CPU, corresponding to, you can see that in addition to DAC and HIDDEN Scheduler running system tasks, there are a total of 8 schedcpu, each corresponding to a logical CPU, used to process internal tasks. Of course, you can set Affinity to include some Scheduler Offline, 2. Note that this process is online, and the website space does not need to be restarted on SQL Server.

. Set Affinity

 

In this case, you can see that four Scheduler instances are Offline without restarting the instance, as shown in Figure 3:

. Offline 4 Scheduler

 

Generally, you do not need to control Affinity unless your server runs other instances or programs.

In addition to the Visible schedible, we also noted that there are some special Scheduler which have more than 255 IDs and these schedid which are used inside the system, for example, resource management, DAC, backup and restoration operations. In addition, although the number of schedcpu and logical CPUs is the same for Hong Kong servers, this does not mean that schedcpu is bound to a fixed logical CPU, but schedcpu can run on any CPU, scheduler is fixed on a CPU only when Affinity Mask is set. One advantage is that when a Scheduler is very busy, it may not lead to only one physical CPU busy, because Scheduler will move between multiple CPUs, so that the CPU usage tends to average.

This means that for a long query, the first half can be executed on CPU0, And the last half can be executed on cpu1.

In addition, on each Scheduler, only one Worker can run at a time. If all resources are ready but no Scheduler is obtained, the Worker is in the Runnable state. Let's take a look at Worker.

 

Worker

Each Worker can be considered to correspond to a thread (or fiber). sched does not directly schedule the thread, but schedules the Worker. The number of workers increases as the load increases. In other words, the number of workers increases on demand until the maximum number is reached. In SQL Server, the maximum number of workers by default is managed by SQL Server. You can set the maximum Worker based on 32-bit or 64-bit and the number of CPUs. For details about the formula, refer to "BOL :( v = SQL .105). aspx. You can also set the maximum number of workers, as shown in figure 4.

. Set the maximum number of workers

For automatic configuration, the maximum number of running threads of SQL Server can be seen in sys. dm_ OS _sys_info, as shown in Figure 5.

. View the maximum number of automatically configured workers

 

In general, you do not need to set this value, but in some cases, you need to set this value. That is, the Worker thread is used up. In addition to DAC, you cannot even connect to SQL Server.