15

I'm using SQL Server 2014 - 12.0.2269.0 (X64) on Windows Server 2012 and I'm experiencing some memory issues. When I run a stored procedure that is doing a quite "heavy" calculation, I get an error after something like 10 minutes:

There is insufficient system memory in resource pool 'default' to run this query.

My SQL Server has multiple databases (like 15, but they are not always used at the same time). I looked to the SQL Server log file (after I got the error), and I saw a lot of lines like this:

2015-12-17 12:00:37.57 spid19s Disallowing page allocations for database 'Database_Name' due to insufficient memory in the resource pool 'default'. See 'http://go.microsoft.com/fwlink/?LinkId=330673' for more information.

A report is generated in the log with the memory used by each component (I think). If I correctly interpret the report, we can see that there's a lot of memory consumed by MEMORYCLERK_SQLBUFFERPOOL. You can find the report here: http://pastebin.com/kgmk9dPH

I also generated a report with a graph that shows the same "conclusion":

![memory usage

Here's maybe another useful reports:

enter image description here

enter image description here

Note that I've also seen this error in the log:

2015-12-17 12:04:52.37 spid70 Failed allocate page due to database memory pressure: FAIL_PAGE_ALLOCATION 8

Here's some information regarding the server's memory:

  • Total memory on the server: 16 Gb

  • Memory allocated to SQL server: 12288 Mb

  • Physical Memory In Use (from sys.dm_os_process_memory): 9287 Mb

If it can help, the server doesn't host any SharePoint database.

  • What is the max memory and total RAM on the server ? – Kin Dec 17 '15 at 16:11
7

While this probably isn't an exact answer to your problem I'll post it anyway hoping it may help you in a way.

What you are seeing is not the MEMORYCLERK_SQLBUFFERPOOL but the MEMORYCLERK_SQLLOGPOOL taking up all the memory.

There is a known issue with SQL 2012 SQL Server 2012 experiences out-of-memory errors. Even though you are running 2014 there is a chance that you are running into the same issue (even though I couldn't find a connect item for 2014).

There is also this known issue for both 2014 and 2012 involving index rebuilds and always on. You didn't specify you are using that but maybe the same bug has other triggers: FIX: SQL Cache Memory decreases and CPU usage increases when you rebuild an index for a table in SQL Server

So my best suggestion for now is to update to the latest CU and see if it still happens, since there have been a few fixes around MEMORYCLERK_SQLLOGPOOL recently. Since you are running RTM with 2 security fixes only that could make sense, there is an SP1 + multiple CU's available.

Since there is also fairly high memory usage in MEMORYCLERK_XTP used by in-memory OLTP this article may help you troubleshoot: Monitor and Troubleshoot Memory Usage

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.