笔记64-徐 其他资源等待

笔记64-徐  其他资源等待

--其他资源等待

--除了上面说的这些最常见的资源等待问题，一个用户任务在某些情况下，还可能
--等待下面这些资源

--1、LATCH_X
--在SQL里，除了同步数据页面访问的latch（buffer latch），还有很多其他内部
--资源要同步。在这些资源上也会出现latch。如果SQL处理得正常，这些latch的申请
--和释放是非常快的，用户应该不会看到这种等待。但是在一些不正常的情况下，会
--看到任务在等待某个latch资源。常见的原因有：

--（1）某个先前的任务出现了访问越界（access violation）异常。SQL强行终止了
--这个任务，但是没能完全将他所申请的所有资源都释放干净，使得某个或者某些latch
--成为无主孤儿。后面任务要申请同样的latch，都会被阻塞住

--这类问题判断起来比较容易，只要打开SQL日志文件（errorlog），看看出问题之前
--是否有过access violation问题发生。但是从用户层面上，是没有办法命令SQL申请
--或释放某个latch资源的。一般只有通过重启SQL服务才能解决问题


--（2）SQL同时发生了其他资源瓶颈，例如：内存，线程调度，磁盘等，而latch等待
--只不过是一个衍生的等待

--（3）当某个数据库文件空间用尽，做自动增长的时候，同一个时间点只能有一个用户
--任务可以做文件自动增长动作，其他任务必须等待。这时候也会出现latch资源的等待


--（4）在一些特殊情况下，有可能是SQL自己没有处理好并发同步，没有使用比较优化
--的算法，使得用户任务比较容易遇到等待。SQL的一些补丁，就曾经修复过这类问题


--所以当看到SQL里的任务出现大量latch等待时，往往是由其他问题衍生而来。
--首先需要检查SQL是否在健康运行，是否先前已经有过任何异常发生，是否有其他
--资源瓶颈。只有保证SQL在健康运行，才能谈论这种latch等待。将SQL升级到
--最新版本，也是一个推荐的做法




--2、ASYNC_NETWORK_IO(NETWORK_IO)
--当SQL要返回数据结果集给客户端的时候，会先将结果集填充到输出缓存中（output cache）
--与此同时，网络层会开始将输出缓存里的数据打包，由客户端接收。如果SQL返回结果集
--的速度比客户端接收结果集的速度要快，会出现SQL已经把后面的结果集准备好，但是
--输出缓存里前面的数据还没有传完，SQL没有地方放新数据结果的情况。这时候，任务
--就会进入ASYNC_NETWORK_IO这个等待状态（SQL2000的时候叫NETWORK_IO）

--这个等待状态是比较容易出现的。要强调以下几点：
--（1）这个等待状态并不意味着SQL自身性能有问题，而是说明，客户端没有及时把数据
--取走。调整SQL这边的配置是不会有什么大的帮助的


--（2）网络层的瓶颈当然是问题的一个可能原因
--如果网络层的确出现瓶颈，要首先考虑的问题应该是：为什麽SQL需要向客户端传输这麽大量
--的结果集？对大部分用户来讲，一个成千上万行的结果集对它们没有任何意义，没有人
--会把这么大的结果集从头到尾看一遍，最多也就只看前面的几十行。所以程序设计是否
--合理？有没有设计合理的逻辑，将客户真正需要的数据集返回？


--很多程序员没有意识到的是：返回大结果集对应用程序的影响往往会大于对SQL的影响。
--SQL是一个为处理大数据操作专门做过优化的服务，返回几万行，甚至几十万行的结果集
--对SQL来讲，不是一件很吃力的事情。但是对应用程序来讲就不是这麽回事。应用程序
--一般不会在这方面做特殊优化。想象一个WEB应用程序，如果同时有十几个，或者几十个
--用户都在从SQL上要求返回几十万行的结果集，而在应用层需要将这些结果缓存在内存里
--处理，最后展示给用户，那应用服务自己的性能和健康就很难保证了

--（3）应用程序端的性能问题，也会导致SQL里的ASYNC_NETWORK_IO等待

