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 Memory-Optimized Data tables for the given resource pool. This monitor is a dependency (rollup) monitor.
The monitor reports a critical state when the amount of memory used by the resource pool is greater than the Threshold setting, expressed as a percentage of memory available for Memory-Optimized Data tables for the given resource pool. This monitor is a dependency (rollup) monitor.
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 Memory-Optimized Data engine.
SQL Server Memory-Optimized Data uses more memory and in different ways than SQL Server does. It is possible that the amount of memory you installed and allocated for Memory-Optimized Data 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.
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 Memory-Optimized Data 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.
Name | Description | Default Value |
Enabled | Enables or disables the workflow. | Yes |
Generates Alerts | Defines whether the workflow generates an Alert. | No |
Target | Microsoft.SQLServer.2014.Database |
Parent Monitor | System.Health.PerformanceState |
Algorithm | WorstOf |
Source Monitor | Microsoft.SQLServer.2014.Monitor.UserResourcePool.MemoryConsumption |
Relationship | Microsoft.SQLServer.2014.RelationshipType.DatabaseReferencesUserResourcePool |
Category | PerformanceCollection |
Enabled | True |
Alert Generate | False |
Alert Auto Resolve | False |
Remotable | True |
Accessibility | Public |
<DependencyMonitor ID="Microsoft.SQLServer.2014.DependencyMonitor.Database.MemoryConsumptionRollUp" Accessibility="Public" Enabled="true" Target="SQL2014Core!Microsoft.SQLServer.2014.Database" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" RelationshipType="SQL2014Core!Microsoft.SQLServer.2014.RelationshipType.DatabaseReferencesUserResourcePool" MemberMonitor="Microsoft.SQLServer.2014.Monitor.UserResourcePool.MemoryConsumption">
<Category>PerformanceCollection</Category>
<Algorithm>WorstOf</Algorithm>
<MemberUnAvailable>Error</MemberUnAvailable>
</DependencyMonitor>