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

Today in my environment i got this error and realized that this happened due to memory pressure. To confirm this whether is this really happened due to memory or not  i have ran the below queries.


Symtoms for Memory pressure:
If you run the below command and if it shows last waittype as RESOURCE_SEMAPHORE then it meas it is clearly memory issue

1)SELECT * FROM SYSPROCESSES
WHERE lastwaittype LIKE '%RESOURCE%'

2) Another query you run the below one. Which shows long running queries in SQL Server along with memory ,CPU. And at this time there is also chance that CPU also is hike at that moment. The below query will also provide you query and its execution plan. Which intern also give you the recommend indexes

SELECT session_id,DB_NAME(DATABASE_ID),
text,
query_plan
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle) ;

3) Another way to look at his in the 'perfmon' counter. Which is performance counter that is
SQLServer:Memory Manager displays a non-zero value for Memory Grants Pending. This means if this value is greater than zero(>0) there is a memory pressure here.

4)When new users try to connect to SQL Server, they receive a "login failed" error message.
5)Users are disconnected and they receive various error messages.
6)CPU usage is very high on the server.
7)‎ xp_readerrorlog 0,1,N'waiting for memory resources'

Workaround:
Need to create missing indexes on the table whatever mentioned in the execution plan of query.

Also please go through the MSDN  below link to get more details
https://support.microsoft.com/en-us/help/309256/how-to-troubleshoot-sql-server-error-8645
-->

And i am providing some more inputs related to blocking here after we finding blocking.

SELECT open_tran,* FROM sys.sysprocesses
where blocked<>0--Here you will get the blocking id.
GO
select * from sys.dm_exec_requests
where session_id=214--This is a blocking id here
go
SELECT   session_id,DB_NAME(DATABASE_ID),
text,
query_plan,*
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
where session_id=214--Pass teh above blocking id here in this query
order by wait_time DESC

The another thing that we need to look at is “SYS.DM_OS_RING_BUFFERS”.It gives us 4 columns and the main column that we need to focus is ‘RECORD’ column.  There are number of different ring buffers but we are only interested in the type called “RING_BUFFER_RESOURCE_MONITOR”. This record changes to memory allocation , which is a great way to see when you are running out of memory pressure because a message that your memory is low is a pretty good indicator that might be seeing memory pressure

select * from sys.dm_os_ring_buffers

where record LIKE '%RING_BUFFER_RESOURCE_MONITOR%'

select * from sys.dm_os_ring_buffers

where record LIKE '%RESOURCE_MEMPHYSICAL_LOW%'

The information presented in the MemoryNode and the MemoryRecord are useful for attempting to figure out what went wrong, but the key points are up in the ResourceMonitor element with the Notification, IndicatorsProcess, and IndicatorsSystem values. First, the notification tells us that this was RESOURCE_MEMPHYSICAL_LOW message that was captured in the ring buffers. That means that physical memory was low. The next two indicators let us know what was low. If the IndicatorsProcess returns 0 and the IndicatorsSystem returns a value then the problem was system wide. But, in our case the IndicatorsProcess has a value and IndicatorsSystem is returning 0. This means that this alert was for a single process that ran suffered from low memory, not the entire system. The values break down as follows:

Value	Meaning
1	High Physical Memory
2	Low Physical Memory
4	Low Virtual Memory

To understand this in a in detailed manner look at the below link

https://blogs.msdn.microsoft.com/mvpawardprogram/2012/06/04/using-sys-dm_os_ring_buffers-to-diagnose-memory-issues-in-sql-server/

Isolating Memory Used by SQL Server

By default, SQL Server changes its memory requirements dynamically, on the basis of available system resources. If SQL Server needs more memory, it queries the operating system to determine whether free physical memory is available and uses the available memory. If SQL Server does not need the memory currently allocated to it, it releases the memory to the operating system. However, you can override the option to dynamically use memory by using the minservermemory, and maxservermemory server configuration options. For more information, see Server Memory Options.

To monitor the amount of memory that SQL Server uses, examine the following performance counters:

·         Process: Working Set

·         SQL Server: Buffer Manager: Buffer Cache Hit Ratio

·         SQL Server: Buffer Manager: Database Pages

·         SQL Server: Memory Manager: Total Server Memory (KB)

The WorkingSet counter shows the amount of memory that is used by a process. If this number is consistently below the amount of memory that is set by the min server memory and max server memory server options, SQL Server is configured to use too much memory.

The Buffer Cache Hit Ratio counter is specific to an application. However, a rate of 90 percent or higher is desirable. Add more memory until the value is consistently greater than 90 percent. A value greater than 90 percent indicates that more than 90 percent of all requests for data were satisfied from the data cache.

If the TotalServerMemory (KB) counter is consistently high compared to the amount of physical memory in the computer, it may indicate that more memory is required.

Determining Current Memory Allocation

The following query returns information about currently allocated memory.

Copy

SELECT 

(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB, 

(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB, 

(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB, 

process_physical_memory_low, 

process_virtual_memory_low 

FROM sys.dm_os_process_memory; 

We can also use below query for memory allocation information.

SELECT --serverInstanceId,
       --     CURRENT_TIMESTAMP collectionTime
       --,    resource_semaphore_id      --0
=regular queries, 1 = small queries
              CAST(CASE WHEN target_memory_kb > 0 THEN target_memory_kb/1048576. ELSE 0 END AS NUMERIC(10,3)) targetMemoryGB            --Grant usage target in kilobytes.
       --,    max_target_memory_kb --Maximum
potential target in kilobytes. NULL for the small-query resource semaphore.
       ,      CAST(CASE WHEN total_memory_kb > 0 THEN total_memory_kb/1048576. ELSE 0 END AS NUMERIC(10,3)) totalMemoryGB                    --Memory held by the resource
semaphore in kilobytes.
       ,      CAST(CASE WHEN available_memory_kb > 0 THEN available_memory_kb/1048576. ELSE 0 END AS NUMERIC(10,3)) availableMemoryGB         --Memory available for a new grant in kilobytes.
       ,      CAST(CASE WHEN granted_memory_kb > 0 THEN granted_memory_kb/1048576. ELSE 0 END AS NUMERIC(10,3)) grantedMemoryGB           --Total granted memory in kilobytes.
       --,    used_memory_kb                    --Physically
used part of granted memory in kilobytes.
       ,      grantee_count granteeCount               --Number of active queries
that have their grants satisfied.
       ,      waiter_count waiterCount                 --Number of queries waiting
for grants to be satisfied.
       ,      timeout_error_count timeoutErrorCount    --Total
number of time-out errors since server startup. NULL for the small-query
resource semaphore.
       ,      forced_grant_count forcedGrantCount             --Total
number of forced minimum-memory grants since server startup. NULL for the
small-query resource semaphore.
FROM sys.dm_exec_query_resource_semaphores
WHERE pool_id = 2 --default pool
AND resource_semaphore_id = 0

With VASummary(Size,Reserved,Free) AS

(SELECT

With VASummary(Size,Reserved,Free) AS
(SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size, region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size)
SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB]
,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0

The below link is useful to understand memory consumption from databsae wise. https://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/