--SQL网络层将结果集打包发给客户端以后，要等到客户端确认收到，才会接着发下一个包
--如果客户端确认得很慢，SQL也不得不慢慢地发。这其实是ASYNC_NETWORK_IO等待最常见
--的原因

--那么客户端为什麽会确认得很慢呢？常见原因有：客户端应用有意只取开头的一段数据
--而不把数据全部取完，或者自己100%CPU了，或者自己hang住了，或者自己遇到了内存
--或者磁盘瓶颈，运行得很慢

--总之，遇到ASYNC_NETWORK_IO等待，要检查应用程序的健康状况，也要检查应用是否
--有必要向SQL申请这么大的结果集返回


--3、和内存相关的等待状态
--前面讲到，当用户任务申请内存暂时申请不到时，会出现一些特殊的等待状态，常见的有
--CMEMTHREAD
--SOS_RESERVEDMEMBLOCKLIST
--RESOURCE_SEMAPHORE_QUERY_COMPILE

--如果在DMV里看到这些等待状态，就要开始确认SQL是否存在内存瓶颈，详见第六章


--4、SQLTRACE_X
--笔者已经反复提到过，对一个繁忙的SQL，开启SQL TRACE，尤其是直接开SQL SREVRE PROFILER
--很可能对性能产生负面影响。所有的操作都要向跟踪报告，导致跟踪自己成为了一个瓶颈

--在SQL2000之前，如果出现了跟踪瓶颈，会看到很多latch等待。SQL2005以后，特别加入了
--以SQLTRACE_X开头的一组等待状态。当看到SQL经常有这样的等待时，除非迫不得已，
--要立刻停止收集SQL TRACE，尤其是用SQL SERVER PROFILER收集跟踪这样的行为。
--关于如何收集SQLTRACE，详见第14章



-----------------------------最后一道瓶颈：许多任务处于runnable状态-----------------------

--如果您的SQL没有遇到以上的任何一个等待状态，那么恭喜您，你的SQL既没有遇到阻塞，也没有
--遇到内存，磁盘瓶颈，数据结果集也能够及时被客户端取走，各个用户任务跑得很欢。您的SQL
--系统要不就是负载比较轻，比较空闲，要不就是优化得比较好。对于一台比较繁忙的SQL，
--完全没有等待状态是很少见的

--但是你不能高兴的太早，这样的SQL还可能会遇到最后一道瓶颈：有很多任务处于runnable状态
--可以运行，但是没有在运行。这样的状态，也会严重影响SQL的性能


SELECT [status] FROM sys.[dm_exec_requests]
SELECT [status] FROM sys.[sysprocesses]
--的status这一列，反映了当前所有任务的状态。如果你看到好几个任务的状态都是runnable，那
--就要严肃对待了。正常的SQL，哪怕非常繁忙，也不应该经常看见runnable的任务，连
--running状态的任务，都不应该很多


--如果SQL没有遇到线程调度问题，没有报出：17883 / 17884之类的警告，出现非常多的runnable
--任务通常有两种可能原因：

--（1）SQL CPU使用率已经接近100%，真的是没有足够CPU资源来及时处理用户的并发任务
--如果通过性能监视器可以确认，SQL的CPU使用率非常高，超过80%，甚至90%，甚至
--接近100%，那么这些runnable的任务是因为用户发过来的请求太多，CPU来不及做完
--在SQL Schedule上积压了下来，这时候只能说，SQL已经超负荷运行了。解决的方法
--是找出最耗CPU资源语句或者应用，将其优化，或者减少负载。当然，在服务器上多加
--几个CPU也是快速解决问题的好选择。



--（2）SQL CPU使用率并不是很高，小于50%
--这是一种令人很头痛的现象，SQL的CPU使用率不高，常常是在30%~50%之间。SQL
--也在运行，每秒钟甚至能够处理几百个批处理请求，但是用户反映，SQL没有正常
--的时候那么快。这时候检查
SELECT task_state FROM [sys].[dm_os_tasks]
SELECT * FROM [sys].[dm_os_tasks]
--state列，常常会发现很多任务的状态都是runnable

--为什麽还有CPU资源,但是任务就是不能运行呢?这是因为SQL除了lock和latch以外
--还有一种更轻量级的同步资源：spin lock（自旋锁） 在SQL里，有些很快就能得到
--也很快会被释放，一般来讲不大会发生长时间等待的同步资源，SQL选择让线程在CPU
--上稍微等一会（所谓自旋），而不是将CPU资源让出来。由于资源很快能够得到，这样
--处理能够减少CPU上线程的切换，更有效率


--像latch一样，spin是一种很轻量级的资源，正常情况下不应该成为SQL的瓶颈。
--不过在SQL内部，自旋锁的种类还是挺多的。你可以运行下面语句，得到SQL
--在所有自旋锁上等待的次数
DBCC SQLPERF(spinlockstats)

--在SQL2005上有一个比较著名的问题，就发生在自旋锁上，而且经常发生在64位
--的SQL上。当SQL的内存比较宽裕时，他会缓存很多执行计划，同时也缓存了很多
--的执行计划安全上下文。memory clerk里，用TokenAndPermUserStore表示。
--当这段内存比较大时，并发用户会容易遇到一种叫MUTEX互斥锁的自旋锁上的瓶颈
--下面的知识库文章对这个问题有所描述
--http://support.microsoft.com/kb/927396

--这个问题只在安全上下文缓存得太多的时候才容易发生，所以定期运行下面语句
--可以有效地防止SQL遇到这个问题，而且对系统的整体性能，也没有什么坏影响

DBCC freesystemcache(tokenandpermuserstore)

--另外一种选择是以/T 4618 和/T 4610 这两个跟踪标志启动SQL,让SQL使用另外
--一种缓存管理机制,也能够有效避免问题发生


--据说,SQL2008对安全上下文的管理有了改进,这种自旋锁将不容易遇到
--其他的自旋锁到现在为止,还没有报告过显著问题


----------------------------小结---------------------------------------
--讲完了所有可能发生的等待状态,下面来总结一下一个用户请求在其生命周期
--中,大致会经过哪些阶段,以及在各个阶段可能需要等待的资源

--1、客户端向SQL发出请求指令，指令经过网络层，SQL接收到
--在这一步，如果指令比较长，或者比较多，客户端发指令的快慢会影响到SQL接收的速度
--网络传输速度也有影响。这也是为什麽把几百个小指令合并成几个大的批处理可能会对
--整体性能有帮助的原因，也能解释为什麽把应用程序和SQL安装在同一台机器上，有些
--时候（注意：不是总是）会对性能有所帮助。如果应用程序自己出现性能问题，在这一步
--也会对性能产生影响



--2、SQL对收到的指令进行语法、语义检查、编译、生成新执行计划，或者找到缓存的执行计划
--重用

--这一步需要用到的资源种类比较多
--CPU资源：做指令语法，语义检查，编译，生成新执行计划，都是要做计算的。所以这一步是
--消耗CPU资源比较多的地方，尤其是当指令本身比较复杂，牵涉的表数量很多，表结构复杂
--上面有很多索引时，SQL可能需要计算很多候选方案，才能决定一个比较合适的执行计划


--如果这一步遇到瓶颈，会出现CPU使用率高，SQLOS SCHEDULER繁忙的现象

--内存：一般短小的语句，这一步需要使用的内存不会很多。但是如果语句很长，例如
--里面有个非常非常长的in子句，或者一个批处理是由几万个，甚至几十万个语句组成。
--那么这一步要花的内存有可能也会很大。而这部分内存，主要是stolen内存，在32位
--的SQL里是一块比较小的区域

--如果这一步内存紧张，有可能会出现下面这些等待状态
--cmemthread
--sos_reservedmemblocklist
--resource_semaphore_query_compile

--当然也可能直接报告 701 错误


--表格上的架构锁（schema lock）。当一条指令在编译的时候，他要防止别人对他要
--访问的对象（表，视图，存储过程等）进行架构上的修改。所以在这一步会有一些架构锁
--产生。如果同时有大量的并发用户在做一样的编译动作，或者有一个人在对象上申请了
--级别很高的锁，可能在指令编译的时候就遇到阻塞


