Sqlserver's unique task scheduling algorithm "sqlos"

Unique sqlserver Task Scheduling Algorithm "Sqlos"

Sqlos developed by MicrosoftBackground:

As an enterprise-level database platform, SQL Server must be able to process hundreds of user requests smoothly at the same time. SQL server must schedule threads.

More suitable for highly concurrent database applications.

Due to the above background:
On the basis of windows, sqlserver has developed its own job scheduling mechanism. Therefore, SQL Server is used as an application.ProgramAnd abstracted by the operating system

Management functions, such:
Task Scheduling Management Subsystem
Memory Management
Error and Exception Handling Mechanism
Deadlock Detection and resolution mechanism
Run third-partyCode(DLL, extended sp, etc.) Mechanism

 

The management component of sqlserver is also called sqlos SQL operating system. Memory Management and task scheduling management are two core components of sqlos.

For sqlserver, except for connections from the DAC dedicated administrator connection, other user connections are equally important for SQL,

Such connections may have hundreds of thousands at the same time point. Therefore, SQL Server cannot Schedule Tasks that depend entirely on Windows.

 

Features of SQL Server's job scheduling mechanism:

1. threads are allocated only for connections that require running tasks. Connections in idle state are expressed in a group of data structures in sqlserver, so they do not occupy thread resources. Greatly reduced

Number of threads required by the SQL server process

2. For each CPU, sqlserver has a sched internally, which determines which sqlserver thread is running at a certain time point.

Therefore, at the windows level, each CPU can correspond to only one running thread at most. Greatly reduce context switch on Windows

Practice has proved that many of them are 1000 ~ For SQL Server with 2000 concurrent users, only 100 or 200 threads are required. The batch processing volume completed by SQL Server can reach 3000 ~ per second ~ 4000.

 

Several sqlos concepts:

 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, reduces the context switch, and provides 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 is also fixed on a certain CPU.

Each scheduler has a worker upper limit and can be created or released based on the workload. Each worker runs a complete task ). Not until the task is finished.

Exit, unless the task enters the waiting status.

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, it also deletes idle workers in large quantities

Saves the memory overhead of Multi-page

Maximum number of worker threads automatically configured in combination with various CPU and sqlserver versions
32-bit computer 64-bit computer
& 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:

1 Select @ Servername 2 Go3 Select Getdate()4 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, even on different Scheduler

As long as a task starts to run, it will not be removed from this worker. For example, if a SELECT statement is blocked by other connections, the worker cannot continue running and can only enter the waiting state. But this

The Select task will not release the worker and allow it to perform other tasks. The result is that the thread corresponding to the worker enters the waiting state.

 

Yieding

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 is called yieding.

Each schedable has a runnable list. All workers waiting for the CPU to run will queue in this list, waiting for the SQL to be allocated to schedable to run with the first-in-first-out algorithm.

Sqlserver defines many yieding rules 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 exceeded 4 ms, yieding will be performed.

2. For every 64 kB result set sorting, yieding will be performed once.

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 there are many workers running on a scheder,

Sqlserver uses worker automatic yieding to schedule concurrent operations. This is more effective than Windows context switch.

 

 The following figure shows the sqlos Task Scheduling Algorithm:

 

Summary:

Sqlserver has a scheduler corresponding to each CPU. In each scheduler, there are several workers corresponding
For each thread. After a request is sent from the client, SQL splits it into one or more tasks. Based on the degree of busyness of each scheduler,
The task is assigned to a scheduler. If there is an idle worker in the scheduler, the task will be assigned to a worker.
If no, scheduler creates a new worker for the task. If the worker in scheduler has reached its upper limit,
All of them have tasks to run, so the new tasks have to enter the state of waiting for worker

Use the following two SQL statements to check the number of SQL Server instances.WorkersHow many are there at work?TasksRunning

1 Select * FromSYS. dm_ OS _workers2 Select * FromSYS. dm_ OS _tasks
 

----------------------------------------------------------Gorgeous split line---------------------------------------------------------

Let's talk about the meaning of the above figure.

Select * From SYS. dm_ OS _workers

For explanations of other columns, you can refer to the msdn

Http://msdn.microsoft.com/zh-cn/library/ms178626 (V = SQL .105). aspx

Bytes ------------------------------------------------------------------------------------------------------------

Select * From SYS. dm_ OS _tasks

The task status can be one of the following options::

Pending: waiting for the working thread.

Runnable: runable, but waiting for receiving range.

Running: The instance is currently running in the scheduler.

Suincluded: has a working thread, but is waiting for the event.

Done: Completed.

Spinloop: trapped in a spin lock

Description of msdn: http://msdn.microsoft.com/zh-cn/library/ms174963 (V = SQL .105). aspx

VeryNot written for a long timeArticleNow, I hope you can read my article to learn more about sqlserver.

It's time to go to bed ~

Sketch