There is insufficient memory in resource pool 'internal to run this query . SQL 2012 SP4 build 11.0.7462.6

Question

Hello,

We are getting "There is insufficient memory in resource pool 'internal to run this query" error in SQL Server 2012 SP4 build 11.0.7462.6.
This happens whenever we run application jobs that downloads PDF files and process them. Occasionally those jobs fail with due to the above error.

Maximum memory on SQL server has been increased to 28GB from 12 GB, still we are seeing job failures due to above memory related error.
Any insight would be really helpful.

Thank you

Answer 1

This error generally is due to a problem when SQL Server cannot allocate RAM fast enough.

Are you getting this error all the time doing a certain activity? Have you looked at your VM memory? Are you running a very large query?

Answer 2

what do you mean by " we run application jobs that downloads PDF files and process them". is this an executable that processes pdf, then sends the data to SQL? if so, why does it not run on a different machine, then send to SQL Server?
I would seriously hope you are not using a CLR to process PDF's. CLRs in general can be very dangerous

I suggest running this query
SELECT b.virtual_address_space_reserved_kb vas_res, b.virtual_address_space_committed_kb vas_com
, b.physical_memory_in_use_kb phy_mem
FROM sys.dm_os_process_memory b

Answer 3

something went horribly wrong in your system.
Suppose you just use the core features of SQL Server, by which I mean anything that allocates only from the common buffer pool. In this case, vas reserved, committed and physical memory should all be about the same, assuming a warmed up buffer cache.

now suppose you use features that require direct OS virtual address space allocation, bypassing the SQL buffer cache. In the ideal case, you declare an object, example: xp call to open an XML document (old API, not the new API), do something, then close and release the document. Overtime, VAS reserved will increase, but VAS committed will be about compared to physical memory in use.
however, if VAS committed rises significantly above phys mem, then that means the OS must page out to the page file, which is a really bad thing on modern systems.
Realistically, the situation of requiring vastly more VAS committed is not because you need that much in the committed VAS, but rather because someone forgot to close and deallocate something they were done with.

Answer 4

in "normal" circumstance using only core features of SQL Server that allocates from the Buffer Pool and no direct OS allocations, Committed memory should be about equal to process physical memory.
In older versions of SQL Server, setting max server memory only constrained the buffer pool and not direct OS allocations.
In more recent versions, target memory is now the combined buffer + direct?
Hence, if something is making direct OS allocations, and not releasing it when done, SQL Server will start releasing buffer pool allocations to bring total memory to inside the limit set for target.
If too much direct allocations are made, then too little is left for the buffer pool

It would seem that whatever makes direct OS allocations is unconstrained, hence committed grows larger than target and stays larger.
However, something that works within the buffer pool now runs into a situation where it generates the error message, because very little allocation can be made from the buffer pool.
Perhaps a necessary feature for SQL Server, now that Max Server memory is combined buffer pool + direct, then there also needs to be a setting for minimum buffer pool (not min. server memory) such that direct allocations can only push out buffer pool to a set limit

Answer 5

What is the value of "Maximum server memory" in the SQL Server property? If you have a fixed number (NOT 2147483647), you have to adjust it after you increased memory.