如何解析RESOURCE_SEMAPHORE和RESOURCE_SEMAPHORE_QUERY_COMPILE等待类型

Question

我们正在试图找出sql server查询运行缓慢的根本原因，这些查询访问/从一个数据库大小为300 GB的数据库中命中/获取数据，该数据库位于以下配置的服务器上：

Windows server 2003 R2，SP2，Enterprise，16 GB RAM，12 CPU的32位SQL server 2005，SP4，企业版，32位。

我们已经通知商界，升级到64位将需要一个多月的时间。

但是对于当前的问题，如果我们能够解决内存压力或最终得出增加RAM的结论，我们将试图收集数据。

操作已完成:重新索引和更新统计数据对此DB是适当的。

如下所示，在过去5天中，我们一直注意到信号量等待类型，在加载时间内运行：

以下查询后的几个信息: buffer= 137272的大小

SELECT SUM(virtual_memory_committed_kb)
FROM sys.dm_os_memory_clerks
WHERE type='MEMORYCLERK_SQLBUFFERPOOL'

和信号量memory= 644024每下面的查询

 SELECT SUM(total_memory_kb)
FROM sys.dm_exec_query_resource_semaphores

下面是从dm_exec_query_resource_semaphores和sys.dm_exec_query_memory_grants车管所收集到的更多信息

因此，从上面收集的信息和SP_Blitz数据资源信号量看来是问题所在。

资源信号量id的内存“target_memory_kb”分配得太低，而可用内存为16 GB。

注*每次分析8小时运行的'target_memory_kb‘总是在1GB以下，而可用的16 GB？

这里可能有什么问题，如何解决，请提出建议

谢谢

Answer 1

哦，天哪，我这里有个坏消息。

在32位操作系统上，Server只对查询工作区等使用前4GB的内存。(它也在为4GB的操作系统而战--任何其他正在运行的应用程序也将争夺这个内存。)

4GB听起来可能很多，但是编写一个需要几GB内存才能运行的查询相对容易。当足够的查询需要足够的内存时，Server会抛出RESOURCE_SEMAPHORE等待，因为查询无法获得足够的内存以启动。RESOURCE_SEMAPHORE_QUERY_COMPILE意味着他们甚至无法获得足够的内存来编译一个执行计划--是的，这非常糟糕。

那你是怎么解决的？

• 切换到64位操作系统(您正在运行的操作系统已经很久没有支持了)
• 切换到SQL Server的64位构建
• 减少服务器上的内存需求(不要在这个机器上运行任何其他应用程序，这在32位的机器上尤为重要，因为我们的内存上限只有4GB)。
• 在AWE/PAE开关中使用更多的内存--但这不适用于RESOURCE_SEMAPHORE等待，因为Server只能将前4GB用于查询工作区
• 调优查询和索引，以减少它们需要的内存

我甚至不愿说最后一个问题，因为32位问题太严重了，而且对于Server的旧版本来说，这真的很困难。如果是当前的，则可以通过计划缓存和按内存分配排序查询，找到最大的授予方，并对其进行调优。不过，这古老的古董没有选择余地。