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).
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.
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.
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.
Name | Description | Default Value |
Enabled | Enables or disables the workflow. | Yes |
Generates Alerts | Defines whether the workflow generates an Alert. | No |
Target | Microsoft.SQLServer.2016.Database |
Parent Monitor | System.Health.PerformanceState |
Algorithm | WorstOf |
Source Monitor | Microsoft.SQLServer.2016.Monitor.DBFileGroupFx.StaleCFPs |
Relationship | Microsoft.SQLServer.2016.RelationshipType.DBHostsDBFileGroupFx |
Category | PerformanceCollection |
Enabled | True |
Alert Generate | False |
Alert Auto Resolve | False |
Remotable | True |
Accessibility | Public |
<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>