Task Scheduling and CPU Deep Explanation in SQL Server

Time:2019-4-3

I. overview

We know that SQL Server products, like other applications, are not treated specifically in the view of the operating system. But memory, hard disk and CPU are the most important core resources of database system, so SQLOS appeared in SQL Server 2005 and later. This component is the middle layer of SQL server and windows, which is used for task scheduling of CPU, resolving resource contention of I/O, coordinating other resource coordination work such as memory management. Now let me try to talk about Scheduler scheduling management under SQLOS. Let’s have a look at the details.

II. CPU Configuration

In Sql server, right-click the database instance to the property and select the processor to configure. The default value for the maximum number of worker threads is 0 (note that worker is configured here as a true encapsulation of CPUs). This enables SQL Server to automatically configure the number of worker threads at startup. Default settings are best for most systems. However, depending on your system configuration, setting the maximum number of worker threads to a specific value can sometimes improve performance. When the actual number of query requests is less than the maximum number of worker threads, a thread processes a query request. However, if the actual number of query requests exceeds the maximum number of threads, the SQL Server pools the Worker Threads thread so that the next available worker thread can process the request.

The configuration is shown in the following figure:

  Task Scheduling and CPU Deep Explanation in SQL Server     

You can also configure the max worker thread option through T-sql, as shown in the following example by sp_configure


USE AdventureWorks2012 ; 
GO 
EXEC sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE ; 
GO 
EXEC sp_configure 'max worker threads', 900 ; 
GO 
RECONFIGURE;

Max Worker Threads server configuration options do not take into account threads such as high availability, Service Broker, Lock management, etc. If the number of configured threads exceeds that, the following query will provide additional thread information about the system tasks generated.

Is_user_process = 0 denotes system tasks and non-user tasks.


SELECT s.session_id, r.command, r.status, r.wait_type, r.scheduler_id, w.worker_address, 
w.is_preemptive, w.state, t.task_state, t.session_id, t.exec_context_id, t.request_id 
FROM sys.dm_exec_sessions AS s 
INNER JOIN sys.dm_exec_requests AS r 
ON s.session_id = r.session_id 
INNER JOIN sys.dm_os_tasks AS t 
ON r.task_address = t.task_address 
INNER JOIN sys.dm_os_workers AS w 
ON t.worker_address = w.worker_address 
WHERE s.is_user_process = 0;

The following shows the number of active sessions per user


SELECT login_name ,COUNT(session_id) AS session_count 
FROM sys.dm_exec_sessions 
WHERE status<>'sleeping'
GROUP BY login_name; 

The following table shows the maximum number of automatic configuration threads for various combinations of CPUs and SQL Server.

Number of CPUs

32-bit computer

64-bit computer

<= 4 processors

256

512

8 processors

288

576

16 processors

352

704

32 processors

480

960

64 processors

736

1472

128 processors

4224

4480

256 processors

8320

8576

According to Microsoft’s recommendation: This option is an advanced option that should be changed only by experienced database administrators or certified SQL Server professionals. If you suspect performance problems, it may not be the availability of worker threads. The reason is more like I/O, which causes the worker thread to wait. Before changing the maximum worker thread settings, it’s best to find the root cause of the performance problem.

II. Scheduling Principle

2.1 Scheduler task scheduling

A Scheduler of Sqlserver corresponds to a logical CPU on the operating system for task allocation. Scheduling starts at the NUMA node level. The basic algorithm is a cyclic scheduling for new connections. When each new connection arrives, it is assigned to a loop-based scheduler. Within the same NUMA node, a new connection is allocated to the scheduler with the minimum load factor.

2.2  Worker

Worker is also called Worker Thread. Each Worker and a thread are the executing units of Sql server tasks. Multiple Workers correspond to a Scheduler, the formula Workers = max worker threads / Onlines scheduler. On a Scheduler, only one Worker can run at the same time. For example, a 64-bit operating system with four processors has a Worker of 512/4 = 128 for each Scheduler.

2.3  Task

The smallest task unit running on Worker. The simplest Task is a simple Batch. When a session makes a request, Sql server splits the request into one or more tasks (Tasks) and associates the corresponding number of worker threads.

