Home
  •  

Memory-Optimized Data Active File Pairs

Microsoft.SQLServer.2014.Monitor.DBFileGroupFx.ActivePairs (UnitMonitor)

The monitor reports a Critical state and raises an alert when the number of active checkpoint file pairs in Memory-Optimized Data Filegroup is higher than the specified threshold.

Knowledge Base article:

Summary

The monitor reports a Critical state and raises an alert when the number of active checkpoint file pairs in Memory-Optimized Data Filegroup is higher than the specified threshold.

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.

Causes

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:

Overridable Parameters

Name

Description

Default Value

Alert Priority

Defines Alert Priority.

Normal

Alert Severity

Defines Alert Severity.

Warning

Enabled

Enables or disables the workflow.

Yes

Generates Alerts

Defines whether the workflow generates an Alert.

Yes

Interval (seconds)

The recurring interval of time in seconds in which to run the workflow.

300

Number of samples

Indicates how many times a measured value should breach a threshold before the state is changed.

6

Script Delay (milliseconds)

This parameter sets the delay between consecutive T-SQL queries executed by the workflow. This may help to reduce the footprint generated by the workflow in case of large number of target objects. Please advise with Microsoft Support before changing this parameter.

0

Synchronization Time

The synchronization time specified by using a 24-hour format. May be omitted.

00:02

Threshold

The collected value will be compared against this parameter.

7900

Timeout (seconds)

Specifies the time the workflow is allowed to run before being closed and marked as failed.

180

Element properties:

TargetMicrosoft.SQLServer.2014.DBFileGroupFx
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.2014.MonitorType.DBFileGroupFx.ActiveAndNonActiveFiles
RemotableTrue
AccessibilityPublic
Alert Message
MSSQL 2014: The total number of Active CFPs is approaching to the limit
The total number of active checkpoint file pairs reached the threshold.
Server: {1}
SQL Server Instance: {0}
Database: {2}
Filegroup: {3}
Active Checkpoint File Pairs: {4}
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2014.Monitor.DBFileGroupFx.ActivePairs" Target="SQL2014Core!Microsoft.SQLServer.2014.DBFileGroupFx" ParentMonitorID="SystemHealth!System.Health.PerformanceState" TypeID="Microsoft.SQLServer.2014.MonitorType.DBFileGroupFx.ActiveAndNonActiveFiles" Accessibility="Public" Enabled="true" Remotable="true" Priority="Normal" ConfirmDelivery="true">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2014.Monitor.DBFileGroupFx.ActivePairs.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Host/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</AlertParameter1>

      <AlertParameter2>$Target/Host/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.ServerRole"]/InstanceName$</AlertParameter2>

      <AlertParameter3>$Target/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.Database"]/DatabaseName$</AlertParameter3>

      <AlertParameter4>$Target/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.FileGroup"]/GroupName$</AlertParameter4>

      <AlertParameter5>$Data/Context/Property[@Name='ActivePairs']$</AlertParameter5>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="ErrorState" MonitorTypeStateID="ErrorState" HealthState="Warning"/>

    <OperationalState ID="SuccessState" MonitorTypeStateID="SuccessState" HealthState="Success"/>

  </OperationalStates>

  <Configuration>

    <IntervalSeconds>300</IntervalSeconds>

    <SyncTime/>

    <ServerName>$Target/Host/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ServerName>

    <SqlInstanceName>$Target/Host/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.ServerRole"]/InstanceName$</SqlInstanceName>

    <DatabaseName>$Target/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.Database"]/DatabaseName$</DatabaseName>

    <Value>$Data/Property[@Name='ActivePairs']$</Value>

    <Threshold>7900</Threshold>

    <TimeoutSeconds>180</TimeoutSeconds>

    <ScriptDelayMsec>0</ScriptDelayMsec>

    <NumSamples>6</NumSamples>

  </Configuration>

</UnitMonitor>