Out of memory issues on SQL Server 2012 SP3

Question

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 <X64> (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_space_kb/1024 )Total_VAS_in_MB,
         process_physical_memory_low,
         process_virtual_memory_low
    from sys. dm_os_process_memory
  
  Output:
  
  Memory_usedby_Sqlserver_MB | Locked_pages_used_Sqlserver_MB | Total_VAS_in_MB | process_physical_memory_low | process_virtual_memory_low
  ----------------------------------------------------------------------------------------------------------------------------------------
                       20553 |                          20393 |       134217727 |                           0 |                          0
  

Server is on SP3, I know there was a memory leak issue in SP1, so ruling that out.

Anyone notice anything in DBCC result that I should focus on?

Answer 1

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:

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

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

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

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

5. Click OK.

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

1. Ensure the VM is turned off.
2. Right-Click the VM, choose "Settings"
3. Select "Memory" from the left-hand pane.
4. Ensure "Enable Dynamic Memory" is not checked.
5. Start the VM.

Answer 2

Couple of configuration changes I would make:

1. Remove the minimum memory settings, there is literally no need for this.
   • The reason you want to change this is because if this is set, SQL Server will never release the memory below the mark. Should pages become locked in memory, the memory can no longer be paged.
2. Adjust the maximum sever memory to 75% of the total for total < 32gb or 87.5% for total > 32gb.
   • Assuming SQL Server is the only thing running on the machine, let it consume whatever memory is left after granting the OS sufficient memory to operate properly.

You can use the following script to accomplish this:

use master;
go

-- MIN Server Memory
exec sys.sp_configure
    'min server memory (MB)'
    ,0;

-- MAX Server Memory
declare
  @systemMemory int
  ,@maxServerMemory int;

select
  @systemMemory = total_physical_memory_kb / 1024
from
  sys.dm_os_sys_memory;

set @maxServerMemory = floor(@systemMemory * .75);

if @systemMemory >= 32768
  begin
    set @maxServerMemory = floor(@systemMemory * .875);
  end;

exec sys.sp_configure
  'max server memory (MB)'
  ,@maxServerMemory;

reconfigure;
go