在查询执行时,等待次数和等待时间在一定程度上指示查询的瓶颈,甚至非常有助于对系统进行诊断,对查询语句进行性能调优。偶尔一次的异常等待,不足以表明系统存在瓶颈,但是,SQL Server实例经常出现特定的等待类型,并且等待时间趋于增加,这就说明,系统存在压力,或内存,或IO等,根据WaitType对系统进行监控和诊断,还能对查询进行性能调优,例如,Lock等待表明查询存在数据竞争,PageIOLatch等待表明IO响应缓慢,PageLatch等待表明文件的布局需要改进等。
一,资源信号(RESOURCE SEMAPHORE)
1,RESOURCE_SEMAPHORE 等待类型表示一个Workder等待SQL Server给予其申请的内存,以便执行Hash和Sort等操作
当出现 RESOURCE_SEMAPHORE 等待时,这说明查询语句请求的内存没有得到满足,就是说,该查询语句在执行Task前,需要一定量的内存资源,如果SQL Server当前的内存不足,不能分配查询语句请求的内存,将导致查询语句处于等待内存资源的状态。在SQL Server存储引擎中,排序(Sort)操作和哈希(Hash)操作是非常消耗内存资源的两个操作,优化相应的查询语句,以减少这两个操作,可以缓解SQL Server的内存压力,但在SQL Server实例中,经常出现RESOURCE_SEMAPHORE 等待,这说明SQL Server存在内存压力。
在数据库中有一个选项,Min Memory Per Query,该选项表示SQL Server为每个查询分配的最小内存,这意味着,当一个查询需要额外的内存资源,该查询获取的内存大小,很大部分是由该选项决定的,只有为每个查询授予一定的内存之后,该查询语句才会真正开始执行。
2,发送RESOURCE SEMAPHORE用于授予请求内存(Requested Memory)
当SQL Server实例收到用户的查询请求时,SQL Server优化器首先创建编译计划(Complied Plan),根据编译计划再创建执行计划(Execution Plan)。当SQL Server优化器创建编译计划时,它需要计算查询在执行时需要消耗的内存,用于执行查询的内存分为必需内存(Required Memory)和额外内存(Additional Memory)。必需内存是指SQL Server实例执行Sort或Hash操作时必须分配的最小内存,如果没有分配必需内存,查询请求不会执行。额外内存是查询用于存储临时的中间数据的内存,如果SQL Server没有足够的内存,查询将临时数据存储在硬盘中,这会降低查询性能。
SQL Server 要授予每个查询多少内存,查询才能真正开始执行呢?
Step1,计算需要的内存(Needed Memory):SQL Server计算每个查询需要多少内存才能执行,这通常是必需内存和额外内存之和,当查询请求以并发方式执行时,需要的内存公式是:(RequiredMemory*DOP)+额外内存。
Step2,计算请求的内存(Requested Memory):SQL Server检查每个查询请求需要的内存数量是否超出系统的限制,SQL Server减少额外内存的数量,以致于不会超出系统的上限,这个最终的内存数量是查询语句得以执行的请求内存。
Step3,为查询分配请求内存:SQL Server实例发送资源信号(RESOURCE SEMAPHORE),为查询(Query)授予/分配请求的物理内存。
当资源信号发送之后,如果SQL Server实例不能被授予查询的请求内存,那么查询将处于RESOURCE_SEMAPHORE 等待状态。SQL Server维护一个先入先出( first-come-first-served)的等待队列,当新的查询处于RESOURCE_SEMAPHORE 等待状态,SQL Server将该查询放入队列的末尾。一旦SQL Server实例找到足够的空闲内存,那么SQL Server取出RESOURCE_SEMAPHORE 等待队列顶端的第一个查询,立即授予其请求的内存;该查询获得请求内存之后,开始执行查询任务;如果SQL Server实例长时间有查询处于RESOURCE_SEMAPHORE等待状态,说明SQL Server 面临内存压力。
二,调度队列信号
DISPATCHER_QUEUE_SEMAPHORE,发生当一个进程(Thread)等待处理更多的Work时,该等待是说,一个Thread处于空闲状态,等待调度去工作。如果等待时间增加,说明调度器(Dispatcher)非常空闲;该WaitType不会成为竞争资源,而将其他事务阻塞,在做Wait统计分析,可以过滤掉。
三,IO等待
1,异步网络IO
ASYNC_NETWORK_IO等待类型,是指SQL Server 产生的结果集需要经过网络(Network)传递到客户端(Client),网络不能及时将结果集传输到Client,导致结果集仍然驻留在SQL Server的会话(Session)中,这意味着,ASYNC_NETWORK_IO 等待状态出现在SQL Server已经把数据准备好,但是网络发送速度跟不上,导致SQLServer返回的数据集仍然驻留在Session中,出现这种等待一般不是数据库的问题,调整数据库配置不会有大的帮助,网络层的瓶颈当然是一个可能的原因,对此要考虑是否真有必要返回那么多数据?所以,检查应用程序是否有必要向SQL Server申请这么大的结果集。
2,等待异步IO完成(ASYNC_IO_COMPLETION)
ASYNC_IO_COMPLETION:当Task正在等待IO完成时发生,长时间的 ASYNC_IO_COMPLETION 等待经常发生在SQL Server正在执行数据库备份和还原操作(执行Backup database 命令和 Restore 命令)时,查看《WaitType:ASYNC_IO_COMPLETION》了解更多。
3,IO完成等待(IO_COMPLETION)
在发生CXPACKET等待时,如果会话(Session)的数据IO(Logical Read/Write, Physical Read)都没有变化,那么,该会话很可能正在处理非数据页的IO操作。IO_COMPLETION等待通常用于表示非数据页的IO操作,例如,事务日志的还原操作,读取系统数据页(GAM)等。通常来说,减少IO_COMPLETION 等待的方法有两种:一是将IO分散到不同的Physical Disk上,一是减少对非数据页的IO操作。
DMV:sys.dm_exec_requests 统计的是查询请求对数据页的Logical Read/Write, Physical Read 操作。
利用 sys.dm_os_waiting_tasks 查看查询请求的所有Task,发现有一些Task 处于 IO_COMPLETION 等待,这说明,会话(Session)发生CXPACKET等待,是因为在SQL Server以并行方式执行Task,有一些 Task执行速度慢,有些Task执行速度快,导致执行速度快的task完成之后,等待还未完成的Task;而执行速度慢的Task正在执行非数据页IO。
四,硬盘IO相关的等待
1,PageIOLatch
当缓存在buffer pool 的data page 和disk 上数据文件里的data page 进行交互时,为了保证不会有多个用户同时 read/write 内存中的buffer(a data page in memory),需要对buffer 加上PageIOLatch。PageIOLatch 是和 IO 有关,或从disk将Data page读取到内存,或从内存将Data page写入到disk。
PageIOLatch主要分为两大类:PageIOLatch_SH和PageIOLatch_EX
PageIOLatch_SH:发生在将一个Data Page从Disk 读取到内存 buffer pool 中时。当用户需要访问一个Data Page,而这个Data Page不在内存中时,SQL Server 需要将 Data page 从Disk 读取到内存中,这说明内存不够大,或内存紧张,导致没有将Data Page始终缓存在内存中,SQL Server 需要过多地Page Read(从Disk读取Data page到内存 buffer pool)操作。这种情况说明内存是bottleneck。
PageIOLatch_EX:发生在用户对内存中的Data page进行了修改,SQL Server需要写回Disk,意味着disk的写入速度慢。
2,PageLatch
PageLatch 是对内存中的buffer(a data page in memory)加锁,用于同步内存 buffer Pool中的Data Page数据修改操作。当一个task需要修改 buffer时,必须申请PageLatch_EX。只有得到这个Latch,才能修改buffer里的内存。
由于buffer的修改都是在memory中完成的,所以每次修改的时间都应该非常短,而PageLatch只是在修改的过程中才会短暂出现。如果出现PageLatch等待,说明大量的并发语句在修改table,而修改操作同时集中在同一个page,或者数量不多的几个page上,这些Page 称作Hot Page。出现Hot Page 是由于数据过于集中导致,将数据分布在不同的Files上,能够减少PageLatch Wait。
3,WriteLog
和Disk的写速度有关,说明任务当前正在等待将日志记录写入日志文件,出现这个等待状态,意味着Disk的写入速度是性能瓶颈。
五,锁管理器等待(LCK_M_**)
LCK_M_** 等待是Thread正在等待获取锁产生,表明系统产生阻塞问题,一般情况下,是由于编程质量差的查询语句造成的,锁升级,或长时间的IO操作,也会使Task长时间持有锁,产生LCK_M_** 等待。
LCK_M_IS:当Task正在等待获取意向锁(IS,Intent Shared)时发生
LCK_M_U:当Task正在等待获取更新锁(U,Update)时发生
参考文档:
Causes of IO_COMPLETION and WRITE_COMPLETION SQL Server wait types
SQL SERVER – IO_COMPLETION – Wait Type – Day 10 of 28
DISPATCHER_QUEUE_SEMAPHORE
Troubleshooting SQL Server RESOURCE_SEMAPHORE Waittype Memory Issues
Wait statistics, or please tell me where it hurts