Home
  •  

Memory-Optimized Data Stale Checkpoint File Pairs Ratio (rollup)

Microsoft.SQLServer.2016.DependencyMonitor.Database.StaleCFPs (DependencyMonitor)

The monitor reports a warning state and raises an alert when the ratio of stale checkpoint file pairs in Memory-Optimized Data Filegroup is higher than the specified thresholds. This monitor is a dependency (rollup) monitor.
Please note that the alerts are raised only if the corresponding database is reasonably big (300 or more checkpoint files total).

Knowledge Base article:

Summary

The monitor reports a warning state and raises an alert when the portion of stale checkpoint file pairs in Memory-Optimized Data Filegroup is higher than the specified thresholds.

Please note that the alerts are raised only if the corresponding database is reasonably big (300 or more checkpoint files total).

Stale CFPs are files left in system waiting for log truncation or garbage collection.

Causes

The automatic-merge and/or the data persistence done by the background thread is not able to keep up with the transactional activity.

Merge of CFP is kicked off based on an internal merge policy. Please refer to this article for details.

Resolutions

Perform a manual merge and force garbage collection as described in these articles:

Also, you should check if the data storage for memory-optimized Filegroup is not under IO pressure.

Overrideable Parameters

Name

Description

Default Value

Enabled

Enables or disables the workflow.

Yes

Generates Alerts

Defines whether the workflow generates an Alert.

No

Element properties:

TargetMicrosoft.SQLServer.2016.Database
Parent MonitorSystem.Health.PerformanceState
AlgorithmWorstOf
Source MonitorMicrosoft.SQLServer.2016.Monitor.DBFileGroupFx.StaleCFPs
RelationshipMicrosoft.SQLServer.2016.RelationshipType.DBHostsDBFileGroupFx
CategoryPerformanceCollection
EnabledTrue
Alert GenerateFalse
Alert Auto ResolveFalse
RemotableTrue
AccessibilityPublic

Source Code:

<DependencyMonitor ID="Microsoft.SQLServer.2016.DependencyMonitor.Database.StaleCFPs" Accessibility="Public" Enabled="true" Target="SQL2016Core!Microsoft.SQLServer.2016.Database" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" RelationshipType="SQL2016Core!Microsoft.SQLServer.2016.RelationshipType.DBHostsDBFileGroupFx" MemberMonitor="Microsoft.SQLServer.2016.Monitor.DBFileGroupFx.StaleCFPs">

  <Category>PerformanceCollection</Category>

  <Algorithm>WorstOf</Algorithm>

  <MemberUnAvailable>Error</MemberUnAvailable>

</DependencyMonitor>