8

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?

  • 1
    Are there any other services/applications hosted on this server? Is the SQL Server Service account part of the Locked Pages in Memory Local Security Policy? Do the Windows Event Logs indicate any other issues around the time you experienced the SQL Server crash? – John Eisbrener Aug 17 '17 at 19:18
  • -No other application hosted on this server, it's a dedicated SQL server. -SQL server service account is part of windows admins group. - I just see a bunch of VSS errors: Volume Shadow Copy Service error: Unexpected error querying for the IVssWriterCallback interface. hr = 0x80070005, Access is denied. . This is often caused by incorrect security settings in either the writer or requestor process. Operation: Gathering Writer Data Context: Writer Class Id: {35e81631-13e1-48db-97fc-d5bc721bb18a} Writer Name: NPS VSS Writer Writer Instance ID: {...} – user132852 Aug 17 '17 at 19:23
  • 1
    Is the server a VM? If so, does it have "dedicated" memory resources or does it share with other VMs? And, if it shares memory, is the distribution of the memory resources overcommitted? I've seen this happen (regardless of version) on VM servers where memory was over-allocated. – SQL_Hacker Aug 17 '17 at 20:20
  • 3
    Thank you Shanky. Just to update, I found the issue last Friday after adding it to monitoring, it appears one of the app monitoring sessions in the application( third party) wasn't configured properly, the connection session doesn't close until hours later when the OOM kills it. – user132852 Aug 22 '17 at 21:49
  • 1
    @user132852 you could put your solution in an answer and mark it answered. – Thomas Kronawitter Apr 11 '18 at 5:43
2

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

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
  • 3
    I know that these are pretty standard best practices, but can you explain a little how changing them will help with the error they're hitting? – Erik Darling Jun 9 '18 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.