sql server 性能调优 资源等待之 CXPACKET

国际太阳娱乐网站2138 12

一.概述

 
最近我的两个库出现,出现较多的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.

国际太阳娱乐网站2138,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

  国际太阳娱乐网站2138 1

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

国际太阳娱乐网站2138 2

 1.2  模拟CXPACKET的并行处理 

 CXPACKET 解释:

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

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

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

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

 国际太阳娱乐网站2138 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

国际太阳娱乐网站2138 4

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

国际太阳娱乐网站2138 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探寻一下大致分配情况:

    国际太阳娱乐网站2138 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等待。

结果如下:

   国际太阳娱乐网站2138 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

  国际太阳娱乐网站2138 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

【关系】

  国际太阳娱乐网站2138 9国际太阳娱乐网站2138 10

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

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

1.4  控制CXPACKET并行度

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

国际太阳娱乐网站2138 11

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

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

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

  国际太阳娱乐网站2138 12

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

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

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图