Failed allocate pages: FAIL_PAGE_ALLOCATION 1

We are on SQL Server 2012 SP3 Enterprise Edition on Windows 2012 R2.

I saw these errors in the sql logs:


Failed allocate pages: FAIL_PAGE_ALLOCATION 1


2016-06-14 04:28:27.44 spid175 Error: 701, Severity: 17, State:
123.


2016-06-14 04:28:27.44 spid175 There is insufficient system memory
in resource pool 'default' to run this query.


2016-06-14 04:28:27.44 Server Error: 17300, Severity: 16, State:
1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.


2016-06-14 04:28:27.44 Server Error: 17300, Severity: 16, State:
1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.


2016-06-14 04:28:27.44 spid131 Error: 701, Severity: 17, State:
123.

As far as I can tell, it looks like it ran out of memory at some point.

Is there a way to figure out what caused it to go out of memory?

The MEMORYCLERK_SQLQERESERVATIONS is pretty high, does anyone know what that is for?

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

EDIT: We have 32 gigs of RAM on the server and 28 is allocated to the SQL Server. The max memory setting is 28gb and min server memory is 8gb.

Here is a link to the ErrorLog output:
ErrorLog

Here is the link to the sys.dm_os_process_memory output:
Query Output

Link to the wait types: WaitTypes

I ran it during a time when we seem to have more memory usage:


SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

Results: memory_grants


SELECT

The output of errorlog had dbcc memorystatus dump and what I noticed was

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

Please note the available physical memory is very low. There was almost no memory in buffer pool

Regarding clerk which is consuming more memory

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

Now on server where max server memory is 28 G if MEMORYCLERK_SQLQERESERVATIONS is taking 21.5 G that is definitely a problem. This is what causing the OOM condition.

What is MEMORYCLERK_SQLQERESERVATIONS

This is a memory clerk in SQL Server which tracks memory allocated to query which involves Sort or hash operations during execution. These operators can be the largest memory consumers for a query.

Why OOM error due to this

When query involving sort and hash operations is executed it will make a reservation request based on the original query plan which contained a sort or a hash operator. Then as the query executes, it requests the memory and SQL Server will grant that request partially or fully depending on memory availability. There is a memory clerk (accountant) named ‘MEMORYCLERK_SQLQERESERVATIONS’ which tracks memory allocation to such requests . Now in your scenario following could be happening

-
Query is requesting so much memory grant that SQL Server is only able to provide it a limited amount, this limited amount is called "Required Memory", so that it starts executing and while executing the query, because memory requirement was large and SQL Server cannot provide it as there was no memory in resource pool, the query fails with OOM error. The memory required when query is running is called "Additional Memory"

-
There was Bug fixed in SQL Server 2012 Sp1 CU4 where query requested huge amount of memory grant causing it to be drastically slow or subsequently failing with OOM error. The possibility that bug resurfaced cannot be ruled out considering fact that QEReservations hogged all of the buffer pool

-
Since the clerk has already taken 90 % of memory. Required Memory for new query is not available and query fails with OOM error.

-
Your tables and indexes has skewed statistics which is forcing optimizer to build sub optimal plan causing it to request much more memory grant than actually required and in turn creating issues.

-
Lastly the queries running on SQL Server requires some serious tuning.

As per This Blogs.msdn article


What Can a Developer Actually Do about Sort/Hash Operations?


Speaking of re-writing queries, here are some things to look for in a
query that may lead to large memory grants.


Reasons why a query would use a SORT operator (not all inclusive
list):

ORDER BY (T-SQL)

GROUP BY (T-SQL)

DISTINCT (T-SQL)

Merge Join operator selected by the optimizer and one of the inputs of the Merge join has to be sorted because a clustered index is

not available on that column.


Reasons why a query would use a Hash Match operator (not all inclusive
list):

JOIN (T-SQL) – if SQL ends up performing a Hash Join. Typically, lack of good indexes may lead to the most expensive of join operators

– Hash Join. Look at query plan.

DISTINCT (T-SQL) – a Hash Aggregate could be used to perform the distinct. Look at query plan.

SUM/AVG/MAX/MIN (T-SQL)– any aggregate operation could potentially be performed as a Hash Aggregate . Look at query plan.

UNION – a Hash Aggregate could be used to remove the duplicates.

To further understand the problem I would require you to add output of below queries into your question. I would also like you to add output of Paul Randal Wait stats query. The source of query is This Blog, I suggest you to read the blog.

```
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.d

StackExchange Database Administrators Q#141236, answer score: 5