A timeout occurred while waiting for memory resources to execute the query in resource pool 'internal' (1). Rerun the query.

Question

Hello,
We are receiving following error in our Sql server which hosted for 3rd party monitoring application wiht 2 databases running.

A timeout occurred while waiting for memory resources to execute the query in resource pool 'internal' (1). Rerun the query.

We have Sql Server 2014 SP3-CU4 , CPU 4, Total Memory: 8 GB and MAX MEMORY 6 GB.
I don't want to restart the server and fix the issue but need to troubleshoot to fix it.

I checked following:

Buffer Cache Hit Ratio is 100

CPU Pressure from sys.dm_os_schedulers:

Answer 1

@pdsqsql Can you post the post the output of select @@version its strange with 6 GB memory assigned . The memory_usedby_sqlserver_mb col is just showing 253 MB.

I would also like to see output of sp_readerrorlog I have a hunch there could be paging happening. Something like a significant part of SQL Server memory has been paged out

Answer 2

Hi @pdsqsql ,

Please reference: mssqlserver-8645-database-engine-error

Solution:
1.Use a separate server to run sql server service;
2.Increase RAM and max server memory;
3.Run the following DBCC command to forcibly release part of the SQL Server memory cache:
DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

If you want to further investigate the cause of memory pressure, steps as next:
1.When you see these error messages, observe the memory applications of each Memory Clerk in sys.dm_os_memory_clerks and their changes;

2.Try to find out the sentences that use more memory, simplify them, or adjust the behavior of the application to reduce the workload.

-- Sorted by the number of pages physically read, the top 20.  
SELECT TOP 20  
qs.total_physical_reads,qs.execution_count,  
 qs.total_physical_reads /qs.execution_count as [Avg IO],  
 SUBSTRING(qt.text,qs.statement_start_offset/2,  
(case when qs.statement_end_offset = -1  
then len(convert(nvarchar(max), qt.text)) * 2  
else qs.statement_end_offset end -qs.statement_start_offset)/2)  
as query_text,  
qt.dbid, dbname=db_name(qt.dbid),  
qt.objectid,  
qs.sql_handle,  
qs.plan_handle  
FROM sys.dm_exec_query_stats qs  
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt  
ORDER BY qs.total_physical_reads desc  

--Sort by the number of logically read pages, the top 20.   
SELECT TOP 20  
qs.total_logical_reads,qs.execution_count,  
 qs.total_logical_reads /qs.execution_count as [Avg IO],  
 SUBSTRING(qt.text,qs.statement_start_offset/2,  
(case when qs.statement_end_offset = -1  
then len(convert(nvarchar(max), qt.text)) * 2  
else qs.statement_end_offset end -qs.statement_start_offset)/2)  
as query_text,  
qt.dbid, dbname=db_name(qt.dbid),  
qt.objectid,  
qs.sql_handle,  
qs.plan_handle  
FROM sys.dm_exec_query_stats qs  
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt  
ORDER BY qs.total_logical_reads desc  

3.Check the dynamic management view sys.dm_exec_query_resource_semaphores to understand the status information of each query resource semaphore. (There are two query resource semaphores by default in SQL Server to handle queries with different complexity. This design helps prevent several large queries from exhausting the entire SQL Server resources, even some very simple queries Unable to respond occurs.)

SELECT CONVERT (varchar(30), getdate(), 121) as runtime,  
resource_semaphore_id,target_memory_kb,total_memory_kb,available_memory_kb,  
granted_memory_kb,used_memory_kb,grantee_count,waiter_count,timeout_error_count  
from sys.dm_exec_query_resource_semaphores  

4.Check the dynamic management view sys.dm_exec_query_memory_grants, and return information about queries that have been granted memory or are still waiting to be executed. Queries that are granted memory grants without waiting will not appear in this view. So for a SQL Server without memory pressure, this view should be empty.

SELECT getdate() as runtime ,  
session_id,scheduler_id,DOP,request_time,grant_time,  
requested_memory_kb,granted_memory_kb,used_memory_kb,  
timeout_sec,query_cost,timeout_sec,resource_semaphore_id,  
wait_order,is_next_candidate, wait_time_ms,  
REPLACE (REPLACE (cast(s2.text as varchar(4000)), CHAR(10), ' '), CHAR(13), ' ') AS  
sql_statement  
from sys.dm_exec_query_memory_grants  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

If you read the original post in this thread, you see that process_physical_memory_low has 1. This lead me to believe that it is not internal memory pressure, but rather external memory pressure. My guess is that you have the VM balloon driver pumping up its memory usage to "reclaim memory" in the machine, and SQL Server being a good citizen seeing memory pressure in the machine lowers its memory usage to a ridiculously low value.

You would have to monitor the memory usage outside SQL Server to capture what is using all this memory (since it clearly isn't SQL Server). Or just talk to the VM people and have them not pumping up a dummy process using memory causing all this...

Answer 4

Hi @pdsqsql ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.