财富等待之,SQLOS职责调节算法

数据库 21
数据库

一.概述

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

前些天在处理一个SQL
Server
LATCH导致的数据库停止响应问题时,遇到了一些需要SQLOS调度知识解决的问题,正好以前看过一篇官网的文章,在这里稍作修改贴出来。

   CXPACKET是指:线程正在等待彼此完成并行处理。什么意思呢? 当sql
server发现一条指令复杂时,会决定用多个线程并行来执行,由于某些并行线程已完成工作,在等待其它并行线程来同步,这种等待就叫CXPACKET。

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

原文网址如下:

  为什么会有并行线程呢?  因为在sql server
里有个任务调度SCHEDULER是跟操作系统CPU个数 默认是一 一匹配的, 
我们也可能通过sp_configure来设置最大并行度,也就是Max Degree of Parallelism
(MAXDOP)。 关于调度可参考” sql server
任务调度与CPU”

  原文出处:

SQL SERVER SQLOS的任务调度

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

  翻译整理:Joe.TJ

【介绍】

  并行处理的劣势:1是并行线程要等待同步。2是由于这10个线程全力以赴,就有10个对应的cpu,这样别的用户发过来的指令就会受到影响,甚至拿不到cpu来执行。所以对于并发度要求高的需要及时响应的,一般会建议手动设置每个指令的并行线程数。反之可以不设置Max
Degree of Parallelism由系统默认去并行或者设少一点并行度。

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

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

   1.1 
 查询 CXPACKET的等待

 

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

  借助上一次性能调优的资源等待统计图,会发现等待时间最长的就是CXPACKET类型。

  联机丛书:

select cpu_count,scheduler_count,scheduler_total_count from sys.dm_os_sys_info

  数据库 1

 
  
当尝试同步查询处理器交换迭代器时出现。如果针对该等待类型的争用成为问题时,可以考虑降低并行度。

数据库 2

 1.2  模拟CXPACKET的并行处理 

 CXPACKET 解释:

WORKER(又称为WORKER
THREAD), 则是工作线程。在一台服务器上,我们可以有多个工作线程。因为每一个工作线程要耗费资源,所以,SQL
Server有一个最大工作线程数。

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

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

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

 数据库 3

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

最大工作线程数可以通过下面的查询得到。SQL
SERVER并不是一开始就把这些所有的工作线程都创建,而是依据需要而创建。

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

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

select cpu_count,max_workers_count from sys.dm_os_sys_info

数据库 4

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

数据库 5

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

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

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

    数据库 6

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

我们使用spid为63的窗口执行一个复杂的查询,此查询使用默认并行度运行(由于有8个CPU因此默认MAXDOP=8)。

 1.3  分析CXPACKET的并行处理

 减少CXPACKET等待:

select * from sys.dm_os_tasks where session_id=63 order by 7

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

 
  
我们不能抛开服务器负载类型来讨论减少CXPACKET等待。

结果如下:

   数据库 7

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

(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

  数据库 8

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

从上图我们可以看到,来自客户端的一个BACTH由于并行查询而被分解成了33个TASK,对应33个task_address,和33个worker_address,这说明一个BATCH占用了33个worker
threads,这个数目是相当大的。由于本例中USER
SCHEDULER的数目是8,因此默认MAXDOP也是8,所以我们看到有编号为0-7的8个scheduler_id,其中scheduler_id为4的CPU被5个task占用,这5个task当中有一个parent_task_address为NULL,说明这个task是整个BATCH的主task。其他7个CPU上都只有4个task。如果观察时间更长一点我们还会发现,同一个CPU上的4个task只有exec_context_id倒数第二大的task是一直处于running状态的,其他的全部是处于占用worker
thread的suspended状态。

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

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

【关系】

  数据库 9数据库 10

 
 Data-warehousing / Reporting server: 因为查询执行时间一般较长,建议设置“Maximum degree of Parallelism”(MAXDOP)为0。

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

1.4  控制CXPACKET并行度

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

数据库 11

   有时后台执行的sql, 对于并发度要求不高, 
不需要及时响应的,一般会建议手动设置每个指令的并行线程数。

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

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

  数据库 12

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

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

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

 
                               我设置“Maximum degree of Parallelism”(MAXDOP)为2,这样意味着查询仍会使用并行操作但是仅利用2颗CPU。

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

    数据库 13

 
                             
 然而,我把“并行查询阀值”设置为较高的值,这样的话,不是所有的查询都有资格使用并行,除了那些查询成本较高的查询。

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

1.5  CXPACKET资源等待总结

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

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

 (1)
通过实例级别查出CXPACKET的等待时间包括总等时间,平均等待时间,最大等待时间。

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

【跟踪】

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

 
                               在下面的脚本中,我设置“Max Degree of Parallelism”为2,这样的话,那些具有较高成本的查询(这里是25),将会在2颗CPU上执行并行查询。

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

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 )

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

步骤一:

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

EXEC sys.sp_configure N'cost threshold for parallelism', N'25'
GO
EXEC sys.sp_configure N'max degree of parallelism', N'2'
GO
RECONFIGURE WITH OVERRIDE
GO

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

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

数据库 14

CREATE DATABASE TEST
go
use TEST
go
CREATE TABLE TEST(ID int,name nvarchar(50))
INSERT INTO TEST VALUES (1, 'aaa')

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

 

步骤二:

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

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

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

begin tran
update TEST set name='bbb' where [ID] = 1

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

步骤三:

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

SELECT * FROM TEST

步骤四:查看连接

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

数据库 15

步骤五:查看batch

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

数据库 16

步骤六:查看TASK

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

数据库 17

步骤七:查看WORKER

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

数据库 18

步骤八:查看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状态。

数据库 19

【应用】

我们了解了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。所以已经到了极限了。

数据库 20

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

数据库 21

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

【总结】 

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图