question

pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 asked ErlandSommarskog commented

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

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:
24858-image.png


Buffer Cache Hit Ratio is 100

24915-image.png


CPU Pressure from sys.dm_os_schedulers:
24943-image.png



sql-server-general
image.png (5.6 KiB)
image.png (4.7 KiB)
image.png (4.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered

Seems you have memory pressure outside your sql server. What else is running on the machine at that time?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 answered pdsqsql-8017 edited

Thanks Tibor.
Mostly Sql, WMI provider host and 3rd party tool application
My Total Server Memory and Target Server Memory are close so no Memory Pressure, right?

24954-image.png



image.png (9.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Seems more like internal memory pressure to me. 8 GB in total is not a lot...

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 answered

Thanks Erland.
Agreed but how can i troubleshoot that it's a internal memory pressure?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

The problem is that even if you can find where the memory is spent, it can be difficult to deal with it. Well, you can start to flush various caches, but that is not that much than restarting the machine. And if you do that, you could add more RAM while you are it.

But you could look at sys.dm_os_memory_clerks to see where the memory is spent.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Shashank-Singh avatar image
0 Votes"
Shashank-Singh answered

@pdsqsql-8017 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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered MiaMiao-MSFT edited

Hi @pdsqsql-8017,


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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered

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...

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered ErlandSommarskog commented

Hi @pdsqsql-8017,

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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI Mia
What if the result of #4 is not empty, what does that mean?

Alex

0 Votes 0 ·

I think that when Mia said:

So for a SQL Server without memory pressure, this view should be empty.

She was not actually correct.

Some operators in an execution need work memory for their operation. Typical examples are Sort and Hash operators. The memory is not allocated dynamically when the operation is running. Instead the optimizer makes an estimate of how much memory that is needed and this fixed amount is needed. This is known as a memory grant. (Would the operation turn out to require more memory than what was granted, it will spill to disk.)

So the fact that there are rows in this view, sys.dm_exec_query_memory_grants, only means that there are queries currently running that are performing sorting or hashing. That alone does not indicate memory pressure.

But if there are entries where grant_time is NULL, this is an indication of memory pressure, because that means that the query still have not been granted the requested memory.

0 Votes 0 ·