Home
  •  

SQL Server 2014 Memory-Optimized Data Number of files (rollup)

Microsoft.SQLServer.2014.DependencyMonitor.Database.ActivePairs (DependencyMonitor)

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.

Knowledge Base article:

Summary

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.

Causes

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.

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.2014.Database
Parent MonitorSystem.Health.PerformanceState
AlgorithmWorstOf
Source MonitorMicrosoft.SQLServer.2014.Monitor.DBFileGroupFx.ActivePairs
RelationshipMicrosoft.SQLServer.2014.RelationshipType.DBHostsDBFileGroupFx
CategoryPerformanceCollection
EnabledTrue
Alert GenerateFalse
Alert Auto ResolveFalse
RemotableTrue
AccessibilityPublic

Source Code:

<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>