Out of memory issues on SQL Server 2012 SP3

I've been having periodic SQL Server OOM errors, one time to the point that SQL Server shut down itself and always happened during night time, when no one's using it, and no SQL Agent job running at that time:

Here is the typical error:


08/17/2017 19:31:17,spid100,Unknown,There is insufficient system memory in resource pool 'internal' to run this query.


08/17/2017 19:31:17,spid100,Unknown,Error: 701 Severity: 17 State: 123.


08/17/2017 19:31:17,spid112,Unknown,Error: 18056 Severity: 20 State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing ETW notifications etc are skipped.

Here is the server info:

• 10GB MIN SQL server memory

• 21GB MAX SQL server memory

• only 4 DBs on the server

• their sizes are only 1 to 2 GB each

• Tempdb size never grew to more than 1GB (set to auto grow to 10GB)

• Indexes are all low frag, stats updated

-
Version:

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) 
    Oct 20 2015 15:36:27 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

I checked multiple items:

• DBCC memorystatus

-
Quick stats:

SELECT (physical_memory_in_use_kb/1024)/1024 AS [PhysicalMemInUseGB]
FROM sys.dm_os_process_memory;
GO

Output:
20 GB
Page Life Expectancy                     155932

-
No resource governor enabled:

```
select pool_id, cache_memory_kb, used_memory_kb,
out_of_memory_count,used_memgrant_kb
from sys.dm_resource_governor_resource_pools

Output:

pool_id | cache_memory_kb | used_memory_kb | out_of_memory_count | used_memgrant_kb
--------+-----------------+----------------+---------------------+-----------------
1 | 295368 | 641416 | 0 | 0

select (physical_memory_in_use_kb/1024) Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024) Locked_pages_used_Sqlserver_MB,
(total_virtual_address_

Since the SQL Server is running in a VM, as indicated by the (hypervisor) indicator in the version statement, you should ensure it has a memory reservation in VMWare (or Hyper-V, etc) on the host server.

The memory reservation should typically be 100% of the memory allocated to the VM for SQL Server virtual machines that are used in a production environment. Without a memory reservation, the host server may "steal" memory from the virtual machine via use of a "balloon driver" for use by some other VM, which is likely the cause of your out-of-memory condition.

In VMWare vCenter, to set the Memory Reservation on a Virtual Machine:

-
Power off the virtual machine before configuring the memory settings.

-
In the vSphere Client, right-click a virtual machine from the inventory and select Edit Settings.

-
In the Virtual Machine Properties window, select the Resources tab and select Memory.

-
In the Resource Allocation panel, select the Reserve all guest memory (All locked) check box.

-
Click OK.

If you're using Microsoft Hyper-V, disable dynamic memory for the VM, using this process in Hyper-V Manager:

• Ensure the VM is turned off.

• Right-Click the VM, choose "Settings"

• Select "Memory" from the left-hand pane.

• Ensure "Enable Dynamic Memory" is not checked.

• Start the VM.

StackExchange Database Administrators Q#183764, answer score: 2