SQL Server 高 CPU 使用率和 RESOURCE_SEMAPHORE 等待

上周在其中一个 SQL Server 上出现问题，CPU 开始燃烧超过 80%（正常为 10-30%）
这持续了大约 2 小时，直到我手动故障转移到 AG 中的辅助副本（这已经解决了问题）

问题开始：12:15
问题结束：14:15（手动 AG 故障转移后）

服务器信息：

SQL Server 2017  
32 logical processors (max DOP = 8)  
256 GB RAM (Max Server Memory = 180 GB, used 179 GB)

问题开始前与问题开始后相比，以下指标没有明显变化

• 用户连接数/秒（平均 200-300）
• 批处理请求/秒（平均 200 次及以下）
• 数据库缓存内存（~150 GB）

低于指标峰值显着，这对于该服务器来说并不典型（通常这些指标很低）：

• 中央处理器 ( 超过 80 % )
• 内存授予待定
• 锁定等待/秒，平均。锁定等待时间，死锁
• 锁存等待时间
• 授予的工作区内存和保留的服务器内存

查询：

当问题开始时，我没有注意到此服务器的工作负载发生变化
开发人员还确认应用程序完成了他们通常的工作并且正在运行通常的查询，应用程序负载没有峰值

在这个“高 CPU 使用率”问题期间，CPU 的前 10 个查询看起来并不异常
即使 CPU 正常，我们通常看到的前 10 个查询都是相同的（10-30 %）

问题：

问题似乎出在几个相关的存储过程中，该应用程序通常运行 1-4 次/秒，并且通常在 50 毫秒内完成，但是在问题期间，任何时候我检查过 sys.dm_exec_requests（也使用了exec ViewSessionsConnections 'running' https ://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/Sessions%20-%20ViewSessionsConnections.sql），有来自 1 个应用程序的 50-70 个会话，所有这些都试图完成上述程序，而且速度很慢

在监控工具中按持续时间查看前 10 个查询时，前 1 和 2 是上述过程中的两条语句 - 它们没有消耗大量 CPU，但有过多的等待（RESOURCE_SEMAPHORE、LCK_M_IS）

通常这些在 10 毫秒或更短的时间内完成，每秒执行 1-4 次并且不会引起任何问题，现在这些开始每 1 次执行的持续时间为 4000-8000 毫秒，这就是问题所在

RESOURCE_SEMAPHORE 绝对不是此服务器的典型情况，但在问题期间，它处于最高等待状态（RESOURCE_SEMAPHORE - 等待授予内存的查询；2 小时内总计 135400234 毫秒；平均 4174 毫秒）

Granted Workspace Memory在 SQL Server 中，Reserved System Memory在问题期间从 0 GB 飙升至 ~110 GB

问题：

1. 你对上面有什么想法和经验？

2. 常量 RESOURCE_SEMAPHORE 等待和 Memory Grants Pending 是否会导致 CPU 压力仅仅是为查询分配工作空间内存？因为在问题期间查看 CPU 的前 10 个查询时，CPU 数量看起来正常/正常

3. 鉴于一直是 180 GB 和~ 150 GB ，问题开始时怎么会这样Granted Workspace Memory并开始消耗 ~ 112 和 110 GB？它是否过度使用内存或类似的东西？Reserved Server MemoryMax Server MemoryDatabase Cache Memory remained

4. 为什么通常在几个月内 10 毫秒内完成的 SP 中的语句会开始经历 RESOURCE_SEMAPHORE 等待并在 4000-8000 毫秒内完成？

5. 如何在不手动故障转移到辅助副本的情况下以更外科手术的方式解决问题？如何让查询平静下来并将其恢复到 10 毫秒？需要删除计划，或者重新编译查询等？最好的方法是什么？

6. Brent Ozar First Responder Kit 或其他诊断程序 - 在性能问题期间应按什么顺序执行，以便更好地了解发生了什么？