电玩城打鱼捕鱼-专业24小时上下分

资源等待之,SQLOS任务调度算法

八月 8th, 2019  |  电玩城上下分数据库

 图片 1

     Mixed System (OLTP &
OLAP):
这样环境会是一个挑战,必须找到正确的平衡点。我采取了非常简单的方法。

图片 2

    图片 3

 
                               同时,不管服务器有多少颗CPU,查询只会选择两颗CPU来执行。 
             

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。

 1.2  模拟CXPACKET的并行处理 

 
    有一个组织/协调(organizer/coordinator)线程(Thread 0),它需要等待所有线程完成并聚合数据来呈现给客户端。

图片 4

  由于并行的原因而从出现了Expacket
的等待。是否并行的执行,通过执行计划可以查看到,下面是查询大表中的数据,sql
server自动加启了并行执行。

  联机丛书:

【应用】

  并行处理的优势:
用多个线程来执行一个指令,当sql
server发现一条指令复杂时或语句中含有大数据量要处理,此时执行计划会决定用多个线程并行来执行,从而提高整体响应时间,例如一个指令读入100w条记录,
如果用一个线程做 可能需要10秒, 如果10个线程来做
可能只需要1秒,加上线程间同步时间也不过2秒。

 

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

 (4)  找出程序中感觉复杂的sql语句,查看执行计划。

  翻译整理:Joe.TJ

步骤六:查看TASK

 或采用sys.sysprocesses查看结果。下面一个举例中
会话session是SPID 56。 这里我们明显看到,SQL Server使用了5个线程kpid
来执行这个query。

EXEC sys.sp_configure N'cost threshold for parallelism', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

【跟踪】

一.概述

 减少CXPACKET等待:

结果如下:

  图片 5

 

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

  共调用了32个线程来并行查询

EXEC sys.sp_configure N'cost threshold for parallelism', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

通常来讲,SCHEDULER个数是跟CPU个数相匹配的。除了几个系统的SCHEDULER以外,每一个SCHEDULER都映射到一个CPU,如下面的查询结果所示,我们有四个CPU,也就有相应四个USER SCHEDULER,而scheduler_total_count有16个则是因为有8个是系统scheduler,我们一般不必关注系统scheduler。

最后考虑调整并行度的开销阈值或降低并行度。

 
   
组织线程必须等待所有线程完成处理才能进行下一步。由于组织线程等待缓慢的线程完成处理所产生的等待,就叫CXPACKET等待。

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

    设置可以发现并行度就二个线程。

  翻译整理仅用于传播资讯之目的。

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

  图片 6图片 7

 
                               在这里我将会设置“‘Cost Threshold for Parallelism’”为25(如图)。你可以选择任何值。但你只能通过在系统上做实验来找到合适的值。

步骤八:查看SCHEDULER

 (6)  当执行计划发现并不需要用并行执行时,强制sql 使用OPTION ( MAXDOP x)
也不会采用并行执行。

 
最近我的两个库出现,出现较多的CXPACKET等待,在网上找了一下资料。其中有篇一个SQL
Server专栏作家的文章不错,也解决了我的一些疑问,就翻译在这里。

TASK是worker的使用者,每个TASK系统会给它分配一个工作线程进行处理,是一对一的关系但并不绑定。如果所有的工作线程都在忙,而且已经达到了最大工作线程数,SQL
Server就要等待,直到有一个忙的工作线程被释放。

 (5)  避免或减少白天执行频繁复杂sql,优化sql 建好索引。

 
                                         
这样大多数查询将会利用并行处理,执行时间较长的查询也会受益于多处理器而提高性能。 
    

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

     下面是一个分组查询,在执行计划中看到,以采用了并行处理

 
    因为某些原因,一个或多个线程滞后,而产生了CXPACKET等待状态。

