【介绍】
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’)
GO
SELECT * FROM TABLE_B
GO
【关系】
我们初步了解了
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;
GO
USE Test4;
GO
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
数，是有限制的。缺省情况下，最大个数如下：
对于很大的
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
Copyright (c) 2012 Microsoft. All rights reserved.
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
等等，了解这些概念，会使得我们的调整更有目的性。
