失败的分配页: FAIL_PAGE_ALLOCATION 1

Question

我们在Windows2012上的Server 2012 SP3企业版上。

我在sql日志中看到了这些错误：

失败分配页面: FAIL_PAGE_ALLOCATION 1 2016-06-1404:28:27.44 spid175错误: 701，严重性: 17，状态: 123。2016-06-1404:28:27.44 spid175资源库“默认”中的系统内存不足，无法运行此查询。2016-06-1404:28:27.44服务器错误: 17300，严重程度: 16，状态: 1. (Params:)此错误是以简洁模式打印的，因为在格式化过程中存在错误。将跳过跟踪、ETW、通知等。2016-06-1404:28:27.44服务器错误: 17300，严重程度: 16，状态: 1. (Params:)此错误是以简洁模式打印的，因为在格式化过程中存在错误。将跳过跟踪、ETW、通知等。2016-06-14 04:28:27.44 spid131错误: 701，严重程度: 17，状态: 123.

据我所知，它似乎在某一时刻耗尽了内存。

有没有办法找出是什么原因导致它失去记忆？

MEMORYCLERK_SQLQERESERVATIONS相当高，有人知道这是做什么的吗？

MEMORYCLERK_SQLQERESERVATIONS (node 0)           KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            22599824

编辑:我们在服务器上有32 28的RAM，28位被分配给Server。最大内存设置为28 8gb，最小服务器内存为8GB。

下面是指向ErrorLog输出的链接：ErrorLog

下面是指向sys.dm_os_process_memory输出的链接：查询输出

链接到等待类型：WaitTypes

我运行它的时候，我们似乎有更多的内存使用：

从sys.dm_exec_query_memory_grants选择* grant_time为null

结果：内存_赠款

选择mg.granted_memory_kb，mg.session_id，t.text，qp.query_plan从sys.dm_exec_query_memory_grants中选择mg交叉应用sys.dm_exec_sql_text(mg.sql_handle)作为t交叉应用sys.dm_exec_query_plan(mg.plan_handle)作为qp顺序按1 DESC选项(MAXDOP 1)进行选择。结果：QueryMemGrants选择前50位t.text，cp.objtype，qp.query_plan，cp.usecounts，cp.size_in_bytes >as 缓存中使用的字节从sys.dm_exec_cached_plans作为cp加入sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle交叉应用sys.dm_exec_query_plan(cp.plan_handle)作为qp交叉应用sys.dm_exec_sql_text(qs.sql_handle)作为t，其中qp.query_plan.exist(‘宣告命名空间>n="http://schemas.microsoft.com/sqlserver/2004/07/showplan“；>//n:MemoryFractions') =1 order by cp.size_in_bytes desc选项(MAXDOP 1)

结果：CachedMemGrants

从granted_query_memory结果中选择sys.dm_exec_requests，session_id，命令：糖尿病组_exec_请求

查询计划的XML来自正在运行的查询之一：QueryPlan

Answer 1

错误日志的输出有dbcc memorystatus转储，我注意到

Process/System Counts                         Value(in Bytes)
---------------------------------------- ----------
Available Physical Memory                1217605632---1.1 G
Available Virtual Memory                 140627167866880
Available Paging File                    5656502272
Working Set                               305238016
Percent of Committed Memory in WS                99
Page Faults                                27923310
System physical memory high                       0
System physical memory low                        0
Process physical memory low                       1--Memory Low
Process virtual memory low                        0
2016-06-14 04:28:27.41 Server    

请注意，可用的物理内存非常低。缓冲池中几乎没有内存。

关于占用更多内存的办事员

MEMORYCLERK_SQLQERESERVATIONS (node 0)           KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            22599824  --21.5 G

Page Life Expectancy                             64

现在，在最大服务器内存为28G的服务器上，如果MEMORYCLERK_SQLQERESERVATIONS占用21.5G，这肯定是个问题。这就是导致OOM条件的原因。

MEMORYCLERK_SQLQERESERVATIONS

是什么

这是Server中的内存事务员，它跟踪分配给查询的内存，其中涉及执行期间的排序或散列操作。这些操作符可以是查询的最大内存使用者。

为什么

导致OOM错误

当执行涉及排序和哈希操作的查询时，它将根据包含排序或散列运算符的原始查询计划发出预订请求。然后，当查询执行时，它将请求内存，Server将根据内存可用性部分或完全授予该请求。有一个名为“MEMORYCLERK_SQLQERESERVATIONS”的内存办事员(会计)，它跟踪对此类请求的内存分配。现在，在您的场景中，可能会发生以下情况

1. 查询请求的内存授权太多，因此SQL Server只能为其提供有限的内存，这一有限的数量称为“所需内存”，因此它在执行查询时开始执行和执行，因为内存需求很大，而且由于资源池中没有内存，因此查询失败，导致OOM错误。运行查询时所需的内存称为“附加内存”。
2. 修复了服务器2012 Sp1 CU4中的Bug，其中查询请求了大量内存授权，导致其速度急剧减慢，或随后由于OOM错误而失败。考虑到QEReservations占用了所有的缓冲池，不能排除错误再次出现的可能性。
3. 因为店员已经占用了90 %的内存。新查询所需的内存不可用，查询因OOM错误而失败。
4. 您的表和索引扭曲了统计信息，这迫使优化器构建次优计划，导致它请求的内存授权比实际需要的要多得多，进而造成问题。
5. 最后，运行在Server上的查询需要进行一些认真的调优。

根据这个Blogs.msdn文章

开发人员实际上可以对排序/哈希操作做些什么？说到重写查询，下面是一些可能导致大量内存授权的查询中需要查找的内容。查询使用排序操作符(并非全部包含列表)的原因: ORDER ( that )组BY (That) DISTINCT (That)合并连接操作符由优化器选择，合并连接的一个输入必须排序，因为该列上没有聚集索引。查询使用哈希匹配操作符(并非所有包含列表)的原因:联接(T)-如果SQL最终执行哈希连接。通常，缺少好的索引可能会导致最昂贵的连接操作符- Hash。查看查询计划。distinct ( the )--可以使用散列聚合来执行DISTINCT。查看查询计划。SUM/AVG/MAX/MIN (T)-任何聚合操作都可能作为散列执行。查看查询计划。UNION -可以使用散列聚合来删除重复项。

为了进一步理解这个问题，我要求您将以下查询的输出添加到您的问题中。我还希望您添加保罗·兰德尔等待状态查询的输出。查询的来源是这个博客，我建议您阅读这个博客。

SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

--Find who uses the most query memory grant:

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)

