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":
Here's maybe another useful reports:
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.
Best Answer
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 theMEMORYCLERK_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 ServerSo 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 byin-memory OLTP
this article may help you troubleshoot: Monitor and Troubleshoot Memory Usage