Memory-Optimized Data Filegroup Container Free Space

Microsoft.SQLServer.Windows.Monitor.Container.FreeSpacePercent (UnitMonitor)

The monitor reports a warning when the available disk space for the Memory-Optimized data filegroup container 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 alert when the free space drops below the Critical Threshold. Note that this monitor is disabled by default. Use overrides to enable it when necessary.

Knowledge Base article:

Summary

The monitor reports a warning when the available disk space for the Memory-Optimized data filegroup container 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 alert 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:

Overridable Parameters

Name

Description

Default Value

Alert Priority

Defines Alert Priority.

Normal

Alert Severity

Defines Alert Severity.

Match monitor’s health

Azure Maximum File Size (MB)

The maximum size of data file stored in Azure BLOB Storage. The workflow will consider this value as a maximum storage capacity for each file.

1048576

Critical Threshold

The monitor will change the state to 'Critical' if the value drops below this threshold.

10

Enabled

Enables or disables the workflow.

No

Generates Alerts

Defines whether the workflow generates an Alert.

No

Interval (seconds)

The recurring interval of time in seconds in which to run the workflow.

900

Synchronization Time

The synchronization time specified by using a 24-hour format. May be omitted.

 

Timeout (seconds)

Specifies the time the workflow is allowed to run before being closed and marked as failed.

300

Timeout for query execution (seconds)

The workflow will fail and register an event, if the query execution takes longer than the specified period.

60

Timeout for database connection (seconds)

The workflow will fail and register an event, if it cannot access the database during the specified period.

15

Warning Threshold

The monitor will change the state to 'Warning' if the value drops below this threshold.

20

Element properties:

TargetMicrosoft.SQLServer.Windows.Container
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledFalse
Alert GenerateTrue
Alert SeverityMatchMonitorHealth
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.Windows.MonitorType.Container.FreeSpacePercent
RemotableTrue
AccessibilityPublic
Alert Message
MSSQL on Windows: Insufficient Free Space for Memory-Optimized Data Filegroup Container
Only {6}\% of free space available for the Memory-Optimized Data Filegroup Container "{2}", which is less than the configured threshold for the alert.
Server: {0}
SQL Server Instance: {1}
Database: {4}
Filegroup: {3}
Container: {2}
Disk size: {5} MB
Memory-Optimized data filegroup container free space: {7} MB ({6}\%)
Memory-Optimized data filegroup container size: {8} MB
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.Windows.Monitor.Container.FreeSpacePercent" Accessibility="Public" Enabled="false" Target="SqlDiscW!Microsoft.SQLServer.Windows.Container" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Windows.MonitorType.Container.FreeSpacePercent" ConfirmDelivery="true">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.Windows.Monitor.Container.FreeSpacePercent.AlertMessage">
<AlertOnState>Error</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>MatchMonitorHealth</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Target/Host/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</AlertParameter1>
<AlertParameter2>$Target/Host/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</AlertParameter2>
<AlertParameter3>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.File"]/FileName$</AlertParameter3>
<AlertParameter4>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.Filegroup"]/GroupName$</AlertParameter4>
<AlertParameter5>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.Database"]/DatabaseName$</AlertParameter5>
<AlertParameter6>$Data/Context/Property[@Name='DiskTotalSizeMB']$</AlertParameter6>
<AlertParameter7>$Data/Context/Property[@Name='FreeSpacePercent']$</AlertParameter7>
<AlertParameter8>$Data/Context/Property[@Name='FreeSpaceMB']$</AlertParameter8>
<AlertParameter9>$Data/Context/Property[@Name='AllocatedSizeMB']$</AlertParameter9>
</AlertParameters>
</AlertSettings>
<OperationalStates>
<OperationalState ID="Critical" MonitorTypeStateID="ContainerFreeSpaceCritical" HealthState="Error"/>
<OperationalState ID="Warning" MonitorTypeStateID="ContainerFreeSpaceWarning" HealthState="Warning"/>
<OperationalState ID="Success" MonitorTypeStateID="ContainerFreeSpaceSuccess" HealthState="Success"/>
</OperationalStates>
<Configuration>
<MachineName>$Target/Host/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</MachineName>
<NetbiosComputerName>$Target/Host/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/NetbiosComputerName$</NetbiosComputerName>
<InstanceName>$Target/Host/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</InstanceName>
<DatabaseName>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.Database"]/DatabaseName$</DatabaseName>
<FileName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.File"]/FileName$</FileName>
<AzureMaxFileSizeMB>1048576</AzureMaxFileSizeMB>
<CriticalThreshold>10</CriticalThreshold>
<WarningThreshold>20</WarningThreshold>
<ConnectionString>$Target/Host/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/ConnectionString$</ConnectionString>
<InstanceVersion>$Target/Host/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Version$</InstanceVersion>
<InstanceEdition>$Target/Host/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Edition$</InstanceEdition>
<MonitoringType>$Target/Host/Host/Host/Property[Type="SqlDiscW!Microsoft.SQLServer.Windows.DBEngine"]/MonitoringType$</MonitoringType>
<SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>
<SqlTimeoutSeconds>15</SqlTimeoutSeconds>
<TimeoutSeconds>300</TimeoutSeconds>
<IntervalSeconds>900</IntervalSeconds>
<SyncTime/>
</Configuration>
</UnitMonitor>