基于SQL Server OS的任务调度机制详解
SQL Server OS是在Windows之上，用于服务SQL Server的一个用户级别的操作办法系统层次。它将操作办法系统部分的功能从整个SQL Server引擎中抽象出来，单独形成一层，以便为存储引擎,网站数据库层面的任务调度的起源是ACM上的一篇名为“Operating System Support for Database Management”。但是对于Windows来说，在操作办法系统层面专门加入支,在Windows NT4之后，Windows任务调度是抢占式的，也就是说Windows任务是根据任务的优先级和时间片来决定。假如如果一个任务的时间片用完，或是有更高优先级的任务正在等待，那么操作办法系统可以强,但是对于SQL Server来说，这种非合作式的、基于时间片的任务调度机制就不那么合适了。假如如果SQL Server使用Windows内的任务调度机制来进行任务调度的话，Windows不会根据SQL S,而对于SQL Server OS来说，线程调度采用的合作模式而不是抢占模式。这是因为这些网站数据库内的任务都在SQL Server这个SandBox之内，SQL Server充分相信其内线程，所以除非线程,SQL Server决定哪一个时间点哪一个线程运行，是通过一个叫Scheduler的东西进行的，下面让咱们来看Scheduler。,SQL Server中每一个逻辑CPU都有一个与之对应的Scheduler，只有拿到Scheduler所有权的任务才允许被执行，Scheduler可以看做一个队SQLOS来说的逻辑CPU。您可以通过s,我的笔记本是一个i7四核8线程的CPU，对应的，可以看到除了DAC和运行系统任务的hIDDEN Scheduler，剩下的Scheduler一共8个，每个对应一个逻辑CPU，用于处理内部Task。当然,此时，无需重启实例就能看到4个Scheduler被Offline,如图3所示:,一般来说，除非您的站点服务器上运行其他实例或程序，否则不需要控制Affinity。,在图1中，咱们还注意到，除了Visible的Scheduler之外，还有一些特殊的Scheduler，这些Scheduler的ID都大于255，这类Scheduler都用于系统内部使用，比如说资源管理,这意味着对于一个比较长的查询，可以前半部分在CPU0上执行，而后半部分在CPU1上执行。,另外，在每一个Scheduler上，同一时间只能有一个Worker运行，所有的资源都就绪但没有拿到Scheduler，那么这个Worker就处于Runnable状态。下面让咱们来看一看Worker。,每一个Worker可以看做是对应一个线程（或纤程），Scheduler不会直接调度线程，而是调度Worker。Worker会随着负载的增加而增加，换句话说，Worker是按需增加，直到增加到最大数字。,假如如果是自动配置，那么SQL Server的最大工作线程数量可以在sys.dm_os_sys_info中看到，如图5所示。,Worker实际上会对应Windows上的一个线程，并与某个特定Scheduler绑定，每一个Worker只要开始执行Task,除非Task完成，否则Worker永远不会放弃这个Task,假如如果一个Ta,此外，同一个连接内的多个Batch之间倾向于使用同一个Worker,比如第一个Batch使用了Worker 100,那么第二个Batch也同样倾向于是用Worker 100，但这并不绝对。,正在运行的任务所是用的Worker，咱们可以通过DMV sys.dm_exec_requests查看正在运行的任务，其中的Task_Address列可以看到正在运行的Task,再通过sys.dm_os,SQL Server会为每一个Worker保留大约2M左右的内存，对于每一个Scheduler上所能有的Worker数量是站点服务器的最大Worker数量/在线的Scheduler,每一个Schedule,那么当一个Scheduler空闲超过15分钟，或是Windows面临内存压力时。SQL Server就会尝试Trim这个Worker池来释放被Worker所占用的内存。,Task是Worker上运行的最小任务单元。只能拿到Worker的Task才能够运行。咱们可以看下面一个简单的例子，如具体相关代码1所示。,SELECT @@VERSION goSELECT @@SPID go
开始
这是去年的问题了，今天在整理邮件的时候才发现这个问题，感觉顶有意思的，特记录下来。
在表RelationGraph中，有三个字段（ID,Node,RelatedNode）,其中Node和RelatedNode两个字段描述两个节点的连接关系；现在要求，找出从节点"p"至节点"j"，最短路径（即经过的节点最少）。
图1.
解析：
了能够更好的描述表RelationGraph中字段Node和 RelatedNode的关系，我在这里特意使用一个图形来描述，
如图2.
图2.
在图2，可清晰的看出各个节点直接如何相连，也可以清楚的看出节点"p"至节点"j"的的几种可能路径。
从上面可以看出第2种可能路径，经过的节点最少。
为了解决开始的问题，我参考了两种具体相关方法，
第1具体相关方法是，
参考单源最短路径算法：
图3.
第2具体相关方法是，
针对第1种具体相关方法的改进，就是采用多源点具体相关方法，这里就是以节点"p"和节点"j"为中心向外层扩展，直到两圆外切点，如图4. ：
图4.
实现：
在接下来，我就描述在SQL Server中，如何实现。当然我这里采用的前面说的第2种具体相关方法，以"P"和"J"为始点像中心外层层扩展。
这里提供有表RelactionGraph的create& Insert数据的脚本：
复制具体相关代码 具体相关代码如下:
use TestDB
go
if object_id('RelactionGraph') Is not null drop table RelactionGraph
create table RelactionGraph(ID int identity,Item nvarchar(50),RelactionItem nvarchar(20),constraint PK_RelactionGraph primary key(ID))
go
create nonclustered index IX_RelactionGraph_Item on RelactionGraph(Item) include(RelactionItem)
create nonclustered index IX_RelactionGraph_RelactionItem on RelactionGraph(RelactionItem) include(Item)
go
insert into RelactionGraph (Item, RelactionItem ) values
('a','b'),('a','c'),('a','d'),('a','e'),
('b','f'),('b','g'),('b','h'),
('c','i'),('c','j'),
('f','k'),('f','l'),
('k','o'),('k','p'),
('o','i'),('o','l')
go
编写一个存储过程up_GetPath
复制具体相关代码 具体相关代码如下:
use TestDB
go
--Procedure:
if object_id('up_GetPath') Is not null
Drop proc up_GetPath
go
create proc up_GetPath
(
@Node nvarchar(50),
@RelatedNode nvarchar(50)
)
As
set nocount on
declare
@level smallint =1, --当前搜索的深度
@MaxLevel smallint=100, --最大可搜索深度
@Node_WhileFlag bit=1, --以@Node作为中心进行搜索时候，作为能否循环搜索的标记
@RelatedNode_WhileFlag bit=1 --以@RelatedNode作为中心进行搜索时候，作为能否循环搜索的标记
--假如如果直接找到两个Node存在直接关系就直接返回
if Exists(select 1 from RelationGraph where (Node=@Node And RelatedNode=@RelatedNode) or (Node=@RelatedNode And RelatedNode=@Node) ) or @Node=@RelatedNode
begin
select convert(nvarchar(2000),@Node ' --> ' @RelatedNode) As RelationGraphPath,convert(smallint,0) As StopCount
return
end
--
if object_id('tempdb..#1') Is not null Drop Table #1 --临时表#1，存储的是以@Node作为中心向外扩展的各节点数据
if object_id('tempdb..#2') Is not null Drop Table #2 --临时表#2，存储的是以@RelatedNode作为中心向外扩展的各节点数据
create table #1(
Node nvarchar(50),--相对源点
RelatedNode nvarchar(50), --相对目标
Level smallint --深度
)
create table #2(Node nvarchar(50),RelatedNode nvarchar(50),Level smallint)
insert into #1 ( Node, RelatedNode, Level )
select Node, RelatedNode, @level from RelationGraph a where a.Node =@Node union --正向：以@Node作为源查询
select RelatedNode, Node, @level from RelationGraph a where a.RelatedNode = @Node --反向：以@Node作为目标进行查询
set @Node_WhileFlag=sign(@@rowcount)
insert into #2 ( Node, RelatedNode, Level )
select Node, RelatedNode, @level from RelationGraph a where a.Node =@RelatedNode union --正向：以@RelatedNode作为源查询
select RelatedNode, Node, @level from RelationGraph a where a.RelatedNode = @RelatedNode --反向：以@RelatedNode作为目标进行查询
set @RelatedNode_WhileFlag=sign(@@rowcount)
--假如如果在表RelationGraph中找不到@Node 或 @RelatedNode 数据，就直接跳过后面的While过程
if not exists(select 1 from #1) or not exists(select 1 from #2)
begin
goto While_Out
end
while not exists(select 1 from #1 a inner join #2 b on b.RelatedNode=a.RelatedNode) --判断是否出现切点
and (@Node_WhileFlag|@RelatedNode_WhileFlag)>0 --判断是否能搜索
And @level<@MaxLevel --控制深度
begin
if @Node_WhileFlag >0
begin
insert into #1 ( Node, RelatedNode, Level )
--正向
select a.Node,a.RelatedNode,@level 1
From RelationGraph a
where exists(select 1 from #1 where RelatedNode=a.Node And Level=@level) And
Not exists(select 1 from #1 where Node=a.Node)
union
--反向
select a.RelatedNode,a.Node,@level 1
From RelationGraph a
where exists(select 1 from #1 where RelatedNode=a.RelatedNode And Level=@level) And
Not exists(select 1 from #1 where Node=a.RelatedNode)
set @Node_WhileFlag=sign(@@rowcount)
end
if @RelatedNode_WhileFlag >0
begin
insert into #2 ( Node, RelatedNode, Level )
--正向
select a.Node,a.RelatedNode,@level 1
From RelationGraph a
where exists(select 1 from #2 where RelatedNode=a.Node And Level=@level) And
Not exists(select 1 from #2 where Node=a.Node)
union
--反向
select a.RelatedNode,a.Node,@level 1
From RelationGraph a
where exists(select 1 from #2 where RelatedNode=a.RelatedNode And Level=@level) And
Not exists(select 1 from #2 where Node=a.RelatedNode)
set @RelatedNode_WhileFlag=sign(@@rowcount)
end
select @level =1
end
While_Out:
--下面是构造返回的结果路径
if object_id('tempdb..#Path1') Is not null Drop Table #Path1
if object_id('tempdb..#Path2') Is not null Drop Table #Path2
;with cte_path1 As
(
select a.Node,a.RelatedNode,Level,convert(nvarchar(2000),a.Node ' -> ' a.RelatedNode) As RelationGraphPath,Convert(smallint,1) As PathLevel From #1 a where exists(select 1 from #2 where RelatedNode=a.RelatedNode)
union all
select b.Node,a.RelatedNode,b.Level,convert(nvarchar(2000),b.Node ' -> ' a.RelationGraphPath) As RelationGraphPath ,Convert(smallint,a.PathLevel 1) As PathLevel
from cte_path1 a
inner join #1 b on b.RelatedNode=a.Node
and b.Level=a.Level-1
)
select * Into #Path1 from cte_path1
;with cte_path2 As
(
select a.Node,a.RelatedNode,Level,convert(nvarchar(2000),a.Node) As RelationGraphPath,Convert(smallint,1) As PathLevel From #2 a where exists(select 1 from #1 where RelatedNode=a.RelatedNode)
union all
select b.Node,a.RelatedNode,b.Level,convert(nvarchar(2000),a.RelationGraphPath ' -> ' b.Node) As RelationGraphPath ,Convert(smallint,a.PathLevel 1)
from cte_path2 a
inner join #2 b on b.RelatedNode=a.Node
and b.Level=a.Level-1
)
select * Into #Path2 from cte_path2
;with cte_result As
(
select a.RelationGraphPath ' -> ' b.RelationGraphPath As RelationGraphPath,a.PathLevel b.PathLevel -1 As StopCount,rank() over(order by a.PathLevel b.PathLevel) As Result_row
From #Path1 a
inner join #Path2 b on b.RelatedNode=a.RelatedNode
and b.Level=1
where a.Level=1
)
select distinct RelationGraphPath,StopCount From cte_result where Result_row=1
go
上面的存储过程，主要分为两大部分，第1部分是实现如何搜索，第2部分实现如何构造返回结果。其中第1部分的具体相关代码根据前面的具体相关方法2，通过@Node 和 @RelatedNode 两个节点向外层搜索，每次搜索返回的节点都保存至临时表#1和#2，再判断临时表#1和#2有没有出现切点，假如如果出现就说明已找到最短的路径（经过多节点数最少），否则就继续循环搜索，直到循环至最大的搜索深度（@MaxLevel smallint=100）或找到切点。要是到100层都没搜索到切点，将放弃搜索。这里使用最大可搜索深度@MaxLevel，目的是控制由于数据量大可能会导致性能差，因为在这里数据量与搜索性能成反比。具体相关代码中还说到一个正向和反向搜索，主要是相对Node 和 RelatedNode来说，它们两者互为参照对象，进行向外搜索使用。
下面是存储过程的执行：
复制具体相关代码 具体相关代码如下:
