SqlServer基础--SQLOS 的任务调度（转）-优快云博客

本文介绍了SQL Server如何通过WORKER、SCHEDULER和TASK等组件进行任务调度和处理。详细解析了这些组件间的相互作用及其对SQL Server性能的影响，并提供了具体的跟踪案例。

https://blogs.msdn.microsoft.com/apgcdsd/2011/11/23/sql-server-sqlos/

【介绍】

SQL Server 通过 WORKER, SCHEDULER, TASK 等来对任务进行调度和处理。了解这些概念，对于了解 SQL Server 内部是如何工作，是非常有帮助的。

通常来讲， SCHEDULER 个数是跟 CPU 个数相匹配的。除了几个系统的 SCHEDULER 以外，每一个 SCHEDULER 都映射到一个 CPU ，如下面的查询结果所示，我们有四个 CPU ，也就有相应四个 SCHEDULER 。

SELECT cpu_count,scheduler_count,scheduler_total_count,max_workers_count FROM sys.dm_os_sys_info

而 WORKER ( 又称为 WORKER THREAD), 则是工作线程。在一台服务器上，我们可以有多个工作线程。因为每一个工作线程要耗费资源，所以， SQL Server 有一个最大工作线程数。一个 TASK 进来，系统会给它分配一个工作线程进行处理。但是当所有的工作线程都在忙，而且已经达到了最大工作线程数， SQL Server 就要等待，直到有一个忙的工作线程被释放。最大工作线程数可以通过下面的查询得到。 SQL SERVER 并不是一开始就把这些所有的工作线程都创建，而是依据需要而创建。

SELECT cpu_count,scheduler_count,scheduler_total_count,max_workers_count FROM sys.dm_os_sys_info

TASK 是由 BATCH 而来。我们知道，一个连接，可以包含多个 BATCH ，而每个 BATCH 则可以分解成多个 TASK 。如下面某一个连接要做的事情。这个连接要做的有两个 BATCH ，而每个 BATCH ，如 SELECT * FROM TABLE_B ，因为可以支持并行化查询，所以可能会被分解成多个 TASK 。具体 BATCH 怎么分解成 TASK ，以及分解成多少个，则是由 SQL Server 内部决定的。

INSERT INTO TABLE_B VALUES (‘aaa’)

SELECT * FROM TABLE_B

【关系】

我们初步了解了 Connection, Batch, Task, Worker, Scheduler, CPU 这些概念，那么，它们之间的关系到底是怎么样呢？

如上图所示，左边是很多连接，每个连接有一个相应的 SPID ，只要用户没有登出，或者没有 timeout, 这个始终是存在的。标准设置下，对于用户连接数目，是没有限制的。

在每一个连接里，我们可能会有很多 batch ，在一个连接里， batch 都是按顺序的。只有一个 batch 执行完了，才会执行下面一个 batch 。因为有很多连接，所以从 SQL Server 层面上看，同时会有很多个 batch 。

SQL Server 会做优化，每一个 batch ，可能会分解成多个 task 以支持如并行查询。这样，在 SQL 层面上来看，同时会有很多个 TASK 。

SQL Server 上，每一个 CPU 通常会对应一个 Scheduler, 有几个额外的系统的 Scheduler ，只是用来执行一些系统任务。对用户来讲，我们只需要关心 User Scheduler 就可以了。如果有 4 个 CPU 的话，那么通常就会有 4 个 User Scheduler 。

每个 Scheduler 上，可以有多个 worker 对应。 Worker 是真正的执行单元， Scheduler （对 CPU 的封装）是执行的地方。 Worker 的总数受 max worker thread 限制。每一个 worker 在创建的时候，自己需要申请 2M 内存空间。如果 max worker thread 为 1024 ，并且那些 worker 全部创建的话，至少需要 2G 空间。所以太多的 worker ，会占用很多系统资源。

【跟踪】

