第16章 从等待状态判断系统资源瓶颈 699
·
SQL Server可能会遇到各式各样的系统资源瓶颈，包括：内存，磁盘I/O，CPU，锁资源阻塞。如果遇到了资源瓶颈，会话任务就拿不到资源，而进入等待状态。以下方法可以了解所有任务的状态以及它们在等待的资源。
·
sys.sysprocesses - 需要说明的是sys.sysprocesses的结构是向前兼容的，它不支持多个活动结果集（MARS）这个概念。在SQL2000的时候，一个连接同时只能运行一个批处理指令。也就是说一个连接在一个时间只能有一个请求在运行。但是SQL2005以后，如果使用了MARS技术，一个连接可以同时开启多个结果集。而sys.sysprocesses视图不能正确显示这种行为。如果您的SQL已经升级到2005或以上，建议转用到下面这三个DMV
·
sys.dm_exec_requests - 返回有关在SQL中执行的每个请求的信息，包括当前的等待状态
·
sys.dm_exec_sessions - 对于SQL中每个经过身份验证的会话都返回相应的一行。它是服务器范围的视图，显示了有关所有活动用户连接和内部任务的信息。此信息包含客户端版本，客户端程序名称，客户端登录时间，登录用户和当前会话设置等。
·
sys.dm_exec_connections - 返回与SQL实例建立的连接有关的信息以及每个连接的详细信息
·
另外还有一个动态管理视图sys.dm_os_wait_stats，可以返回从SQL启动以来所有等待状态总的等待数和等待时间。从这些累积值，可以看出SQL经常会遇到哪一类等待。不同的等待状态有不同的含义，代表着不同的系统资源。SQL是一个很复杂的系统，在SQL2012里，有不下200种等待状态（SQL2005有202种）。但是幸运的是，经常会引起问题的等待不是太多。无须去研究每一个等待状态。
16.1.
lck_xx类型 701
·
如果SQL经常有阻塞发生，会经常看到如下这些类似以“LCK_”开头的等待状态。例如：
o
LCK_M_BU:正在等待获取大容量更新锁BU
o
LCK_M_IS:正在等待获取意向共享锁IS锁
o
LCK_M_IU：正在等待获取意向更新锁IU
o
LCK_M_IX:正在等待获取意向排它锁IX
o
LCK_M_RIn_NL:正在等待获取当前键值上的NULL锁以及当前键和上一个键之间的插入范围锁。键上的NULL锁是指立即释放的锁
o
LCK_M_S:正在等待获取共享锁
o
LCK_M_SCH_M:正在等待获取架构修改锁
o
LCK_M_SCH_S:正在等待获取架构共享锁
o
LCK_M_U:正在等待获取更新锁
o
LCK_M_X:正在等待获取排它锁
。。。
·
阻塞是SQL里最容易出现的等待状态
16.2.
pageiolatch_x与writelog 702 - PAGEIOLATCH_SH是内存问题造成，PAGEIOLATCH_EX 和WRITELOG是由磁盘速度跟不上造成的。
·
当缓存在内存缓冲区域里的数据页面，和磁盘上数据文件里的数据页面进行交互时，为了保证不会有多个用户同时读取/修改内存里的数据页面，SQL会像对待表格里的数据一样，对内存中的页面实行加锁机制，以同步多用户并发处理。不同的是，Latch同步的是SQL Server的内部对象，而Lock同步的是用户对象如表、行、索引等。
·
例如，当SQL将数据页面从数据文件里读入内存时，为了防止其它用户对内存里的同一个数据页面进行访问，SQL会在内存的数据页面上加一个排它的latch（至于为什么是读入时是加排它锁而不是共享锁，见例子中第4步的说明）。而当有任务要读缓存在内存里的页面时，会申请一个共享的latch。像LOCK一样，latch也会出现阻塞的现象。
·
根据不同的等待资源，在SQL里等待的状态会是：
o
PAGEIOLATCH_DT:destroy buffer page io latch
o
PAGEIOLATCH_EX:exclusive buffer page io latch
o
PAGEIOLATCH_KP:keep buffer page io latch
o
PAGEIOLATCH_NL:null buffer page io latch
o
PAGEIOLATCH_SH:shared buffer page io latch
o
PAGEIOLATCH_UP:update buffer page io latch、
·
PAGEIOLATCH_SH和PAGEIOLATCH_EX是PAGEIOLATCH_x类型等待中最常见的是两大类，
PAGEIOLATCH_SH经常发生在用户想要去访问一个数据页面而同时SQL却要把这个页面从磁盘读进内存。如果这个页面是用户经常有可能访问到的，那么说到底，问题是因为内存不够大，没能够将数据页面始终缓存在内存里。所以，往往是先有内存压力，触发SQL做了很多读取页面的工作，才引发磁盘读的瓶颈。这里磁盘瓶颈常常是内存瓶颈的副产品。而PAGEIOLATCH_EX常常发生在用户对数据页面做了修改，SQL要向磁盘回写的时候，基本意味着磁盘的写入速度明显跟不上。这里和内存瓶颈没有直接关系。
·
这里举一个最容易发生的等待：PAGEIOLATCH_SH 来做例子，看看这种等待是怎麽发生的
（1） 有一个用户请求，必须读取整张X表，由Worker x执行
（2） Worker x 在执行表扫描的过程中发现它要读取数据页面1:100
（3） SQL发现页面1:100并不在内存中的数据缓存里
（4）
SQL在缓冲池里找到一个页面的空间，在上面申请一个EX的latch，以防止数据从磁盘里读出来之前，有别人也来读取或修改这个页面
（5） Worker x发起一个异步IO请求，要求从数据文件里读出页面1:100。
（6） 由于是个异步IO，worker x可以接着做它下面要做的事情。而下面要做的就是要读出内存中的页面1:100。读取的动作需要申请一个SH的latch。
（7） 由于Worker x之前已经申请了一个EX latch还没释放，所以这个SH latch将被阻塞住。Worker x 被自己阻塞住了，等待的资源就是PAGEIOLATCH_SH。
（8） 当异步IO结束后，系统会通知worker x，你要的数据已经写入内存了。
（9） 这时候EX latch就被释放。接着Worker x得到了它申请的SH latch。
（10） 数据页1:100 终于被worker x读到，读取工作结束，worker x可以继续下面的操作了
由此可以看到，在发生PAGEIOLATCH类型的等待时，SQL是在等待某个IO动作的完成。所以如果一个SQL经常出现这一类的等待，说明磁盘的速度不能满足SQL的需要，它已经成为了SQL的一个瓶颈。
·
和磁盘有关的另一个等待状态是WRITELOG，说明任务当前正在等待将日志记录写入日志文件。出现这个等待状态也意味着磁盘的写入速度明显跟不上。所以，和磁盘的写入速度有关的有：PAGEIOLATCH_EX 和WRITELOG。
16.3.
pagelatch_x
704
·
pagelatch_x和pageiolatch_x是完全不一样的。PAGEIOLATCH_X出现在SQL要和磁盘进行交互的时候，所以中间有IO这两个字。而PAGELATCH与IO无关，它 是SQL Server为了解决同时修改页面而引发潜在冲突使用的一种管理先来后到的机制。当一个任务要修改页面时，它必须先申请一个EX的latch。只有得到这个latch，才能修改页面里的内容，而稍后一点的修改请求则必须要等上一个请求在这个页面上的latch释放后才可进行。
·
由于数据页的修改都是在内存中完成的，所以每次修改的时间都应该非常短，几乎可以忽略不计。而pagelatch只是在修改的过程中才会出现，所以pagelatch的生存周期应该也非常短。
·
如果这个资源成为了SQL经常等待的资源，可以说明以下问题：
o
SQL没有明显的内存和磁盘瓶颈
o
应用程序发来大量的并发语句在修改同一张表格里的记录，而表格架构设计以及用户业务逻辑使得这些修改都集中在同一个页面，或者数量不多的几个页面上。这些页面有时候也被称为 Hot Pages。这样的瓶颈通常只会发生在并发用户比较多的，典型的OLTP系统上。
o
这种瓶颈是无法通过提高硬件配置解决的，只有通过修改表格设计或者业务逻辑，让修改分散到尽可能多的页面上，才能提高并发性能。How？
ü 最简单的方法：是换一个数据列建聚集索引，而不要建立在IDENTITY字段上。这样表格里的数据就按照其它方式排序，同一时间的插入就有机会分散在不同的页面上。
ü 如果实在是一定要在IDENTITY的字段上建立聚集索引，建议根据其它某个数据列在表格上建立若干个分区（Partition）表分区，把一个表格分成若干个分区，可以使得接受新数据的页面数目增加。例如以股票交易系统为例子。不同的股票属于不同的行业。开发者可以根据股票的行业属性，将一张交易表分成若干个分区。在SQL里，已分区表（Partition
Table）的每个分区都是一个独立的存储单位。分属不同分区的数据行是严格分开存储的。所以同一个时间发生的交易记录，因其它行业不同，也会分别保存在不同的分区里。这样，在同一个时间点，可以插入不同行业的交易记录。每个分区上的HOT PAGE（接受新数据插入的PAGE）就不那么HOT了。
16.4.
tempdb上的pagelatch 708
·
SQL不仅在数据页修改的时候加latch，在数据文件的系统页面上，例如SGAM、PFS、GAM页面发生修改的时候，也会加latch。这些latch在某些情况下也有可能成为系统瓶颈。
·
当数据库创建一张新表的时候，SQL要为这张表分配存储页面，同时SQL也要修改SGAM、PFS、GAM页面，把已经分配出去的页面标志成已使用。所以每创建一张新表，SGAM、PFS、GAM页面都会有修改动作。这种行为对一般用户数据库不会有问题，因为正常的应用不会折腾着不停地建表，删表。
·
但是tempdb就不同了。如果一个存储过程使用了临时表，而这个存储过程被并发用户广泛使用，那很自然地就会有很多并发用户在tempdb里同时创建表，做完了以后又删除表。这时候在一个时间点，会有很多任务要修改SGAM、PFS、GAM页面。它们上面的latch就会成为系统瓶颈。所以这类问题，通常会发生在tempdb上。
·
数据页面上的“HOT” 可以通过调整表格设计来缓解，系统页面的瓶颈怎麽解决呢
o
（1）SQL使用几颗CPU在运行，就为TEMPDB创建几个数据文件
o
（2）这些文件的大小必须一样大！
o
（3）要严格防止tempdb数据空间用尽，引发数据文件自动增长。因为自动增长只会增长其中一个文件，造成只有一个文件有空闲空间，所有的任务就会集中在它的身上，它就又变成瓶颈了
·
当然，监视tempdb的使用情况。搞清楚是谁在tempdb里占用这么多空间也是很重要的。监视方法和脚本见：http://sqlserverandme.blogspot.com/2014/08/how-to-monitor-space-usage-in-temp-db.html
16.5.
其它资源等待 710
·
除了上面说的这些最常见的资源等待问题（LCK_XX, PAGEIOLATCH_X,WRITELOG, PAGELATCH, 和tempdb上的PAGELATCH），一个用户任务在某些情况下，还可能等待下面这些资源：
·
LATCH_X - 在SQL里，除了同步数据页面访问的latch（buffer latch），还有很多其它内部资源要同步。一般情况下，这些latch的申请和释放是非常快的，用户应该不会看到这种等待。但是在一些不正常的情况下，会看到任务在等待某个latch资源。常见的原因有：
o
某个先前的任务出现了访问越界（access violation）异常。SQL强行终止了这个任务，但是没能完全将它所申请的所有资源都释放干净，使得某个或者某些latch成为无主孤儿。后面任务要申请同样的latch，都会被阻塞住。这类问题判断起来比较容易，只要打开SQL日志文件（errorlog），看看出问题之前是否有过access
violation问题发生。但是从用户层面上，是没有办法命令SQL申请或释放某个latch资源的。一般只有通过重启SQL服务才能解决问题。
o
SQL同时发生了其它资源瓶颈，例如：内存，线程调度，磁盘等，而latch等待只不过是一个衍生的等待。
o
当某个数据库文件空间用尽，做自动增长的时候，同一个时间点只能有一个用户任务可以做文件自动增长动作，其它任务必须等待。这时候也会出现latch资源的等待。
o
在一些特殊情况下，有可能是SQL自己没有处理好并发同步，没有使用比较优化的算法，使得用户任务比较容易遇到等待。SQL的一些补丁，就曾经修复过这类问题。
所以当看到SQL里的任务出现大量latch等待时，往往是由其它问题衍生而来。首先需要检查SQL是否在健康运行，是否先前已经有过任何异常发生，是否有其它资源瓶颈。只有保证SQL在健康运行，才能谈论这种latch等待。将SQL升级到最新版本，也是一个推荐的做法。
·
ASYNC_NETWORK_IO (NETWORK_IO) - 当SQL要返回数据结果集给客户端的时候，会先将结果集填充到输出缓存中（output cache）。与此同时，网络层会开始将输出缓存里的数据打包，由客户端接收。如果SQL返回结果集的速度比客户端接收结果集的速度要快，会出现SQL已经把后面的结果集准备好，但是输出缓存里前面的数据还没有传完，SQL没有地方放新数据结果的情况。这时候，任务就会进入ASYNC_NETWORK_IO这个等待状态（SQL2000的时候叫NETWORK_IO）。这个等待状态是比较容易出现的。要强调以下几点：
o
（1）这个等待状态并不意味着SQL自身性能有问题，而是说明，客户端没有及时把数据取走。调整SQL这边的配置是不会有什么大的帮助的。
o
（2）网络层的瓶颈当然是问题的一个可能原因 - 如果网络层的确出现瓶颈，要首先考虑的问题应该是：为什麽SQL需要向客户端传输这麽大量的结果集？对大部分用户来讲，一个成千上万行的结果集对它们没有任何意义，没有人会把这么大的结果集从头到尾看一遍，最多也就只看前面的几十行。所以程序设计是否合理？有没有设计合理的逻辑，将客户真正需要的数据集返回？
o
（3）应用程序端的性能问题，也会导致SQL里的ASYNC_NETWORK_IO等待 - SQL网络层将结果集打包发给客户端以后，要等到客户端确认收到，才会接着发下一个包如果客户端确认得很慢，SQL也不得不慢慢地发。这其实是ASYNC_NETWORK_IO等待最常见的原因。那么客户端为什麽会确认得很慢呢？常见原因有：
ü 客户端应用有意只取开头的一段数据，而不把数据全部取完，
ü 或者自己100%CPU了，
ü 或者自己hang住了，
ü 或者自己遇到了内存或者磁盘瓶颈。
总之，遇到ASYNC_NETWORK_IO等待，要检查应用程序的健康状况，也要检查应用是否有必要向SQL申请这么大的结果集返回
·
和内存相关的等待状态 - 当用户任务申请内存暂时申请不到时，会出现一些特殊的等待状态，常见的有：CMEMTHREAD，SOS_RESERVEDMEMBLOCKLIST， RESOURCE_SEMAPHORE_QUERY_COMPILE。如果在DMV里看到这些等待状态，就要开始确认SQL是否存在内存瓶颈。
·
SQLTRACE_X - 对一个繁忙的SQL，开启SQL TRACE，尤其是直接开SQL SREVRE PROFILER很可能对性能产生负面影响。所有的操作都要向跟踪报告，导致跟踪自己成为了一个瓶颈。在SQL2000之前，如果出现了跟踪瓶颈，会看到很多latch等待。SQL2005以后，特别加入了以SQLTRACE_X开头的一组等待状态。当看到SQL经常有这样的等待时，除非迫不得已，要立刻停止收集SQL TRACE，尤其是用SQL SERVER
PROFILER收集跟踪这样的行为。
16.6.
最后一道瓶颈：许多任务处于runnable状态 713
·
如果您的SQL没有遇到以上的任何一个等待状态，那么恭喜您，你的SQL既没有遇到阻塞，也没有遇到内存，磁盘瓶颈，数据结果集也能够及时被客户端取走，各个用户任务跑得很欢。您的SQL系统要不就是负载比较轻，比较空闲，要不就是优化得比较好。但即便如此，你的SQL还可能会遇到最后一道瓶颈：有很多任务处于runnable状态 - 可以运行，但是没有在运行。这样的状态，也会严重影响SQL的性能。
·
运行SELECT [status] FROM sys.[dm_exec_requests] 或者SELECT [status] FROM sys.[sysprocesses]。正常的SQL，哪怕非常繁忙，也不应该经常看见runnable的任务，连running状态的任务，都不应该很多。
·
如果SQL没有遇到线程调度问题，没有报出：17883 / 17884之类的警告，出现非常多的runnable任务通常有两种可能原因：
o
（1）SQL CPU使用率已经接近100%，真的是没有足够CPU资源来及时处理用户的并发任务。通过性能监视器可以确认SQL的CPU使用率。解决的方法是找出最耗CPU资源语句或者应用，将其优化，或者减少负载。当然，在服务器上多加几个CPU也是快速解决问题的好选择。
o
（2）SQL CPU使用率并不是很高，小于50%。那为什麽还有CPU资源, 但是任务就是不能运行呢? 这是因为SQL除了lock和latch以外，还有一种更轻量级的同步资源：spin lock（自旋锁）。像latch一样，spin是一种很轻量级的资源，正常情况下不应该成为SQL的瓶颈。但在SQL2005上有一个比较著名的问题，就发生在自旋锁上，而且经常发生在64位的SQL上。当SQL的内存比较宽裕且安全上下文缓存得太多的时候，并发用户会容易遇到一种叫MUTEX互斥锁的自旋锁上的瓶颈。定期运行DBCC
freesystemcache(tokenandpermuserstore)可以有效地防止SQL遇到这个问题。另外一种选择是以/T 4618 和/T 4610 这两个跟踪标志启动SQL, 让SQL使用另外一种缓存管理机制, 也能够有效避免问题发生。SQL2008对安全上下文的管理有了改进, 这种自旋锁将不容易遇到，其它的自旋锁到现在为止, 还没有报告过显著问题。
16.7.
小结 - 下面来总结一下一个用户请求在其生命周期中,大致会经过哪些阶段,以及在各个阶段可能需要等待的资源
·
1、客户端向SQL发出请求指令，指令经过网络层，SQL接收到。在这一步，如果指令比较长，或者比较多，客户端发指令的快慢会影响到SQL接收的速度，网络传输速度也有影响。这也是为什麽把几百个小指令合并成几个大的批处理可能会对整体性能有帮助的原因，也能解释为什麽把应用程序和SQL安装在同一台机器上，有些时候（注意：不是总是）会对性能有所帮助。如果应用程序自己出现性能问题，在这一步也会对性能产生影响。
·
2、SQL对收到的指令进行语法、语义检查、编译、生成新执行计划，或者找到缓存的执行计划重用。这一步需要用到的资源种类比较多:
o
CPU资源：做指令语法，语义检查，编译，生成新执行计划，都是要做计算的。所以这一步是消耗CPU资源比较多的地方，尤其是当指令本身比较复杂，牵涉的表数量很多，表结构复杂，上面有很多索引时，SQL可能需要计算很多候选方案，才能决定一个比较合适的执行计划。如果这一步遇到瓶颈，会出现CPU使用率高，SQLOS
SCHEDULER繁忙的现象。
o
内存：一般短小的语句，这一步需要使用的内存不会很多。但是如果语句很长，那么这一步要花的内存有可能也会很大。而这部分内存，主要是stolen内存，在32位的SQL里是一块比较小的区域。如果这一步内存紧张，有可能会出现下面这些等待状态：cmemthread，sos_reservedmemblocklist，resource_semaphore_query_compile。当然也可能直接报告 701 错误。
o
表格上的架构锁（schema lock）。当一条指令在编译的时候，它要防止别人对它要访问的对象（表，视图，存储过程等）进行架构上的修改。所以在这一步会有一些架构锁产生。如果同时有大量的并发用户在做一样的编译动作，或者有一个人在对象上申请了级别很高的锁，可能在指令编译的时候就遇到阻塞。
o
在SQL确认是否有现成的执行计划可用时，要在内存中进行搜索。这时候可能会有一些自旋锁。
·
3、运行指令 - 在得到执行计划以后，就进入运行阶段。在这个阶段，用到的资源是最多的。一般来讲在这一步花的时间也是最长的。运行一条指令，SQL要做很多事情：
o
（1）SQL首先为指令的运行申请内存 - 如果同时有很多复杂的指令在执行，可能新的指令在申请内存上会遇到困难。这时候会看到一些以resource_semaphore_开头的等待状态。
o
（2）如果发现要访问的数据不在内存里，要将数据从磁盘读到内存中，如果发现内存里没有足够的空闲页面存放所有数据，还要做一些内存整理和paging动作（换页动作）腾出足够的空间放数据。这一步是挺花时间的。如果要访问的数据事先都缓存在内存里，这一步就不需要做了。这就是为什麽有些语句第一次运行会比第二次运行慢很多的原因，也能解释为什麽一个内存资源不足的SQL性能有明显下降。在这一步的等待状态，通常是PAGEIOLATCH_X 。这时候也会观察到其它表明内存和磁盘繁忙的现象。
o
（3）按照执行计划，扫描或者seek内存中的数据页面，将指令需要处理的记录找出来。在这一步，SQL要申请各式各样的锁，以实现事务隔离。所以这个动作很容易遇到阻塞问题。等待状态一般是以LCK_X开头的那些。
o
（4）指令可能还要做一些联接或者计算的工作（sum ，max， sort等） - 这些动作要使用的资源主要是CPU。如果这方面比较复杂，或者选取的执行计划不够优化，导致计算量庞大，而指令没有遇到其它资源等待，那么会看到SQL CPU使用率比较高的现象。
o
（5）根据指令内存，执行计划和数据量，SQL可能还要在tempdb里创建一些对象，存放临时表，表变量，帮助做join，sort等。这时候，tempdb可能会成为瓶颈，包括它的空间，系统表，系统页面等。我们会看到tempdb上的资源的等待。
o
（6）如果指令要修改数据记录，SQL会修改内存缓冲区里页面的内容 - 由于修改的对象是内存里的页面，在这一步不会直接触发磁盘写入，所以应该不会直接导致磁盘瓶颈。但是如果有并发用户在修改同一个页面的时候，会出现PAGELATCH_X的等待状态。
o
（7）如果指令发生数据修改，在提交事务之前，SQL必须将相应的日志记录按照顺序写入事务日志文件。这一步会发生对事务日志文件所在磁盘的物理写入。如果磁盘写入速度不够快，或者瞬间需要写入的日志量太大，会出现WRITELOG的等待状态。
o
（8）将结果集返回给客户端 - SQL会把结果集放入输出缓存，等客户端来把结果集完全取走。如果结果集太大，或者一次批处理是由许多小的命令组成，导致网络交互太多，或者是应用程序自己有性能问题取数据取得很慢，都会导致指令整体运行时间延长。而这种等待也不是SQL能够控制解决的。如果这里有等待，等待状态会是：ASYNC_NETWORK_IO（在SQL2000里是NETWORK_IO）。
ü 当然，以上这些动作都要在SQLOS里首先拿到一个Worker（也就是thread），然后这个Worker还要能排上Scheduler，在CPU上运行。
ü 如果SQL Server没有空闲的worker，而且总的worker数目也达到了最大值，那么任务就要等待一个空闲的worker出现。这时候可以看到任务的等待状态是Ox46（UMSTHREAD）。而SELECT [work_queue_count] FROM sys.[dm_os_schedulers] --可用工作队列数的值会不等于0
ü 如果任务成功拿到一个worker，但是scheduler现在正在运行其它worker，任务进入等待队列这时候任务的状态是runnable。SELECT [runnable_tasks_count] FROM sys.[dm_os_schedulers]的值应该大于1
ü 如果任务拿到了scheduler，进入运行状态（running）。如果任务非常消耗CPU资源，可能会在CPU上跑一会。这时候会看到某颗CPU使用率高的现象。
总之，从任务当前的等待状态，可以大概知道它当前运行到哪一步，也可以分析SQL可能存在的资源瓶颈，从而提高SQL的并发性。在做性能问题定位的时候，先看一下出问题时候SELECT * FROM sys.[dm_exec_requests] 各个连接的状态，对找到问题正确方向会很有帮助。
