Troubleshooting SQL Server RESOURCE_SEMAPHORE Waittype Memory Issues

 

 前言： 本文是对博客https://www.mssqltips.com/sqlservertip/2827/troubleshooting-sql-server-resourcesemaphore-waittype-memory-issues/的翻译,本文基本直译，部分地方读起来有点不自然。 如有翻译不对或不好的地方，敬请指出，大家一起学习进步。尊重原创和翻译劳动成果，转载时请注明出处http://www.cnblogs.com/kerrycode/p/7068246.html。谢谢！

 

 

问题描述

 

今天，我们的一个SQL Server实例性能变得非常慢。当我登录到数据库服务器进行一些初始检查时，我最初始观察、注意到的是它的内存压力（memory pressure）。接下来，我们必须找出是什么导致我们的实例出现内存紧张。当我检查事务的等待类型时，RESOURCE_SEMAPHORE等待是大多数事务的问题。在这篇文章中，我将描述这个问题，以及如何查找哪个查询语句或事务导致了内存压力

 

解决方案

 

当我检查所有事务的等待类型时，大多数事务的等待类型为RESOURCE_SEMAPHORE 等待以及某些页面IO等待。页面IO等待也是由于内存压力导致，因为这些事务无法获得足够的内存来执行这个操作。

资源信号量等待

 

在继续之前，我想对资源信号量（Resource_semaphore）等待进行一些说明，以便您可以更好地了解SQL Server是如何将内存授予SQL Server查询语句的。

当SQL Server收到用户发送的查询请求（查询语句）时，它首先创建一个编译过的计划，然后在这个基础上创建一个执行步骤（个人觉得执行步骤比执行计划要合适）。当SQL Server创建一个编译过的计划时，它会计算两个内存授予参数，称为“请求内存”（required memory）和“附加内存”(additional memory)。请求内存是运行排序和HASH连接所需的最小内存。它之所以是 "必需"的, 是因为如果没有可用的“请求内存”, 查询将无法启动。附加内存（additional memory）是在内存中存储临时行（个人觉得翻译为中间结果可能更合理）所需的内存量。这被称为额外（附加）的，因为如果没有足够可用的“附加内存”可以将查询的中间结果存储在磁盘上。 

首先，服务器计算任何给定的查询执行需要多少内存。这通常是“请求内存”（required memory）和“附加内存”(additional memory)的总和，但如果您的实例正在并行处理，那么所需的内存将是（所需的内存* DOP）+附加内存。服务器检查所需的内存是否超过每个查询限制，然后服务器减少“附加内存”，直到总数达到限制。这个修改后的大小称为请求内存。SQL Server有一个内部工具称为资源信号量（RESOURCE SEMAPHORE)，用于将此请求的内存授予查询。如果无法通过资源信号量向该请求的内存授予查询，那么如果查询sys.sysprocesses系统表或​​sys.dm_exec_request DMV，则该查询将处于等待状态，并出现RESOURCE_SEMAPHORE等待类型。

当资源信号量（Resource_semaphore）接收到新的请求时，它首先检查是否有任何查询正在等待中。只要发现存在即是一个等待查询，那么会将新查询（新请求）放入队列中，因为等待队列是以先到先得的方式设计的，并且有小权重以支持于小型查询。当没有等待查询或查询返回保留的内存时。资源信号量尝试授予内存。如果找到足够的内存，那么请求内存被授予并且查询可以开始运行，并且如果没有找到足够的可用内存来授予所请求的内存，那么它将当前查询放入等待队列中，并且给当前会话RESOURCE_SEMAPHORE等待类型， 此时服务器开始面临内存压力。

 

 

识别RESOURCE_SEMAPHORE等待

 

步骤1

 

 

首先，我们需要研究我们的实例，弄清楚为什么在SQL Server中出现内存压力。要查看所有事务的大概信息，我们可以查询sys.sysprocesses，或者我们可以使用sys.dm_exec_requests DMV。

SELECT * FROM sys.sysprocesses

ORDER BY lastwaittype

 

 

这里我们可以看到所有产生RESOURCE_SEMAPHORE等待类型的进程。

 

步骤2

 

从上面的SQL查询，我们可以看到大量的事务正处于Resource Semaphore（资源信号量）等待状态。现在我们可以运行下面的SQL语句来查看已分配到内存的查询的目前状态，和未被分配内存的查询的数量。                

                                                       

该DMV的输出返回两行，一个表示大型查询（resource_semaphore_id为0），另一个表示小型查询（resource_semaphore_id为1），小于5 MB。在这里，您可以获得实例的总授予内存和总可用内存。请参阅grantee_count和waiter_count上的数字，grantee_count是已经分配了内存的总查询数，waiter_count是队列中等待获取内存的总查询数量。所以在这里我们可以看到大约100个查询正在等待获得他们要求的内存。

 

SELECT * FROM sys.dm_exec_query_resource_semaphores

 

 

步骤3

 

现在我们将获取所有正在等待队列中获取所请求的内存的所有查询的详细信息，我们将使用DMV sys.dm_exec_query_memory_grants来获取队列中等待分配内存的查询总数。对于等待获取其请求的内存的查询，grant_time和granted_memory_kb列将为NULL。您可以在下面的截图中查看所请求的内存量及其等待状态，因为它们的grant_time和granted_memory_kb值为NULL。我们还可以使用该DMV获取所有查询的plan_handle和sql_handle。稍后我们将使用这些值来获取确切的查询。

注意：有太多列要显示，可以只显示部分所需的列。

 

SELECT * FROM sys.dm_exec_query_memory_grants

 

 

 

步骤4

现在，我们将找到内存密集型查询。我们可以看到所有等待查询的请求内存。在这里我们可以看到所请求的内存对于大多数事务来说太大了。我们将获得所有这些查询的plan_handle，以获得确切的SQL文本来查看查询计划。

从sys.dm_exec_query_memory_grants中选择前10个*

 

select top 10 * from sys.dm_exec_query_memory_grants

 

步骤5

 

现在我们将使用上面查询所获得的plan_handle和sql句柄来获取SQL代码。

运行以下语句从上述查询中获取SQL代码，使用sql_handle。

 

SELECT * FROM sys.dm_exec_sql_text（sql_handle）

 

我们还可以使用步骤4中查询中的plan_handle获取SQL计划。

SELECT * FROM sys.dm_exec_sql_plan（plan_handle）   --译者注  实际没有sys.dm_exec_sql_plan, 而是sys.dm_exec_query_plan，估计是作者笔误。

 

 

结论

 

现在我们已经找到内存密集型查询及其执行计划，我们的下一步是研究这些查询，并找出如何调整、优化它们。我们应该查看查询中是否使用的错误的索引或是否存在索引缺失的情况，并创建正确的索引。在我们这种情况下，这是由于糟糕的索引设计导致了内存紧张。在创建、调整合适的索引之后，相同的查询运行的时候请求的内存就会少得多。

 

下一步

 

• 使用此提示来识别由于内存不足而消耗更多内存并将剩余的事务置于等待状态的查询。
• 还要查看上述DMV的其他列，并将它们联系起来，以便更好地分析和了解性能问题。
• 这些DMV应提供大量信息，以便您能够识别问题。
• 阅读有关性能调优的更多提示，以提高系统性能。