Home
  •  

SQL Server 2014 In-Memory OLTP 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 In-Memory file group 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 In-Memory file groups in the database is higher than the specified threshold.

Memory optimized file group 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 in-memory 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 file group is not under IO pressure.

Overrideable Parameters

Name

Description

Default Value

Enabled

 

Yes

Generates Alerts

 

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>