(33 行受影响)
task_address       task_state  context_switches_count pending_io_count pending_io_byte_count pending_io_byte_average scheduler_id session_id exec_context_id request_id  worker_address     host_address       parent_task_address
------------------ ---------------------------------- ---------------- --------------------- ----------------------- ------------ ---------- --------------- ----------- ------------------ ------------------ -------------------
0x000000000DB29468 SUSPENDED   4696                   510              0                     0                       0            63         7               0           0x0000000032E02160 0x0000000000000000 0x0000000025E67468
0x000000000DB29088 SUSPENDED   1457                   290              0                     0                       0            63         11              0           0x0000000017FE2160 0x0000000000000000 0x0000000025E67468
0x0000000012358CA8 RUNNING     1937                   1945             0                     0                       0            63         21              0           0x0000000034E84160 0x0000000000000000 0x0000000025E67468
0x0000000012359088 SUSPENDED   2                      0                0                     0                       0            63         32              0           0x000000000685A160 0x0000000000000000 0x0000000025E67468
0x000000000F20D468 SUSPENDED   4489                   510              0                     0                       1            63         4               0           0x000000001FE30160 0x0000000000000000 0x0000000025E67468
0x0000000035F19468 SUSPENDED   1731                   290              0                     0                       1            63         16              0           0x00000002BD8DC160 0x0000000000000000 0x0000000025E67468
0x0000000035F19088 SUSPENDED   2280                   1864             0                     0                       1            63         23              0           0x000000001AA60160 0x0000000000000000 0x0000000025E67468
0x0000000035F18CA8 SUSPENDED   9                      0                0                     0                       1            63         28              0           0x00000002BB60A160 0x0000000000000000 0x0000000025E67468
0x000000002E283468 SUSPENDED   4485                   510              0                     0                       2            63         5               0           0x000000001FE48160 0x0000000000000000 0x0000000025E67468
0x000000001A736108 SUSPENDED   1700                   290              0                     0                       2            63         15              0           0x00000000310C6160 0x0000000000000000 0x0000000025E67468
0x000000001A737468 RUNNING     2256                   1865             0                     0                       2            63         20              0           0x00000000049DC160 0x0000000000000000 0x0000000025E67468
0x000000001A737848 SUSPENDED   5                      0                0                     0                       2            63         30              0           0x0000000018390160 0x0000000000000000 0x0000000025E67468
0x000000001A609088 SUSPENDED   3973                   510              0                     0                       3            63         8               0           0x000000001BEC0160 0x0000000000000000 0x0000000025E67468
0x0000000014A49848 SUSPENDED   1652                   290              0                     0                       3            63         14              0           0x0000000017436160 0x0000000000000000 0x0000000025E67468
0x0000000014A49088 RUNNING     2058                   1878             0                     0                       3            63         18              0           0x0000000025D2C160 0x0000000000000000 0x0000000025E67468
0x000000000FD5C108 SUSPENDED   6                      0                0                     0                       3            63         26              0           0x00000000213DA160 0x0000000000000000 0x0000000025E67468
0x0000000025E67468 SUSPENDED   3                      0                0                     0                       4            63         0               0           0x00000000353A6160 0x0000000000000000 NULL
0x0000000006EC9C28 SUSPENDED   4469                   510              0                     0                       4            63         6               0           0x000000002AF14160 0x0000000000000000 0x0000000025E67468
0x000000001C0708C8 SUSPENDED   1725                   290              0                     0                       4            63         13              0           0x000000002AC74160 0x0000000000000000 0x0000000025E67468
0x000000001C0704E8 RUNNING     2324                   1889             0                     0                       4            63         24              0           0x000000001497A160 0x0000000000000000 0x0000000025E67468
0x0000000012035468 SUSPENDED   5                      0                0                     0                       4            63         29              0           0x00000002B70E6160 0x0000000000000000 0x0000000025E67468
0x00000002BB1144E8 SUSPENDED   4084                   511              0                     0                       5            63         1               0           0x0000000028F4E160 0x0000000000000000 0x0000000025E67468
0x00000002BB115C28 SUSPENDED   1775                   290              0                     0                       5            63         12              0           0x000000000E7B4160 0x0000000000000000 0x0000000025E67468
0x00000002BB115468 RUNNABLE    2256                   1830             0                     0                       5            63         22              0           0x000000000AC4C160 0x0000000000000000 0x0000000025E67468
0x000000000BBA5848 SUSPENDED   5                      0                0                     0                       5            63         27              0           0x000000002ABFC160 0x0000000000000000 0x0000000025E67468
0x00000000263BFC28 SUSPENDED   5031                   510              0                     0                       6            63         2               0           0x000000002E444160 0x0000000000000000 0x0000000025E67468
0x00000002BE5D6108 SUSPENDED   1856                   290              0                     0                       6            63         10              0           0x00000002BF20E160 0x0000000000000000 0x0000000025E67468
0x0000000020446CA8 RUNNING     2275                   1936             0                     0                       6            63         19              0           0x0000000005104160 0x0000000000000000 0x0000000025E67468
0x0000000020446108 SUSPENDED   5                      0                0                     0                       6            63         31              0           0x0000000022F9E160 0x0000000000000000 0x0000000025E67468
0x000000003193B468 SUSPENDED   4276                   510              0                     0                       7            63         3               0           0x000000002B58C160 0x0000000000000000 0x0000000025E67468
0x000000003193A8C8 SUSPENDED   1806                   290              0                     0                       7            63         9               0           0x000000001FCEA160 0x0000000000000000 0x0000000025E67468
0x000000000E2A2CA8 SUSPENDED   2308                   2007             0                     0                       7            63         17              0           0x00000000113AE160 0x0000000000000000 0x0000000025E67468
0x000000000E2A28C8 SUSPENDED   10                     0                0                     0                       7            63         25              0           0x000000002504C160 0x0000000000000000 0x0000000025E67468

 (3) 找出cpu和i/o耗性能最高的sql语句, 查看执行计划是否有并行处理。

  CXPACKET 已经成为所有等待类型中最常见的一种了。我通常会在多CPU系统的前五位等待类型统计中看见CXPACKET.