--下面是一个压力测试下遇到的问题。并发用户在同时运行一个存储过程，而这个存储过程
--由于设计上的缺陷，每次运行都要进行编译，不能重用执行计划。编译的时候在存储过程
--上会申请架构锁，从而造成了阻塞


--在SQL确认是否有现成的执行计划可用时，要在内存中进行搜索。这时候可能会有一些
--自旋锁


--3、运行指令
--在得到执行计划以后，就进入运行阶段。在这个阶段，用到的资源是最多的。一般来讲
--在这一步花的时间也是最长的。运行一条指令，SQL要做很多事情：

--（1）SQL首先为指令的运行申请内存
--需要内存大小和指令的长度，复杂度有关。小的指令可能只要几KB，几十KB或者几百KB
--超大的指令可能需要几MB甚至十几MB。如果同时有很多复杂的指令在执行，可能新的
--指令在申请内存上会遇到困难。这时候会看到一些以resource_semaphore_开头的等待状态



--（2）如果发现要访问的数据不在内存里，要将数据从磁盘读到内存中，如果发现内存里
--没有足够的空闲页面存放所有数据，还要做一些内存整理和paging动作（换页动作）
--腾出足够的空间放数据
--这一步是挺花时间的。如果要访问的数据事先都缓存在内存里，这一步就不需要做了
--可以省下大把时间，这就是为什麽有些语句第一次运行会比第二次运行慢很多的原因
--也能解释为什麽一个内存资源不足的SQL性能有明显下降

--在这一步的等待状态，通常是PAGEIOLATCH_X 。这时候也会观察到其他表明内存和磁盘
--繁忙的现象

--（3）按照执行计划，扫描或者seek内存中的数据页面，将指令需要处理的记录找出来
--在这一步，SQL要申请各式各样的锁，以实现事务隔离。所以这个动作很容易遇到阻塞
--问题。等待状态一般是以LCK_X开头的那些


--（4）指令可能还要做一些联接或者计算的工作（sum ，max， sort等）
--这些动作要使用的资源主要是CPU。如果这方面比较复杂，或者选取的执行计划不够
--优化，导致计算量庞大，而指令没有遇到其他资源等待，那么会看到SQL CPU使用率
--比较高的现象

--（5）根据指令内存，执行计划和数据量，SQL可能还要在tempdb里创建一些对象，存放
--临时表，表变量，帮助做join，sort等

--这时候，tempdb可能会成为瓶颈，包括他的空间，系统表，系统页面等。我们会看到
--tempdb上的资源的等待


--（6）如果指令要修改数据记录，SQL会修改内存缓冲区里页面的内容
--由于修改的对象是内存里的页面，在这一步不会直接触发磁盘写入，所以应该
--不会直接导致磁盘瓶颈。但是如果有并发用户在修改同一个页面的时候，会出现
--PAGELATCH_X的等待状态


--（7）如果指令发生数据修改，在提交事务之前，SQL必须将相应的日志记录按照顺序
--写入事务日志文件
--这一步会发生对事务日志文件所在磁盘的物理写入。如果磁盘写入速度不够快，或者
--瞬间需要写入的日志量太大，会出现WRITELOG的等待状态

--（8）将结果集返回给客户端
--不是SQL将指令的结果集做好，指令就能够结束的。SQL会把结果集放入输出缓存，等
--客户端来把结果集完全取走，SQL这里指令才能够结束。如果结果集太大，或者一次
--批处理是由许多小的命令组成，导致网络交互太多，或者是应用程序自己有性能问题
--取数据取得很慢，都会导致指令整体运行时间延长。而这种等待也不是SQL能够控制解决的


--如果这里有等待，等待状态会是：ASYNC_NETWORK_IO（在SQL2000里是NETWORK_IO）

--当然，以上这些动作都要在SQLOS里首先拿到一个Worker（也就是thread），然后这个
--Worker还要能排上Scheduler，在CPU上运行。如果：

--SQL所有的WORKER都在忙自己的事情，没有空闲的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的并发性。在做性能问题定位的时候，先看一下出问题时候
--下面一类DMV里各个连接的状态，对找到问题正确方向会很有帮助
SELECT * FROM sys.[dm_exec_requests]