SQL Server sqlos Task Scheduling

Http://blogs.msdn.com/ B /apgcdsd/archive/2011/11/24/sql-server-sqlos.aspx

SQL Server schedules and processes tasks through worker, scheduler, and tasks. Understanding these concepts is very helpful for understanding how SQL Server works internally.

 

Generally, the number of schedcpu instances matches the number of CPUs. In addition to the scheduler of several systems, each scheduler is mapped to a single CPU. As shown in the following query results, we have four CPUs and four scheduler S.

 

 

Worker (also known as worker thread) is a worker thread. On a server, we can have multiple working threads. Because each worker thread consumes resources, SQL server has a maximum number of worker threads. When a task comes in, the system will assign it a working thread for processing. However, when all the working threads are busy and the maximum number of working threads has been reached, SQL Server will wait until a busy working thread is released. The maximum number of working threads can be obtained through the following query. SQL Server does not create all these worker threads at the beginning, but is created as needed.

 

 

Task is from batch. We know that a connection can contain multiple batchs, and each batch can be divided into multiple tasks. As shown in the following figure, a connection is required. This connection has two batchs, and each batch, such as select * From table_ B, may be divided into multiple tasks because it supports parallel queries. It is determined by the SQL server.

 

Insert into table_ B values ('aaa ')

Go

Select * From table_ B

Go

 

 

[Relationship]

 

We have a preliminary understanding of the concepts of connection, batch, task, worker, scheduler, and CPU. What is the relationship between them?

 

 

 

As shown in, there are many connections on the left. Each connection has a corresponding spid. This always exists as long as the user does not log out or has no timeout. In standard settings, there is no limit on the number of user connections.

In each connection, we may have many batchs. In a connection, batchs are sequential. The following batch is executed only when one batch is finished. Because there are many connections, there are also many batchs at the SQL server level.

SQL Server performs optimization. Each batch may be divided into multiple tasks to support parallel queries, for example. In this way, at the SQL level, there will be many tasks at the same time.

On SQL Server, each CPU usually corresponds to a scheduler, and there are several additional system scheduler, which is only used to execute some system tasks. For users, we only need to care about user scheduler. If there are 4 CPUs, there will usually be 4 User scheduler.

Each scheder can have multiple workers. Worker is the real execution unit, and scheduler (encapsulation of CPU) is the place where execution is performed. The total number of workers is limited by Max worker threads. When creating a worker, you need to apply for 2 MB of memory space. If the max worker thread is 1024 and all the workers are created, at least 2 GB of space is required. Therefore, too many workers occupy a lot of system resources.

[Tracking]

We understand the relationship between connection, batch, task, worker, scheduler, and CPU. Next we use DMV to track the operation process.

Step 1:

Run the following script to create a test database and test data table.

Create Database Test

Go

Use test

Go

Create Table Test

(ID int,

Name nvarchar (50)

)

Insert into test
Values (1,
'Aaa ')

Step 2:

Open a query window and execute the following statement. Note that there is no commit transaction here.

Begin tran

Update Test Set Name = 'bbb'
Where [ID] = 1

Step 3:

Open another window and execute the following statement. We can see that the following query is always executed because the previous transaction is not closed. From the query window, we can see that the spid of the following statement is 58.

Select *
From Test

Step 4: view the connection.

According to the following query, the spid corresponding to our connection is 58 and it is blocked.

 

Step 5: View batch

Check SQL profiler and see that our batch is select * from test

 

 

Step 6: view the task

With the following DMV, we can see that for session_id = 58, there is only one task (Address: 0x0064f048), and for this task, the worker address is 0x803081a0. At the same time, we can also see that the worker runs on scheduler 0.

 

 

Step 7: View worker

The following query shows that the worker has executed 5291 tasks. The corresponding scheduler address of this worker is 0x00932080.

 

 

Step 8: View Scheduler