--Search cache for queries with memory grants:

SELECT t.text, cp.objtype,qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qp.query_plan.exist(‘declare namespace n=”http://schemas.microsoft.com/sqlserver/2004/07/showplan“; //n:MemoryFractions’) = 1

还有其他几件事，我想让你检查查询运行在系统上。

Select granted_query_memory,session_id,command from sys.dm_exec_requests

这将显示为系统上运行的查询分配了多少内存。

如果您可以看到MemoryGrant=xxxxx的实际执行计划，那么您可以为代价高昂的查询收集这个值。

所有这些都将告诉我们，在查询中是否存在问题，或者为什么请求这么多内存来执行这些问题。

编辑

来自您粘贴的各种查询输出。

您可以看到大量查询的requested_memory_kb大约是5G，这是大内存授权，理想情况下应该是很少MB的。请注意，required_memory_kb只有5MB左右，granted_query_memory为NULL。这是因为由于内存压力，Server只能提供启动查询的最小内存，但无法为查询执行提供额外内存，从而导致查询失败，从而导致OOM错误。

请求巨大内存的查询的查询成本也很高，这使我相信，要么统计数据扭曲了，要么查询写得不好。另一种可能是查询不被适当的索引所支持。请求这么大内存授权的查询数量是很好的。

对于上面的查询，请参阅granted_query_memory，它都是以GB表示的。前3个运行的查询使用了大约15G的内存，几乎占用了50 %的内存。在Server中，有数以百万计的进程运行，它们以某种方式需要内存，因此您可以看到是否有3个查询使用了50%的可用内存OOM问题。

溶液

您应该认真考虑在上面的屏幕截图中调优前4个查询。

确保运行索引重建和统计数据更新至少每周一次，这样倾斜的统计数据不会迫使优化器产生错误的计划。

使用资源调控器并创建资源池和工作负载组，并运行请求此池中的大量内存授权的查询。可以使用参数request_max_memory_grant_percentage限制内存请求。在这个博客中显示了一个例子。这只是一个备用的方法，直到您调优所有的查询。