合 MSSQL中的等待事件RESOURCE_SEMAPHORE和RESOURCE_SEMAHPORE_QUERY_COMPILE说明
Tags： MSSQLSQL Server等待事件内存不足内存授予性能问题RESOURCE_SEMAPHORERESOURCE_SEMAHPORE_QUERY_COMPILE
简介
SQL Server 有 两类RESOURCE_SEMAPHORE 等待事件： RESOURCE_SEMAPHORE 和 RESOURCE_SEMAHPORE_QUERY_COMPILE.
RESOURCE_SEMAPHORE是指一条或多条语句在执行的时候等待memory的分配。
RESOURCE_SEMAHPORE_QUERY_COMPILE是指一条语句在编译的时候等待memory的情况。
资源信号（RESOURCE_SEMAPHORE）
RESOURCE_SEMAPHORE 等待类型表示一个Workder等待SQL Server给予其申请的内存，以便执行Hash和Sort（order by、group by、distinct 、union）等操作。
1，RESOURCE_SEMAPHORE 揭示内存压力
当出现 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 面临内存压力。
RESOURCE_SEMAPHORE_QUERY_COMPILE
等待事件介绍
关于等待事件RESOURCE_SEMAPHORE_QUERY_COMPILE，官方的介绍如下：
Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.
该等待事件在并发查询编译的数量达到阀值限制时出现。 等待时间较长或等待次数较多可能表明编译、重新编译或不能缓存的计划过多。
等待事件分析
内存授予的等待类型叫做“RESOURCE_SEMAPHORE”.在理解这个等待事件前，我们先来了解一下查询内存授予(query memory grant)，它是用于在排序或连接时存储临时数据的服务器内存的一部分。查询在实际执行前需要先请求保留内存，所以会存在一个授予的动作。这样的好处是提高查询的可靠性和避免单个查询占用所有的内存。
SQL Server在收到查询时，会执行3个被定义好的步骤来返回用户所请求的结果集。
- 生成编译计划(compiled plan)。它包括各种逻辑指令，如怎么联接数据行。
- 生成执行计划(execution plan)，它包含将编译计划中的各种逻辑引用转换成实际的对象的指令和查询执行的跟踪机制。
- 从指令树的顶端开始执行。
生成编译计划是件开销较大的事情，因为它需要在数以百计的编译计划中找出较优的一个。它的时间通常很短，因为优化器会在找到最优的编译计划后便马上释放内存。编译主要使用内存和CPU资源。缺少可用内存可能会导致编译延迟和得到非最优的编译计划。
当SQL Server创建编译计划时，会计算两个参数：必须内存(Requeried memory)和额外内存(Additional memory)。
必须内存：执行排序和哈希联接所需的最少内存。这部分内存是“必须”的，它用来创建处理排序和哈希所需要的内部数据结构。
额外内存：存储所有临时数据行所需的内存。它的大小由基数评估(Cardinality estimate,如行数和行大小)决定。“额外”，顾名思义在缺少这部分内存时，将会将临时数据行存到硬盘上，并不会导致查询失败。一个查询的额外内存大小如果超过预设的限制，它实际得到的内存量并不一定会跟请求量一样。