SQL SERVER SQLOS的任务调度

 (2) 查看并行的前十条语句
(这种查询不建议使用,因为条件是查找含有并行parallel的执行计划,查询响应很慢)。

 
                               在一个即有OLTP查询又有报表服务器的系统上,我发现这样做运行得很好。

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

1.5  CXPACKET资源等待总结

 
   
如果你在任何查询上禁止此种等待,那么查询也许会变慢,因为不能为它执行并行操作。

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

  下面是通过sys.dm_os_waiting_tasks 来查看该语句的task任务。

 
    请注意,并不是所有的CXPACKET等待类型都是不好的事情。你也许会遇某个CXPACKET等待是完全有意义的案例,有时它也是不可避免的。

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

   图片 8

 
 OLTP: 在纯OLTP系统上,它的事务较短,查询也不长,但是通常很快速。设置“Maximum degree of Parallelism”(MAXDOP)为1。

一个客户端connection可能包含一个或多个BATCH,一般SQL
Server引擎会为一个BATCH视为一个TASK,但使用并行化查询的BATCH会被分解成多个TASK。具体BATCH怎么分解成TASK,以及分解成多少个,则是由SQL
Server内部决定的。但是在这里我们依然可以使用相关DMV探寻一下大致分配情况:

图片 9

 
       
这样做可以确保查询永远不必使用并行方式运行,并且不会导致更多的数据库引擎开销。 
       

图片 10

   1.1 
 查询 CXPACKET的等待

 
  
当为SQL查询创建一个并行操作时,会有多个线程去执行这个查询。每个查询处理不同的数据集或行集。

步骤一:

SELECT TOP 10
        p.* ,
        q.* ,
        qs.* ,
        cp.plan_handle
FROM    sys.dm_exec_cached_plans cp
        CROSS APPLY sys.Dm_exec_query_plan(cp.plan_handle) p
        CROSS APPLY sys.Dm_exec_sql_text(cp.plan_handle) AS q
        JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
WHERE   cp.cacheobjtype = 'Compiled Plan'
        AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/SQL Server/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
OPTION  ( MAXDOP 1 )

图片 11

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

标签:, , , ,

Your Comments

近期评论

    功能


    网站地图xml地图