从等待类型找出性能瓶颈
1. Resource_semaphore等待：
SQL Server 有两类RESOURCE_SEMAPHORE 等待事件： RESOURCE_SEMAPHORE 和 RESOURCE_SEMAHPORE_QUERY_COMPILE.Resource_SEMAPHORE是指一条或多条语句在执行的时候等待memory的分配。RESOURCE_SEMAHPORE_QUERY_COMPILE是指一条语句在编译的时候等待memory的情况。
首先我们来弄清楚一下什么是Resource_semaphore等待。当SQLServer收到一个用户请求（或者查询时）。首先会创建一个编译后的计划，然后在这个基础上创建一个执行计划。当SQLServer创建一个编译后的计划时，它会计算两个内存授予参数，分别为：请求内存（required memory）和额外内存（additional memory），请求内存是运行排序和hash连接的所需最少内存，之所谓成为“请求”，是因为查询不需要在一开始就申请这部分的内存。而额外内存是存放临时数据到内存中所需的那部分内存。如果没有足够的内存，查询所需的数据将会存到硬盘当中。首先，服务器会计算运行特定查询所需要的内存。这部分通常等于请求内存和额外内存的总和。但当实例使用并行执行时，所需的内存为（请求内存*并行度）加上额外内存的总和。服务器会检查是否有足够的内存来运行每个查询，然后会降低额外内存的量，知道所有总内存需求量刚好达到内存的限制量。这部分修改后的内存成为需求内存（requested memory）。在SQLServer内不能，有一个叫Resource Semaphore的设置，用于授予需求内存用于查询。当查询没有得到足够的内存，就会把等待状态改为：Resource_Semaphore。可以从sysprocesses系统表或者sys.dm_exec_request DMV中查询。当Resource_semaphore接受一个新的请求时，首先检查是否有查询还在等待中，如果发现有，那么会把这个新请求放到先进先出的队列中，Resource Semaphore会尝试对未等待的查询授予内存，这部分内存可能是之前的查询执行完毕后返回的内存。如果发现有足够的内存，那么就会把内存赋予给处于Resource Semaphore等待状态的查询，让其开始运行。如果不够，那么会把查询放入等待队列并标记为Resource_Semaphore等待。因此，看这个等待状态可以发现内存存在压力。
总结：当由于存在其他并发查询而无法立即批准查询内存请求时出现。等待时间较长或等待次数较多可能指示并发查询的数量过多或内存请求的数量过多。即内存存在内存的压力，会造成页面I/O等待。
可以通过动态视图DV查看具体是哪些语句造成的：
1.筛选Resource_Semaphore等待的数据： SELECT * FROM sys.SYSPROCESSES WHERE lastwaittype = 'RESOURCE_SEMAPHORE' ORDER BY lastwaittype /*******************************2.查看已分配到内存的查询的目前状态，和未被分配内存的查询的数量*********************** dm_exec_query_resource_semaphores:提供一般的查询执行内存状态，并允许您确定系统是否可以访问足够的内存 resource_semaphore_id:资源信号的非唯一标识。0为常规资源信号量，1为小型查询资源信号量。 grantee_count是已经分配了内存的总查询数量，而waiter_count是在队列中等待授予内存的总查询数量 ********************/ SELECT * FROM sys.dm_exec_query_resource_semaphores /******************************3.获得在等待队列中的查询所需要内存的详细信息********************************** dm_exec_query_memory_grants:返回所有已请求并正在等待内存授予或已被授予内存授予的查询的信息*********************** requested_memory_kb:请求的内存总量 granted_memory_kb:实际授予的内存总量 实际授予的内存总量,对于一个典型的情况，这个值应该和requested_memory_kb一样 required_memory_kb:运行此查询所需的最小内存 ***********************************************************/ select * from sys.dm_exec_query_memory_grants --4.查看对应的执行计划 SELECT TOP 10 * FROM sys.dm_exec_query_memory_grants a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle)
2. ASYNC_NETWORK_IO等待
此等待状态出现在SQLServer已经把数据准备好，但是网络没有足够的发送速度跟上，所以SQLServer的数据没地方存放。
- 出现这种情况一般不是数据库的问题，调整数据库配置不会有大的帮助。
- 网络层的瓶颈当然是一个可能的原因：对此要考虑是否真有必要返回那么多数据？
- 应用程序端的性能问题，也会导致SQLServer里的ASYNC_NETWORK_IO等待。如果见到了这个类型的等待，就要检查应用程序的健康状况，也要检查应用是否有必要想SQLServer申请这么大的结果集。
将结果集返回给客户端,得到结果后，SQLServer会把结果集放到输出缓存中，等客户端把结果集全部取走。指令才结束。如果数据集太大，会导致网络交互太多。此时容易出现：ASYNC_NETWORK_IO等待状态。这个等待类型表示并非SQL Server的问题，绝大多数情况下也与网络问题无关（很多时候大家都认为是网络问题），一个简单的测试方式是从客户端ping一下服务端，如果延迟接近sys.dm_exec_requests中wait_time的平均值则证明确实与网络相关（很多时候都只是正常的网络延迟，并不是网络故障）。
- 客户端代码使用RBAR方式处理数据集，每次只从结果集拉取一条数据，而不是全部获取完毕后再处理。
- 客户端所在的服务器有某些性能问题，导致客户端运作缓慢。
- 客户端运行在配置错误或者过载的虚拟机上，总之也是服务器本身的问题。
- SQL只取必须的字段数据
- 查应用是否有必要想SQLServer申请这么大的结果集
3. CXPACKET等待
EXEC sys.sp_configure N'cost threshold for parallelism', N'1' GO RECONFIGURE WITH OVERRIDE GO
Data-warehousing / Reporting server: 因为查询执行时间一般较长，建议设置“Maximum degree of Parallelism”（MAXDOP）为0。 这样大多数查询将会利用并行处理，执行时间较长的查询也会受益于多处理器而提高性能。
EXEC sys.sp_configure N'cost threshold for parallelism', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
Mixed System (OLTP & OLAP):这样环境会是一个挑战，必须找到正确的平衡点。我采取了非常简单的方法。我设置“Maximum degree of Parallelism”（MAXDOP）为2，这样意味着查询仍会使用并行操作但是仅利用2颗CPU。
然而，我把“并行查询阀值”（cost threshold for parallelism）设置为较高的值，这样的话，不是所有的查询都有资格使用并行，除了那些查询成本较高的查询。在一个即有OLTP查询又有报表服务器的系统上，
我发现这样做运行得很好。在这里我将会设置“‘Cost Threshold for Parallelism’”为25,你可以选择任何值。但你只能通过在系统上做实验来找到合适的值。在下面的脚本中，我设置“Max Degree of Parallelism”为2，
这样的话，那些具有较高成本的查询(这里是25)，将会在2颗CPU上执行并行查询。同时，不管服务器有多少颗CPU，查询只会选择两颗CPU来执行。
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
导致CXPACKET等待高的原因有很多，不能盲目地修改 MAXDOP的值或修改实例级别的最大并发度
可能原因有：
- 统计过期导致生成低效的执行计划
- 缺乏索引导致表扫描
- 中间结果集无法预估结果集行数，导致执行计划低效（即统计信息不准确）
- 某个线程因为其他资源被堵塞
解决方案：
- 检查执行计划是否高效
- 修改语句的并发度
- 修改实例级别的并发度
补充：建议将MAXDOP的值设置为小于逻辑CPU的数，以避免单个查询阻塞所有请求。
总结：CXPACKET 这个等待可以简单理解成CPU相关的等待，主要发生在并行计划中。由于并行计划需要协同多个task同时工作，那么“协同”分配等等操作的时候出现的就是这个等待。如果 CXPACKET 在你系统中是最为严重的等待，
这时候一般的表现是你的CPU很高。一般建议系统如果超过32个CPU 那么并行度设置成8或者4，如果系统中都是特别短小且频繁的语句建议设置成1（取消语句并行，要慎重真的符合你的场景才好）, 并行开销的阀值，
主要控制SQL优化器何时选用并行计划，建议默认值，此值设置的越小优化器越容易选择并行计划。
4.Latch_Ex等待
5. IO类
PAGEIOLATCH_ 是数据页相关的磁盘等待。WRITELOG 是日志相关。如果系统中这三个等待是主要等待，说明系统磁盘存在压力或已经成为瓶颈。
PAGEIOLATCH_ 等待：
pageiolatch_x类型等待最常见的是两类,pageiolatch_sh和pageiolatch_ex。pageiolatch_sh经常发生在用户正想要访问一个数据页面,而同时 sql server却要把这个页面从磁盘读到内存,如果这个页面是
用户经常有可能访问到的,那么说到底,问题是因为内存不够大,没能将数据页面缓存在内存里, 所以,往往是现有内存的压力,触发sql server做了很多读取页面的工作,才引发了磁盘读的瓶颈,这里的磁盘瓶颈
常常是内存瓶颈的副产品. 而pageiolatch_ex常常是发生在用户对数据页面做了修改,sql server要向磁盘回写的时候,基本意味着磁盘的写入速度明显跟不上,这里和内存瓶颈没有直接的联系.
和磁盘有关的另一个等待状态是writelog，说明任务当前正在等待将日志记录写入日志文件，出现这种等待，也意味着磁盘的写入速度明显跟不上。
可能导致的原因:
- IO系统的确存在问题,存在IO瓶颈
- 执行计划不佳导致扫描
- 内存存在压力导致页在内存中的存活时间过短
解决方案：
- 检查执行计划
- 检查是否存在内存压力
- 检查是否存在其他应用导致IO压力
- 检查存储是否满足需求
1>导致大量事务日志的操作产生，比如索引维护2>事务并发度较高3>IO系统瓶颈4>频繁页拆分
解决方案
1>查看LOGBUFFER等待，是否存在对日志缓冲区的争抢2>查看日志所在磁盘是否存在队列3>查看事务平均大小，是否存在小事务频繁操作，将小事务合并为大事务4>删除无用索引5>业务拆分，由多个数据库来承担压力6>提高日志磁盘的性能7>修改索引，减少页分裂
6. pagelatch
- SQL Server没有明显的内存和磁盘瓶颈。
- 应用程序发来大量的并发语句在修改同一张表格里的记录，而表格架构设计以及用户业务逻辑使得这些修改都集中在同一个页面，或者数量不多的几个页面上。这些页面有的时候也被称为Hot Page。这样的瓶颈通常只会发生在并发用户比较多的、典型的OLTP系统上。
- 这种瓶颈是无法通过提高硬件配置解决的，只有通过修改表格设计或者业务逻辑，让修改分散到尽可能多的页面上，才能提高并发性能。
这样sql server在要插入新数据的时候,就能很快的找到开始插入的位置,而页尾的偏移记录了每一条数据行的起始位置,这样sql server在找每一条记录的时候,
就能很快找到.当页面里的数据行发生变化的时候,sql server不但要去修改数据本身,还要修改这些偏移量的值.以保证sql server能够继续准确的管理数据页面的每一行.
- 最简单的办法,是换一个数据列建聚集索引,而不要建在identity的字段上,专业表格里的数据就按照其他方式排序,同一时间的插入就有机会分散到不同的页面上.
- 如果必须在identity列上建聚集索引,建议根据其他某个数据列在表格上建立若干个分区,把一个表格分成若干个分区,可以使得接受新数据的页面增多.
- GUID类型的索引键造成的页拆分,设置合适的填充因子或修改GUID键
- 数据变化过大引起的页拆分,将变化较大(长度)且变化频率较高的列拆分到其他表
- 控制索引键的长度
解决高并发插入自增表导致的插入热点：
- 使用随机或组合键来将数据分散到表中各个部分而不是尾部
- 修改架构,将数据插入到多个表中或多个数据库中
- 不合理的事务隔离级别
- 过大的事务长期占有某些资源
- 执行效率不高的语句
- 语句优化，让语句执行的更快，减少等待时间。
- 采用批量操作代替循环方式。
- 尽量减少事务的长度。
- 尝试降低事务隔离级别。
- 述都不能缓解...请选用读写分离。
8. sos scheduler yield
SQL Server的thread scheduling（线程调度）是由SQL Server自己来完成的。它分为三个状态，running, suspended, runnable。我画了一个图来解释这三个状态：
9. REDO_THREAD_PENDING_WORK