For example, here are two Tasks, which may not be the same Worker. Two Workers may not be the same Scheduler.


select @@servername
Go
select getdate()
GO

Each Task thread has three states:

  • Running: A processor can only do one thing at a time. When a thread is running on a processor, the state of the thread is running.
  • Suspended: When there are insufficient resources, the current thread abandons its ownership of the processor and becomes suspended.
  • Runnable: A thread has completed its wait, but before it’s turn to run, it becomes a runnable state, a signal wait.

2.4 Yielding

Yelding means that workers running on all logical schedulers are non-preemptive. On Scheduler, workers give up to other workers because of resource waiting, which is called Yielding.

Following is a description of several states of occurrence:

1. When Woker runs over 4 ms on Scheduler, Yielding is done.

2. Yielding is done once for every 64K result set sorting.

3. In the process of compiling statement Complie, when this process takes up CPU resources, there are often Yielding and so on.

2.5 Scheduling diagram is as follows:

Task Scheduling and CPU Deep Explanation in SQL Server             

2.5 Task’s schedule diagram is as follows:

Task Scheduling and CPU Deep Explanation in SQL Server                 

1. When Task is Running, it is Schedler’s active Worker.

2. When Task waits only for the CPU to run, it is placed in a Schedler runnable queue.

3. When Task is waiting for a resource (such as lock, disk input/output, etc.), it is in the Suspended suspended state.

4. If the Task Scheduler pending state completes the wait, it will be placed at the end of Scheduler’s Runable queue.

5. If the running thread yidling yields automatically, put it back at the end of Scheduler’s Runable queue.

6. If a running thread needs to wait for a resource, it will be called out of the Scheduler scheduler and enter the pending state Waiter list.

7. If the running thread completes its work, the first thread at the top of the Runnable queue becomes a “running” thread.

3. Viewing with DMV tasks

3.1. See the relationship between scheduler and CPU through sys.dm_os_sys_info as follows:


 SELECT cpu_count,max_workers_count,scheduler_count FROM sys.dm_os_sys_info

Task Scheduling and CPU Deep Explanation in SQL Server  

3.2 View the maximum number of Workers


select max_workers_count from sys.dm_os_sys_info  

3.3 View the relationship between Task and Worker

-- In each connection, we may have many batches, broken down into tasks to support parallel queries, for example
 select task_address,task_state,scheduler_id,session_id,worker_address 
 from sys.dm_os_tasks where session_id>50

select state,last_wait_type,tasks_processed_count,task_address, worker_address, scheduler_address
 from sys.dm_os_workers where worker_address =0x00000000043621A0

Task Scheduling and CPU Deep Explanation in SQL Server

3.4 Check Scheduler

Schduler_id<255 represents the CPU of the user and, conversely, the SYSTEM SCHEDULER
SELECT
 scheduler_id,
 cpu_id,
 is_online,
 current_tasks_count,
 runnable_tasks_count,
 current_workers_count,
 active_workers_count,
 work_queue_count
 FROM sys.dm_os_schedulers
 WHERE scheduler_id < 255

Cpu_id: The associated cpu. Scheduler such as CPU ID >= 255 is used for internal use of the system. For example, resource management, DAC, backup and restore operations.

Is_online: 0 scheduler offline, 1 online.

Curr_tasks_count: Number of current tasks, status including: (wait, run, completed).

Runnable_tasks_count: To assign tasks and the number of tasks waiting to be scheduled in a runnable queue, this value will be 0 if the utilization rate is low.

Curr_workers_count: The number of threads associated with this scheduler. Includes idle thread work.

Active_workers_count: The number of threads currently processing activities, which must be associated with task, including running, runnable, suspend.

Work_queue_count: The number of task wait in the queue, if not 0, means the pressure of thread exhaustion.

Speaking of this, I will talk about the analysis of CPUf over-high.

Reference:

  Troubleshooting SQL Server Scheduling and Yielding

Microsoft SQL Server Enterprise Platform Management Practice

  How It Works: SQL Server 2012 Database Engine Task Scheduling

summary

Above is the whole content of this article. I hope that the content of this article has a certain reference learning value for everyone’s study or work. If you have any questions, you can leave a message to exchange. Thank you for your support to developpaer.