Sql-server – Out of memory issues on SQL Server 2012 SP3

memorysql serversql-server-2012

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?

Best Answer

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.

Related Solutions

Sql-server – SQL Server 2005 SP3 Memory Errors when plenty of memory is seemingly available

The AWE mechanism in 32 bit process can only be used for data pages (buffer pool). It cannot be used for procedure cache, for query memory grants, for execution stacks, for access token cache, for CLR etc etc etc, basically all the other allocations other than data pages. All these allocations (including code pages) have to cram in the 2GB of the process address space.

Your only solution worth considering is moving to a 64bit SQL Server deployment on a 64bit OS. Everything else is a waste of time.

See Using AWE

The SQL Server buffer pool can fully utilize AWE mapped memory; however, only database pages can be dynamically mapped to and unmapped from SQL Server's virtual address space and take full advantage of memory allocated through AWE. AWE does not directly help supporting additional users, threads, databases, queries, and other objects that permanently reside in the virtual address space.

Sql-server – There is insufficient system memory in resource pool to run this query

From the output you posted I can see

MEMORYCLERK_SQLQERESERVATIONS       39874 (MB)

The value is 38G which is huge. Why is SQL Server operations(sortand hash) requesting so much memory

What is SQLRESERVATIONS

SQL Server memory allocated during query execution for Sort and Hash operations. Do you really think 38 G is what the sort and hash operations require. This is the problem.

Looking at your resource governor configuration

CREATE WORKLOAD GROUP [fm] WITH(GROUP_MAX_REQUESTS=0, 
IMPORTANCE=HIGH,
REQUEST_MAX_CPU_TIME_SEC=0, 
REQUEST_MAX_MEMORY_GRANT_PERCENT=100,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC=0, 
MAX_DOP=8) USING [fm]
GO

Just have a look at REQUEST_MAX_MEMORY_GRANT_PERCENT=100 now this seems a very wrong configuration to me. According to BOL document

REQUEST_MAX_MEMORY_GRANT_PERCENT = value Specifies the maximum amount of memory that a single request can take from the pool. This percentage is relative to the resource pool size specified by MAX_MEMORY_PERCENT

So this means when queries run on this workload they can request almost ALL memory as memory grant for the execution there by forcing others to starve for memory. And this is may definitely cause OOM error.

Further more what Microsoft says is

We do not recommend setting value greater than 70 because the server may be unable to set aside enough free memory if other concurrent queries are running. This may eventually lead to query time-out error 8645

Can you also share the output of

select  total_request_count,blocked_task_count,max_request_grant_memory_kb,requested
 from sys.dm_resource_governor_workload_groups


SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan 
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)
USE master 
GO

;WITH    cte
  AS ( SELECT   RP.pool_id ,
  RP.Name ,
  RP.min_memory_percent ,
  RP.max_memory_percent ,
  CAST (RP.max_memory_kb / 1024. / 1024. 
    AS NUMERIC(12, 2)) AS max_memory_gb ,
  CAST (RP.used_memory_kb / 1024. / 1024. 
    AS NUMERIC(12, 2)) AS used_memory_gb ,
  CAST (RP.target_memory_kb / 1024. / 1024. 
    AS NUMERIC(12,2)) AS target_memory_gb,
  CAST (SI.committed_target_kb / 1024. / 1024. 
    AS NUMERIC(12, 2)) AS committed_target_kb 
    FROM     sys.dm_resource_governor_resource_pools RP
    CROSS JOIN sys.dm_os_sys_info SI
  )
SELECT  c.pool_id ,
  c.Name ,
  c.min_memory_percent ,
  c.max_memory_percent ,
  c.max_memory_gb ,
  c.used_memory_gb ,
  c.target_memory_gb ,  
  CAST(c.committed_target_kb  *
  CASE WHEN c.committed_target_kb <= 8 THEN 0.7
    WHEN c.committed_target_kb < 16 THEN 0.75
    WHEN c.committed_target_kb < 32 THEN 0.8
    WHEN c.committed_target_kb <= 96 THEN 0.85
    WHEN c.committed_target_kb > 96 THEN 0.9
  END * c.max_memory_percent /100 AS NUMERIC(12,2))
   AS [Max_for_InMemory_Objects_gb]
FROM    cte c

EDIT:

From the output you posted

total_request_count  blocked_task_count max_request_grant_memory_kb
-------------------- ------------------ ---------------------------
553                  0                  18000
1633564              0                  19344744
0                    0                  0
89509                0                  23448232
2073                 0                  8032
24999                0                  4485384
861807               0                  3149248
236419               0                  32241240
293                  0                  32241240
1735195              0                  1889544

Now you can see there are so many requests which your workgroup handles and see the amount of memory they requested all ranges from 19G to 32 G. This can be a problem the query requesting huge memory grants can deprive others from memory and also note the memory for workgroup wont be shared.

You can also see

granted_memory_kb    session_id
-------------------- ----------
19344744             210

Session ID 210 was granted 19G of memory. This is blunder. just consider a scenario if 5-10 such queries start running.

Can you also add output of

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

Best Answer

Related Solutions

Sql-server – SQL Server 2005 SP3 Memory Errors when plenty of memory is seemingly available

Sql-server – There is insufficient system memory in resource pool to run this query

Related Question