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个被定义好的步骤来返回用户所请求的结果集。
1. 生成编译计划(compiled plan)。它包括各种逻辑指令，如怎么联接数据行。
2. 生成执行计划(execution plan)，它包含将编译计划中的各种逻辑引用转换成实际的对象的指令和查询执行的跟踪机制。
3. 从指令树的顶端开始执行。
生成编译计划是件开销较大的事情，因为它需要在数以百计的编译计划中找出较优的一个。它的时间通常很短，因为优化器会在找到最优的编译计划后便马上释放内存。编译主要使用内存和CPU资源。缺少可用内存可能会导致编译延迟和得到非最优的编译计划。
当SQL Server创建编译计划时，会计算两个参数：必须内存(Requeried memory)和额外内存(Additional memory)。
必须内存：执行排序和哈希联接所需的最少内存。这部分内存是“必须”的，它用来创建处理排序和哈希所需要的内部数据结构。
额外内存：存储所有临时数据行所需的内存。它的大小由基数评估(Cardinality estimate,如行数和行大小)决定。“额外”，顾名思义在缺少这部分内存时，将会将临时数据行存到硬盘上，并不会导致查询失败。一个查询的额外内存大小如果超过预设的限制，它实际得到的内存量并不一定会跟请求量一样。
例如，对行大小为10byte的100万行数据进行排序，此查询的必须内存为为512KB(此值是SQL Server处理一个排序操作创建内部数据结构所需的最小内存量)。为了存储所有数据行，额外内存可能是10MB。
当编译计划中含有多个排序和联接操作时，额外内存的计算就变得复杂了。因为SQL Server要考虑所有操作符如何高效地使用内存。可以查看ShowPlan XML中的<MemoryFractions>标记部分内容，获取更多内存使用的信息。
RESOURCE_SEMAPHORE_QUERY_COMPILE等待事件一般是查询正在等待授予内存以开始进行编译时发生。编译内存来自缓冲池（buffer pool），并需要保留足够的时间以完成编译过程。 对于多个并发编译而言，占用太多内存页可能会导致内存压力。 为了缓解这种情况，SQL Server启动编译过程，确定哪些查询需要大量的页面，并迫使某一些查询会话等待。 同样，如果内存压力已经存在，SQL Server将限制可以同时编译的资源密集型查询的数量。
如果你的数据库经常看到这种等待事件或此等待类型过多，那么你的数据库可能会有太多内存密集型查询（大型查询），或者其他进程可能正在从缓冲池中窃取内存页面.
减少等待事件方案
- Decrease query complexity 降低查询语句的复杂度。
- Appropriate indexing could reduce plan complexity 合理创建索引减少执行计划复杂度
- Improve plan reuse (therefore compilation can be avoided) 改善执行计划重用（因此可以避免编译）
- kill掉一些糟糕的SQL语句（内存资源密集型SQL），当然这个要看是否可行。
个人曾遇到过这样一个案例，由于过度灵活设计，导致很多报表需要在SQL中大量关联相关表，更糟糕的是，由于开发人员大量使用视图，尤其是还存在视图嵌套视图的情况，所以在这样一个系统中，一些查询语句往往需要授予大量的内存，尤其是当出现一个或一些写的很糟糕的SQL语句时，就会经常看到一些会话处于RESOURCE_SEMAPHORE_QUERY_COMPILE的等待状态，而且当大量会话处于RESOURCE_SEMAPHORE_QUERY_COMPILE等待时，还有一个特殊现象就是活动的会话数量会彪增，此时，可以找到消耗内存最多的SQL，然后Kill掉后，活动的会话就会立即降下来。下面就是我遇到案例的一个截图。
SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)
另外，内存紧张也会导致RESOURCE_SEMAPHORE_QUERY_COMPILE的出现的概率增加，那么是否增加内存就有效解决RESOURCE_SEMAPHORE_QUERY_COMPILE等待事件呢？答案是否定的，但是能缓解。如下描述：
This wait occurs when queries cannot be compiled due to the amount of compile memory currently available. This mostly occurs due to large queries requiring an excessive amount of memory. SQL Server caps the amount of complex queries that can be compiled at once, so increasing the memory allocation will not solve the problem effectively (it will only increase the amount of memory that can be allocated, not the number of queries)
参考资料：
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql
https://documentation.red-gate.com/sm4/working-with-overviews/using-performance-diagnostics/list-of-common-wait-types/resource_semaphore_query_compile
https://www.sqlskills.com/help/waits/resource_semaphore_query_compile/
最新文章
- JQ的表单验证
- TCP三次握手四次挥手
- Neil·Zou 语录三
- win10 用cmake 3.5.2 和 vs 2015 update1 编译 GPU版本(cuda 8.0, cudnn v5 for cuda 8.0)
- 360和QQ大战之我见
- 按要求编写Java应用程序。 编写一个名为Test的主类，类中只有一个主方法； 在主方法中定义一个大小为50的一维整型数组，数组名为x，数组中存放着{1， 3，5,…,99}输出这个数组中的所有元素，每输出十个换一行；在主方法中定义一 个大小为10*10的二维字符型数组，数组名为y，正反对角线上存的是‘*’，其余 位置存的是‘#’；输出这个数组中的所有元素。
- wpf 前台获取资源文件路径问题
- jsp\struts1.2\struts2 中文件上传（转）
- zookeeper安装及部署
- C#6.0 VS2015
- 开发自定义View
- codeforces#256DIV2 D题Multiplication Table
- HiPAC高性能规则匹配算法之查找过程
- java系列--过滤器
- 前端技术大行其道，再不拥抱TypeScript你就老了！
- python基础 常见用法
- git上传新项目到coding
- 洛谷P4065 [JXOI2017]颜色(线段树)
- githug rename_commit 修改已经commit但还没push的一条message
- C# 调用Windows图片查看器