我们了解了 Connection, Batch, Task, Worker, Scheduler, CPU 之间的关系，下面我们用 DMV 跟踪一下运作的流程。

步骤一：

执行下面的脚本，创建一个测试数据库和测试数据表

CREATE DATABASE Test4;

USE Test4;

CREATE TABLE Test

(ID INT,

name NVARCHAR(50)

);

INSERT INTO Test

VALUES(1,'aaa');

步骤二：

打开一个查询窗口，执行下面的语句，注意，我们这里并没有 commit transaction.

USE Test4

BEGIN TRAN;

UPDATE Test SET name = 'bbb'

WHERE [ID] = 1;

步骤三：

打开另外一个窗口，执行下面的语句，我们会看到，下面的查询会一直在执行，因为我们前面的一个 transaction 并没有关闭。从查询窗口，我们可以看到，下面语句执行的 SPID 为 58

USE Test4

SELECT * FROM Test

步骤四：查看连接。

从下面的查询来看，我们的连接对应的 SPID 是 58 ，被 block 住了。

SELECT * FROM sys.sysprocesses where spid=56

步骤五：查看 batch

我们查看 SQL Profiler, 看到我们的 Batch 是 SELECT * FROM TEST

步骤六：查看 TASK

用下面的 DMV, 我们可以看到，针对 SESSION_ID=58 的，只有一个 task. ( 地址为 0x0064F048), 而针对该 TASK 的 worker 地址为 : 0x803081A0 。同时我们也可以看到该 worker 运行在 Scheduler 0 上面。

步骤七：查看 WORKER

从下面的查询可以知道，这个 WORKER 已经执行了 5291 个 task 了。这个 worker 相应的 Scheduler 地址是 0x00932080

SELECT * FROM sys.dm_os_tasks where session_id=56

步骤八：查看 SCHEDULER

从下面的查询可以得知， Scheduler_address (0x00932080) 相应的 CPU_ID 是 0 。在我们的系统上，有 4 个 CPU, 编号分别为 0 ， 1 ， 2 ， 3. 但是有 7 个 SCHEDULER, 其中 3 个是 SYSTEM SCHEDULER, 4 个是 USER SCHEDULER 。在每个 SCHEDULER 上，有相应的 WORKER 数目。因为 WORKER 是根据需要而创建的，所以，在每个 SCHEDULER 上，目前 WORKER 数目很少。而且其中有些 WORKER 还处于 SLEEPING 状态。

SELECT * FROM sys.dm_os_schedulers

【应用】

我们了解了 SQL SERVER 任务调度的机制，那么有些问题，就会更加清楚。

设置 MAXDOP 的作用。 MAXDOP=1 的话，可以使得一个 BATCH 只对应一个 TASK 。如果一个 BATCH 产生多个 TASKS ，那么 TASK 之间的协调，等待等等，将是很大的开销。把 MAXDOP 设小，能同时减少 WORKER 的使用量。所以，如果我们看到等待类型为 CXPACKET 的话，那么我们可以设置 MAXDOP ，减少并行度。

比较大的 SPID 。如果我们看到 SPID 的号码非常大，如超过 1000, 那么通常表明，我们系统有很严重的 BLOCKING 。 SQL SERVER 不对连接数做限制，但是对于 WORKER 数，是有限制的。缺省情况下，最大个数如下：

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

对于很大的 SPID 编号，通常表明，我们的 WORKER 数是很高的。这种情况比较危险，如果一个新的连接进来，可能没有空闲 WORKER 来处理这个连接。在 CLUSTER 环境下， ISALIVE 检查会失败，会导致 SQL SERVER 做 FAILOVER 。

