DB Memory-Optimized Data Filegroup Container Space (rollup)

Microsoft.SQLServer.2016.DependencyMonitor.DBFileGroupFx.ContainerFreeSpacePercentRollUp (DependencyMonitor)

The monitor reports a warning when the available disk space for all Memory-Optimized Data Filegroup Containers drops below the Warning Threshold setting, expressed as percentage of the sum of the Memory-Optimized Data Filegroup Container size plus disk free space. The monitor reports a critical state when the free space drops below the Critical Threshold. This monitor is a dependency (rollup) monitor.

Knowledge Base article:

Summary

Note: This monitor is a dependency (rollup) monitor. The health state for this monitor is determined by the best state of any member monitor. Please also review the knowledge base article for related unit monitors.

The monitor reports a warning when the available disk space for all Memory-Optimized Data Filegroup Containers drops below the Warning Threshold setting, expressed as percentage of the sum of the Memory-Optimized Data Filegroup Container size plus disk free space. The monitor reports a critical state when the free space drops below the Critical Threshold.

The Memory-Optimized Data Filegroup Container folder typically grows over time as more data is added into Memory-Optimized Data tables. As such, administrators will wish to monitor to ensure there is sufficient space remaining in the storage location for the container folder.

Causes

When the available disk space on the drive where the database storage location is configured becomes too low, failures can occur, preventing successful processing of data, delaying user’s queries, or affecting other applications, which share the same allocation of disk space.

The storage is allocated as part of DML operations on the durable memory-optimized tables. You need to configure storage up to 4x of the Memory-Optimized data size of the durable memory-optimized tables. If you have configured the storage correctly but still running into low free storage space, it is possible IOPS supported by the containers are not able to support the demand of the workload. General recommendation for containers is to support 3x IOPS of the data generation rate to account for initial population of the data/delta files and for automatic merge operation that needs to read the source data/delta files and then merge them into a target CFP.

Resolutions

The resolution(s) for low available disk space depend upon the root causes, which must be investigated in each case when this issue occurs. The condition may be alleviated by any of many potential approaches:

Decrease the amount of data being stored in Memory-Optimized Data tables.
Remove data consuming disk space on the same drive with the Memory-Optimized Data Filegroup Container folder.
Add another container to the Memory-Optimized Data Filegroup.
Increase the size of the logical drive on which the Memory-Optimized Data Filegroup Container folder is stored with Disk Manager.
Copy the Memory-Optimized Data Filegroup Container folder in its entirety to a new drive with more free space.
Modify thresholds for related unit monitors accordingly to suit the expected workload and conditions in the environment.
Disable the monitor entirely if disk space is not a concern for the instance.

Overrideable Parameters

Name	Description	Default Value
Alert Priority	Defines Alert Priority.	Normal
Alert Severity	Defines Alert Severity.	MatchMonitorHealth
Enabled	Enables or disables the workflow.	Yes
Generates Alerts	Defines whether the workflow generates an Alert.	Yes

Element properties:

Target

Microsoft.SQLServer.2016.DBFileGroupFx

Parent Monitor

Microsoft.SQLServer.2016.AggregateMonitor.DBFileGroupFx.SpaceMonitor

Algorithm

BestOf

Source Monitor

Microsoft.SQLServer.2016.Monitor.Container.FreeSpacePercent

Relationship

Microsoft.SQLServer.2016.DBFileGroupFxHostsContainer

Category

PerformanceHealth

Enabled

True

Alert Generate

True

Alert Severity

MatchMonitorHealth

Alert Priority

Normal

Alert Auto Resolve

True

Remotable

True

Accessibility

Public

Alert Message

MSSQL 2016: Memory-Optimized Data Filegroup Container is running out of space

The Memory-Optimized Data Filegroup "{0}" for the database "{1}" in SQL instance "{2}" on computer "{3}" is running out of space. Please review the state of unit monitors to determine the root cause.
See knowledge section for more details.

Source Code:

<DependencyMonitor ID="Microsoft.SQLServer.2016.DependencyMonitor.DBFileGroupFx.ContainerFreeSpacePercentRollUp" Accessibility="Public" Enabled="true" Target="SQL2016Core!Microsoft.SQLServer.2016.DBFileGroupFx" ParentMonitorID="Microsoft.SQLServer.2016.AggregateMonitor.DBFileGroupFx.SpaceMonitor" Remotable="true" Priority="Normal" RelationshipType="SQL2016Core!Microsoft.SQLServer.2016.DBFileGroupFxHostsContainer" MemberMonitor="Microsoft.SQLServer.2016.Monitor.Container.FreeSpacePercent">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2016.DependencyMonitor.DBFileGroupFx.ContainerFreeSpacePercentRollUp.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>MatchMonitorHealth</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.FileGroup"]/GroupName$</AlertParameter1>

      <AlertParameter2>$Target/Host/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.Database"]/DatabaseName$</AlertParameter2>

      <AlertParameter3>$Target/Host/Host/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.ServerRole"]/InstanceName$</AlertParameter3>

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

    </AlertParameters>

  </AlertSettings>

  <Algorithm>BestOf</Algorithm>

  <MemberUnAvailable>Error</MemberUnAvailable>

</DependencyMonitor>