人人都是 DBA(III)SQL Server 调度器

在 SQL Server 中,当数据库启动后,SQL Server 会为每个物理 CPU(包括 Physical CPU 和 Hyperthreaded)创建一个对应的任务调度器(Scheduler),Scheduler 可以看作为逻辑 CPU(Logical CPU)。

根据 Affinity Mask 选项的配置,Scheduler 的状态被设置为 ONLINE 或 OFFLINE。使用下面的 SQL 来查询当前环境中 Scheduler 的状态。

人人都是 DBA(III)SQL Server 调度器
SELECT is_online
    ,[status]
    ,COUNT(*) AS [count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
GROUP BY is_online
    ,[status];
人人都是 DBA(III)SQL Server 调度器

人人都是 DBA(III)SQL Server 调度器

默认的 Affinity Mask 是 0,也就是所有 Scheduler 均为 ONLINE。

SELECT *
FROM sys.configurations
WHERE [name] LIKE '%affinity%';

人人都是 DBA(III)SQL Server 调度器

例如,如果把 Affinity Mask 设置为 3,即 00000011,则意味着只有 0 和 1 号 CPU 可以使用。

假设有 64 个 CPU,则常用的 Affinity Mask 值有:

  • 255 -> 0xFF
  • 65280 -> 0xFF00
  • 16711680 -> 0xFF0000
  • 4278190080 -> 0xFF000000
  • 4294967040 -> 0xFFFFFF00
  • -256 -> 0xFFFFFF00

下面的 VM 的配置为 4 * 8 = 32 Logical CPUs 情况。

人人都是 DBA(III)SQL Server 调度器

设置 NumaNode0 上的 8 个 CPU 用于 I/O,其他 3 个节点用于 Processor。

人人都是 DBA(III)SQL Server 调度器

人人都是 DBA(III)SQL Server 调度器

Scheduler 负责根据需求创建和销毁 Worker,一个 Worker 即可是一个 Thread 也可以是一个 Fiber,可以通过 Max Worker Threads 和 Use Windows Fibers 配置项来进行设置。

Max Worker Threads 选项负责限制线程池(Threading Pool)中线程的最大数量。

SELECT *
FROM sys.configurations
WHERE [name] LIKE '%worker%';

人人都是 DBA(III)SQL Server 调度器

默认值为 0,即允许 SQL Server 根据 CPU 和版本情况进行自动配置最大线程数量。

 Number of CPUs

 32-bit Computor 

 64-bit Computor 

 <= 4 processors 

 256

 512

8 processors

 288

 576

 16 processors

 352

 704

 32 processors

 480

 960

 64 processors

 736

 1472

可以使用 sp_configure 来配置该选项。

人人都是 DBA(III)SQL Server 调度器
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'max worker threads', 900;
GO
RECONFIGURE;
GO
人人都是 DBA(III)SQL Server 调度器

使用如下 SQL 查询来查看当前数据库环境中的 Max Workers Count 和 Current Workers Count。

SELECT max_workers_count FROM sys.dm_os_sys_info;
SELECT SUM(current_workers_count) AS current_workers_count FROM sys.dm_os_schedulers;

人人都是 DBA(III)SQL Server 调度器

Worker 直接使用 Scheduler,每个 Worker 只会关联到 1 个 Scheduler,Worker 不能从一个 Scheduler 转移到另一个 Scheduler 上。

Worker 处理的工作单元可以是一个 Request,也可以是一个 Task。比如批处理 Request 可能被分解成多个 Task。当 Scheduler 接收到新的 Request 或 Task 请求时,如果当前没有空闲 Worker(Idle Worker),则根据配置开始创建新的 Worker,而 Request 或 Task 将被绑定到该 Worker 上。

SELECT is_idle
    ,COUNT(*) AS [count]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
    AND is_online = 1
GROUP BY is_idle;

人人都是 DBA(III)SQL Server 调度器

sys.dm_os_schedulers 中的 scheduler_id < 255 则为常规查询,如果 scheduler_id >= 255 则为系统内调度。

如果 Worker 已经空闲了至少 15 分钟以上,或者 SQL Server 检测到有内存压力时,空闲的 Worker 可能被销毁。

  • 在 32 位机上,1 个 Worker 至少占用 0.5M 的内存。
  • 在 64 位机上,1 个 Worker 至少占用 2M 的内存。

所以,销毁空闲的 Worker 以释放内存可以立即改善系统对内存的迫切需求。

SQL Server 设计了非常高效的 Worker Pool,所以即使有大量的并发在访问数据库,可能 Worker Pool 的大小仍远小于配置的 Max Worker Threads 的值。但尽管如此,如果 Worker 中处理的 Task 发生了锁定或者等待 IO 完成等阻塞操作,Worker 即会被阻塞,Worker 不会其他任何请求直到阻塞条件解除。

人人都是 DBA(III)SQL Server 调度器
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]
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
    AND is_online = 1;
人人都是 DBA(III)SQL Server 调度器

人人都是 DBA(III)SQL Server 调度器

SQL Server 中的 Session 实际上只描述了建立连接后的通道,通过该通道可以发送 Request,通道也可以保持空闲。所以 Session 不会与特定的 Scheduler 进行绑定。

人人都是 DBA(III)SQL Server 调度器
SELECT s.session_id
    ,r.command
    ,r.[status]
    ,r.wait_type
    ,r.scheduler_id
    ,w.is_preemptive
    ,t.task_state
    ,u.cpu_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
INNER JOIN sys.dm_os_schedulers AS u ON t.scheduler_id = u.scheduler_id
WHERE s.is_user_process = 0
ORDER BY r.scheduler_id;
人人都是 DBA(III)SQL Server 调度器

人人都是 DBA(III)SQL Server 调度器

当 Session 建立后,会将当前负载最低的 Scheduler 分配给该 Session。然后,当 Session 中有新的 Request 抵达时,SQL Server 会将最近处理过该 SPID 中 Request 的 Scheduler 作为推荐的调度器(Preferred Scheduler)优先调度。尽管如此,当 Session 中抵达的 Request 开始排队时,SQL Server 会计算每个 Scheduler 的 Load Factor,寻找负载最低的 Scheduler 来处理任务。

 

《人人都是 DBA》系列文章索引:

 序号 

 名称 

1

 人人都是 DBA(I)SQL Server 体系结构

2

 人人都是 DBA(II)SQL Server 元数据

3

 人人都是 DBA(III)SQL Server 调度器

4

 人人都是 DBA(IV)SQL Server 内存管理

5

 人人都是 DBA(V)SQL Server 数据库文件

6

 人人都是 DBA(VI)SQL Server 事务日志

7

 人人都是 DBA(VII)B 树和 B+ 树

8

 人人都是 DBA(VIII)SQL Server 页存储结构

9

 人人都是 DBA(IX)服务器信息收集脚本汇编

10

 人人都是 DBA(X)资源信息收集脚本汇编

11

 人人都是 DBA(XI)I/O 信息收集脚本汇编

12

 人人都是 DBA(XII)查询信息收集脚本汇编

13

 人人都是 DBA(XIII)索引信息收集脚本汇编

14

 人人都是 DBA(XIV)存储过程信息收集脚本汇编 

15

 人人都是 DBA(XV)锁信息收集脚本汇编







本文转自匠心十年博客园博客,原文链接:http://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_sqlserver_scheduler.html,如需转载请自行联系原作者
上一篇:在项目中使用Google托管的JavaScript库


下一篇:vim 常用快捷键