NON-YIELDING SCHEDULER 错误。我们有时候会看到 SQL Server 会报一个 17883 错误， NON-YIELDING SCHEDULER 。这个错误指的是，在一个 SCHEDULER 上，会有多个 WORKER ，它们以友好的方式，互相占用一会儿 SCHEDULER 资源。某个 WORKER 占用 SCHEDULER 后，执行一段时间，会做 YIELD ，也就是退让，把 SCHEDULER 资源让出来，让其他 WORKER 去使用。如果某一个 WORKER 出于某种原因，不退让 SCHEDULER 资源，导致其他 WORKER 没有机会运行，这种现象叫 NON-YIELDING SCHEDULER 。出现这种情况， SQL SERVER 有自动检测机制，会打一个 DUMP 出来。我们需要进一步分析 DUMP 为什么该 WORKER 不会 YIELD 。

WORKER 用完。我们可以做一个小实验。我们在一台 32 位机器上，创建上面提及的测试数据库，并且，开启一个同样的未关闭 transaction 的 update 语句。

然后执行下面的程序。下面的程序会开启 256 个连接到 SQL Server, 这 256 个连接由于前面的 transaction 未闭合，都处于 BLOCKING 状态。

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 = "sqlcmd.exe" ;

startInfo.Arguments = " -E -S SERVERNAME -d TEST -q \" SELECT * FROM TEST \"" ;

Process .Start(startInfo);

}

查询 SELECT * FROM sys . dm_os_tasks 这时候我们发现有 278 个 TASK ，而查询 sys . dm_os_schedulers 我们发现有两个 CPU, 因此有两个用户 SCHEDULER, 每个 SCHEDULER 上，有 128 个 workers. 加起来有 256 个 WORKERS 。针对两个 CPU 的架构，我们缺省最大的 WORKER 数是 256 。所以已经到了极限了。

这时候，我们新开启一个连接，会发现 SQL Server 连不上，并报如下错误 :

这是因为 WORKER 用完的缘故。新的连接无法获得一个 WORKER 来做 login process 。所以导致连接失败。在群集环境下，如果连接不上 SQL Server, ISALIVE 检查会失败，会引起 SQL Server FAILOVER 。所有的连接都会被强迫中止，并且 SQL Server 会在新结点上重新启动。针对这种情况，我们可以修改提高 MAX WORKER THREAD ，但是并不能最终解决问题，由于 BLOCKING 缘故，新的连接会迅速积累，一直把 MAX WORKER THREAD 用完，所以这时候，我们应该检查 BLOCKING 。使得 task 能及时完成，释放 WORKER 。

PS C:\WINDOWS\system32> sqlcmd.exe /?

Microsoft (R) SQL Server Command Line Tool

Version 11.0.2100.60 NT x64

usage: Sqlcmd [-U login id] [-P password]

[-S server] [-H hostname] [-E trusted connection]

[-N Encrypt Connection][-C Trust Server Certificate]

[-d use database name] [-l login timeout] [-t query timeout]

[-h headers] [-s colseparator] [-w screen width]

[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]

[-c cmdend] [-L[c] list servers[clean output]]

[-q "cmdline query"] [-Q "cmdline query" and exit]

[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]

[-u unicode output] [-r[0|1] msgs to stderr]

[-i inputfile] [-o outputfile] [-z new password]

[-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]

[-k[1|2] remove[replace] control characters]

[-y variable length type display width]

[-Y fixed length type display width]

[-p[1] print statistics[colon format]]

[-R use client regional setting]

[-K application intent]

[-M multisubnet failover]

[-b On error batch abort]

[-v var = "value"...] [-A dedicated admin connection]

[-X[1] disable commands, startup script, environment variables [and exit]]

[-x disable variable substitution]

[-? show syntax summary]

sqlcmd.exe -U sa -P 123456 -S localhost -d Test4 -q " SELECT * FROM TEST "

【总结】

SQL Server 的任务调度使得 SQL SERVER 能够以最快方式处理用户发过来的请求。了解 SQL SERVER 的任务调度过程，对于我们调整系统性能是非常有帮助的。如适当增加 MAX WORKER THREAD ，调整 MAXDOP ，去除 BLOCKING 等等，了解这些概念，会使得我们的调整更有目的性。