The following query shows that the cpu_id of scheduler_address (0x00932080) is 0. In our system, there are 4 CPUs numbered 0, 1, 2, 3. however, there are 7 scheduler, 3 of which are system scheduler and 4 are user scheduler. Each scheduler has a corresponding number of workers. Because worker is created as needed, currently there are very few workers in each scheduler. In addition, some workers are still in sleeping state.

 

 

[Application]

We understand the task scheduling mechanism of SQL Server, so some problems will be clearer.

Set the role of maxdop. If maxdop = 1, a batch can correspond to only one task. If a batch generates multiple tasks, the coordination and waiting among tasks will be very costly. Set maxdop to a smaller value to reduce the worker usage. Therefore, if we see that the waiting type is cxpacket, we can set maxdop to reduce the degree of parallelism.

Relatively large spid. If we see that the spid number is very large, such as more than 1000, it usually indicates that our system has very serious blocking. SQL Server does not limit the number of connections, but limits the number of workers. By default, the maximum number is as follows:

Number of CPUs	32bit	64 bit
<= 4 Processors	256	512
8 Processors	288	576
16 Processors	352	704
32 processors	480	960

 

For a large spid number, it usually indicates that the number of workers is very high. This situation is dangerous. If a new connection comes in, there may be no idle worker to process the connection. In the cluster environment, the isalive check will fail, resulting in SQL Server failure.

Scheduler NON-YIELDINGError. We sometimes see that SQL Server reports a 17883 error, NON-YIELDING scheduler. This error indicates that there will be multiple workers on a scheduler, which occupy the resources of Scheduler for a while in a friendly way. After a worker occupies the schedeld, it performs yield for a period of time, that is, a concession. The scheduler resource is provided for other workers to use. If a worker does not backend scheduler resources for some reason, other workers do not have the opportunity to run, this is called a NON-YIELDING.
Scheduler. In this case, the SQL server has an automatic detection mechanism that will be dumped. We need to further analyze why dump does not use yield.

Worker used up. We can do a small experiment. On a 32-bit machine, we create the test database mentioned above and enable an update statement that does not close transaction.

Then execute the following program. The following program will enable 256 connections to the SQL server. These 256 connections are in the blocking status because the previous transaction is not closed.

Using system;

Using system. diagnostics;

 

Namespace worker

{

Class Program

{

Static void main (string [] ARGs)

{

For (INT I = 0; I <256; I ++)

{

Openconnection ();

}

}

 

Static void openconnection ()

{

Processstartinfo startinfo =
New processstartinfo ();

Startinfo. filename = "sql0000.exe ";

Startinfo. Arguments = "-e-s servername-d test-Q \" select * from test \"";

Process. Start (startinfo );

}

}

}

Query select *
From SYS. dm_ OS _tasks at this time we found that there were 278 tasks, and the query SYS. dm_ OS _schedulers
We found that there are two CPUs, so there are two users, scheders. Each scheders has 128 workers. In addition, there are 256 workers. For the architecture of two CPUs, the maximum number of workers is 256 by default. So it has reached the limit.

 

When a new connection is enabled, the SQL server cannot be connected and the following error is reported:

 

This is because worker is used up. The new connection cannot obtain a worker for login process. Therefore, the connection fails. In a cluster environment, if SQL Server cannot be connected, the isalive check will fail, causing SQL Server failover. All connections are forcibly aborted, and SQL Server is restarted on the new node. In this case, we can modify and improve the max worker thread, but it cannot solve the problem. Due to blocking, new connections will accumulate quickly and the max worker thread will be used up all the time, we should check blocking. This allows tasks to be completed in a timely manner and worker is released.

[Summary]

The task scheduling of SQL Server allows SQL Server to process user requests in the fastest way. Understanding the task scheduling process of SQL Server is very helpful for us to adjust the system performance. If you add a max worker thread, adjust maxdop, and remove blocking, understanding these concepts will make our adjustments more purposeful.