The monitor reports a Critical state when the number of Active Checkpoint File Pairs in Memory-Optimized Data Filegroup in the database is higher than the specified threshold. This monitor is a dependency (rollup) monitor.
The monitor reports a Critical state when the number of Active Checkpoint File Pairs in Memory-Optimized Data Filegroups in the database is higher than the specified threshold.
Memory optimized Filegroup can have up to 8192 Active CFP (checkpoint file pairs) to store rows inserted, updated and deleted in durable memory-optimized tables. When there 8,000 CFPs are allocated, no new DML transactions can be executed on durable memory-optimized tables. Only checkpoint and merge operations are allowed to consume the remaining entries. This can lead to unavailability of the application. Therefore, it is important to monitor the count of CFPs and take corrective action.
There are two main reasons. First, you may have exceeded 256 GB limit for Memory-Optimized Data size of durable memory-optimized tables. Second, 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.2014.Database |
Parent Monitor | System.Health.PerformanceState |
Algorithm | WorstOf |
Source Monitor | Microsoft.SQLServer.2014.Monitor.DBFileGroupFx.ActivePairs |
Relationship | Microsoft.SQLServer.2014.RelationshipType.DBHostsDBFileGroupFx |
Category | PerformanceCollection |
Enabled | True |
Alert Generate | False |
Alert Auto Resolve | False |
Remotable | True |
Accessibility | Public |
<DependencyMonitor ID="Microsoft.SQLServer.2014.DependencyMonitor.Database.ActivePairs" 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.DBHostsDBFileGroupFx" MemberMonitor="Microsoft.SQLServer.2014.Monitor.DBFileGroupFx.ActivePairs">
<Category>PerformanceCollection</Category>
<Algorithm>WorstOf</Algorithm>
<MemberUnAvailable>Error</MemberUnAvailable>
</DependencyMonitor>