Resource Pool Memory Consumption

Microsoft.SQLServer.2014.Monitor.UserResourcePool.MemoryConsumption (UnitMonitor)

The monitor reports a critical state and raises an alert when the amount of memory used by the resource pool is greater than the Threshold setting, expressed as a percentage of memory available for In-Memory tables for the given resource pool.

Knowledge Base article:

Summary

The named resource pool when mapped to memory-optimized database tracks memory allocated to all memory-optimized tables, including internal tables that are used for various structures within In-Memory OLTP engine.

Causes

SQL Server In-Memory OLTP uses more memory and in different ways than SQL Server does. It is possible that the amount of memory you installed and allocated for In-Memory OLTP becomes inadequate for your growing needs. If so, you could run out of memory.

Please refer to this article for more details: Bind a Database with Memory-Optimized Tables to a Resource Pool.

Resolutions

To resolve your Low Memory or Out Of Memory condition you need to either free up existing memory by reducing usage, or make more memory available to your in-memory tables. Possible corrective actions may include:

Check if there are long running transaction that is preventing garbage collection of memory. If yes, consider killing the long running transaction and/or look into the design of the application to see if you can reduce the duration of the transactions.
Free up existing memory.
Delete non-essential memory optimized table rows and wait for garbage collection.
Move one or more rows to a disk-based table.
Increase value of MAX_MEMORY_PERCENT on the resource pool.
Increase memory available to SQL Server Instance by configuring the Max Server Memory to a higher value.
Install additional memory.

External

How to Manage your Memory for In-Memory OLTP

Overrideable Parameters

Name	Description	Default Value
Enabled		Yes
Generates Alerts		Yes
Interval (seconds)	The recurring interval of time in seconds in which to run the workflow.	900
Script Delay (milliseconds)	This parameter sets the delay between consecutive T-SQL queries executed by the workflow. This may help to reduce the footprint generated by the workflow in case of large number of target objects. Please advise with Microsoft Support before changing this parameter.	0
Synchronization Time	The synchronization time specified by using a 24-hour format. May be omitted.
Threshold	The collected value will be compared against this parameter.	90
Timeout (seconds)	Specifies the time the workflow is allowed to run before being closed and marked as failed.	300

Element properties:

Target

Microsoft.SQLServer.2014.UserResourcePool

Parent Monitor

System.Health.PerformanceState

Category

PerformanceHealth

Enabled

True

Alert Generate

True

Alert Severity

Error

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.2014.MonitorType.UserResourcePool.MemoryConsumption

Remotable

True

Accessibility

Public

Alert Message

MSSQL 2014: In-Memory OLTP: Resource Pool is using too much memory.

Resource Pool "{0}" is using too much memory.
Total physical memory on the server: {6} MB
Target memory for SQL Instance: {1} MB
Pool memory: {2} MB
Available for In-Memory tables: {3} MB ({4} \%)
Currently used: {5} MB

RunAs

Default

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2014.Monitor.UserResourcePool.MemoryConsumption" Target="SQL2014Core!Microsoft.SQLServer.2014.UserResourcePool" ParentMonitorID="SystemHealth!System.Health.PerformanceState" TypeID="Microsoft.SQLServer.2014.MonitorType.UserResourcePool.MemoryConsumption" Accessibility="Public" Enabled="true" Remotable="true" Priority="Normal" ConfirmDelivery="true">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2014.Monitor.UserResourcePool.MemoryConsumption.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.ResourcePool"]/Name$</AlertParameter1>

      <AlertParameter2>$Data/Context/Property[@Name='MemoryForSQLInstanceMB']$</AlertParameter2>

      <AlertParameter3>$Data/Context/Property[@Name='PoolAvailableMemoryMB']$</AlertParameter3>

      <AlertParameter4>$Data/Context/Property[@Name='PoolFreeMemoryMB']$</AlertParameter4>

      <AlertParameter5>$Data/Context/Property[@Name='PoolFreeMemoryPercent']$</AlertParameter5>

      <AlertParameter6>$Data/Context/Property[@Name='PoolUsedMemoryMB']$</AlertParameter6>

      <AlertParameter7>$Data/Context/Property[@Name='TotalMemoryOnTheServerMB']$</AlertParameter7>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="ErrorState" MonitorTypeStateID="ErrorState" HealthState="Error"/>

    <OperationalState ID="SuccessState" MonitorTypeStateID="SuccessState" HealthState="Success"/>

  </OperationalStates>

  <Configuration>

    <IntervalSeconds>900</IntervalSeconds>

    <SyncTime/>

    <ServerName>$Target/Host/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ServerName>

    <SqlInstanceName>$Target/Host/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.ServerRole"]/InstanceName$</SqlInstanceName>

    <Value>Property[@Name='PoolUsedMemoryPercent']</Value>

    <PoolID>$Target/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.ResourcePool"]/PoolID$</PoolID>

    <Threshold>90</Threshold>

    <TimeoutSeconds>300</TimeoutSeconds>

    <ScriptDelayMsec>0</ScriptDelayMsec>

  </Configuration>

</UnitMonitor>