Everyone is a DBA (III) SQL Server Scheduler

In SQL Server, when a database is started, SQL Server creates a corresponding Task scheduler (Scheduler) for each physical CPU (including physical CPU and hyperthreaded), and Scheduler can be thought of as a logical CPU (Logical CPU).

Depending on the configuration of the Affinity Mask option, the Scheduler status is set to ONLINE or OFFLINE. Use the following SQL to query the status of Scheduler in the current environment.

SELECTIs_online,[Status]    ,COUNT(*) as [Count] fromsys.dm_os_schedulersWHEREscheduler_id< 255GROUP  byIs_online,[Status];

The default Affinity Mask is 0, which means that all Scheduler are ONLINE.

SELECT *  from sys.configurations WHERE [name]  like ' %affinity% ';

For example, if Affinity Mask is set to 3, or 00000011, it means that only 0 and 1th CPUs can be used.

Assuming there are 64 CPUs, the commonly used Affinity Mask values are:

• 0xFF, 255
• 65280-0XFF00
• 16711680-0xFF0000
• 4278190080-0xff000000
• 4294967040-0XFFFFFF00
• -256-0XFFFFFF00

The following VM configuration is 4 * 8 = Logical CPUs case.

Set the 8 CPUs on the NumaNode0 for I/O and the other 3 nodes for the Processor.

Scheduler is responsible for creating and destroying workers on demand, a worker can be a Thread or a Fiber, and can be set through the Max Worker Threads and use Windows fibers configuration items.

The max Worker Threads option is responsible for limiting the maximum number of threads in the thread pool (threading pool).

SELECT *  from sys.configurations WHERE [name]  like ' %worker% ';

The default value is 0, which allows SQL Server to automatically configure the maximum number of threads based on CPU and version conditions.

Number of CPUs	32-bit Computor	64-bit Computor
<= 4 Processors	256	512
8 Processors	288	576
Processors	352	704
Processors	480	960
Processors	736	1472

You can use sp_configure to configure this option.

 exec  sp_configure  " show Advanced options   ", 1   go  reconfigure   go  exec  sp_configure  " max worker threads  , 900   go  reconfigure   go  

Use the following SQL query to view Max Workers count and current Workers count in the currently database environment.

SELECT  from Sys.dm_os_sys_info; SELECT SUM  as  from Sys.dm_os_schedulers;

The worker uses Scheduler directly, and each worker is only associated to 1 Scheduler,worker and cannot be transferred from one Scheduler to another Scheduler.

A worker's working unit can be either a Request or a task. For example, batch Request may be decomposed into multiple tasks. When the Scheduler receives a new request or task demand, if no idle worker is currently present, a new worker is created based on the configuration, and the request or task is bound to the worker.

SELECT is_idle    ,count(*as[Count] from Sys.dm_os_schedulersWHERE<255and    =1  GROUP by Is_idle;

scheduler_id < 255 in Sys.dm_os_schedulers is a regular query, and if scheduler_id >= 255 is scheduled within the system.

If the worker has been idle for at least 15 minutes, or if SQL Server detects a memory pressure, the idle worker may be destroyed.

• On a 32-bit machine, 1 workers consume at least 0.5M of memory.
• On a 64-bit machine, 1 workers consume at least 2M of memory.

Therefore, destroying idle workers to free memory can immediately improve the system's urgent need for memory.

SQL Server designed a very efficient worker pool, so even with a large number of concurrent accesses to the database, the size of the worker pool might still be much smaller than the configured max worker Threads value. However, if a Task that is processed in a worker is locked or waits for blocking operations such as IO completion, the worker is blocked and the worker does not have any other requests until the blocking condition is lifted.

SELECT AVG(Current_workers_count) as [Avg_current_workers_count]    ,AVG(Active_workers_count) as [Avg_active_workers_count]    ,MAX(Current_workers_count) as [Max_current_workers_count]    ,MAX(Active_workers_count) as [Max_active_workers_count]    ,SUM(Current_workers_count) as [Total_current_workers_count]    ,SUM(Active_workers_count) as [Total_active_workers_count]    ,SUM(Pending_disk_io_count) as [Total_pending_disk_io_count] fromsys.dm_os_schedulersWHEREscheduler_id< 255     andIs_online= 1;

The Session in SQL Server actually only describes the channel after the connection is established, through which the Request can be sent and the channel can remain idle. Therefore, the Session is not bound to a particular Scheduler.

SELECTs.session_id, R.command, R.[Status], R.wait_type, r.scheduler_id, W.is_preemptive, T.task_state, u.cpu_id fromSys.dm_exec_sessions assINNER JOINSys.dm_exec_requests asR ons.session_id=r.session_idINNER JOINSys.dm_os_tasks asT onR.task_address=t.task_addressINNER JOINSys.dm_os_workers asW onT.worker_address=w.worker_addressINNER JOINSys.dm_os_schedulers asU ont.scheduler_id=u.scheduler_idWHERES.is_user_process= 0ORDER  byr.scheduler_id;

When the session is established, the Scheduler with the lowest current load is assigned to the session. Then, when a new request arrives in the Session, SQL Server prioritizes the Scheduler that recently processed the request in the SPID as the recommended scheduler (Preferred Scheduler). However, when the Request arrives in the Session begins to queue, SQL Server calculates the load Factor for each Scheduler, looking for the least loaded Scheduler to handle the task.

This series of articles "Everyone is a DBA" by Dennis Gao published from the blog Park Personal technology blog, without the author's consent to prohibit any form of reproduction, any automatic or artificial reptile reproduction or plagiarism behavior are bullying.

Everyone is a DBA (III) SQL Server Scheduler