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.