Detailed SQL Server OS task scheduling mechanism

Brief introduction

SQL Server OS is a user-level operating system hierarchy on top of Windows that serves SQL Server. It abstracts the functionality of the operating system part from the entire SQL Server engine and forms a separate layer to serve the storage engine. SQL Server OS mainly provides a variety of functions such as task scheduling, memory allocation, deadlock detection, resource detection, lock management, Buffer pool management, etc. This article is mainly about the task scheduling mechanism provided in SQL OS.

Preemptive (preemptive) scheduling and non-preemptive (non-preemptive) scheduling

The origin of task scheduling at the database level is an ACM article called "Operating System Support for database Management". But for Windows, at the operating system level to participate in supporting the database task scheduling, rather than in the SQL Server to abstract out a layer of scheduling, since you can abstract a layer of database-level task scheduling, so why not in this abstraction layer for memory and IO management? The idea is the origin of the SQL Server OS.

After Windows NT4, Windows task scheduling is preemptive, meaning that Windows tasks are determined based on priority and time slices for tasks. If a task's time slice runs out, or a task with a higher priority is waiting, the operating system can force the CPU that is occupied by the running thread (the basic unit of the Task Scheduler) to cede CPU resources to other threads.

However, for SQL Server, this kind of non cooperative, time based task scheduling mechanism is not so appropriate. If SQL Server uses the task scheduling mechanism within Windows to schedule tasks, Windows does not optimize according to SQL Server's scheduling mechanism, but breaks threads in terms of time slices and priorities, which results in the following two defects:

Windows does not know the best point of interruption in the tasks in SQL Server (that is, the task in SQL OS, which is described later in the article), which is bound to result in more context switch (the context switch is very, very expensive, Requires a conversion between the thread character user state and the kernel mentality, because Windows scheduling is not the thread itself deciding whether to sell the CPU, but is determined by Windows. Windows does not know whether the corresponding threads in the current database are doing critical tasks, and only indiscriminately captures the thread's CPU.

The connection to SQL Server cannot be performed all the time, and there will be a lot of slack between each batch. If each connection needs to occupy a single thread, it is unwise for SQL Server to maintain these threads by consuming additional resources.

In the case of SQL Server OS, the mode of collaboration used in thread scheduling is not preemption mode. This is because the tasks within these databases are within the sandbox of SQL Server, and SQL Server is fully confident of its internal thread, so unless the thread voluntarily abandons the Cpu,sql Server OS does not force the thread to be stripped of its CPU. This way, although the switch between the worker is still through the Windows context switch, this mode of collaboration will significantly reduce the number of context switches required.

SQL Server determines which thread runs at which point in time, through a thing called scheduler, let's look at scheduler.

Scheduler

Each logical CPU in SQL Server has a corresponding scheduler, only the task that gets scheduler ownership is allowed to be executed, scheduler can be regarded as a logical CPU of a team Sqlos. You can look at all the scheduler in the system by sys.dm_os_schedulers this DMV, as shown in Figure 1.

Figure 1. View Sys.dm_os_schedulers

My notebook is a i7 four-core 8-thread CPU, corresponding to the hidden Scheduler that can be seen in addition to the DAC and run system tasks, the remaining Scheduler altogether 8, each corresponding to a logical CPU, for handling internal tasks. Of course, you can also set affinity to some scheduler Offline, as shown in Figure 2. Note that this process is online and can be implemented without restarting SQL Server.

Figure 2. Setting up affinity