Home
  •  

[Deprecated] Garbage Collection

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

The monitor reports a Critical State and raises an alert if the amount of space used by active rows in Memory-Optimized Data files drops below the Threshold setting, expressed as a percentage of the size of data files. Note: This monitor is disabled by default. Please use overrides to enable it when necessary.

Knowledge Base article:

Summary

This monitor reports a Critical State and raises an alert if the Garbage Collector Fill Factor (an amount of space used by active rows in Memory-Optimized Data files, expressed as a percentage of the size of data files) drops below the Threshold setting.

Garbage Collection Fill Factor for Memory-Optimized Data Filegroup is the metric that shows the average fill factor across all active data/delta files containing inserted/deleted rows. If this metric is lower than 50%, it indicates that automatic merge is falling behind. This is not an immediate cause of concern as long as the number of CFPs (checkpoint file pairs) is below 8000 and you have plenty of storage available Memory-Optimized Data Filegroup.

Note: This monitor is disabled by default. Please use overrides to enable it when necessary.

More information can be found under the sys.dm_db_xtp_checkpoint_files (Transact-SQL) documentation

http://go.microsoft.com/fwlink/?LinkId=799249

‘How Garbage Collection works’ section can be found under the topic 'Creating and Managing Storage for Memory-Optimized Objects'

http://go.microsoft.com/fwlink/?LinkId=799250

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.

Error

Enabled

Enables or disables the workflow.

No

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.

900

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

Threshold

The collected value will be compared against this parameter.

50

Timeout (seconds)

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

300

Element properties:

TargetMicrosoft.SQLServer.2014.DBFileGroupFx
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledFalse
Alert GenerateTrue
Alert SeverityError
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.2014.MonitorType.DBFileGroupFx.GarbageCollectionState
RemotableTrue
AccessibilityPublic
Alert Message
MSSQL 2014: Memory-Optimized Data Garbage Collection Fill Factor is too low
The average fill factor across all ACTIVE data/delta files containing inserted/deleted rows is too low.
Server: {1}
SQL Server Instance: {0}
Database: {2}
Filegroup: {3}
Fill Factor: {4} \%
RunAsDefault

Source Code:

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

  <Category>PerformanceHealth</Category>

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

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

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

      <AlertParameter2>$Target/Host/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</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='StorageUsageFillFactor']$</AlertParameter5>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Critical" MonitorTypeStateID="Critical" HealthState="Error"/>

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

  </OperationalStates>

  <Configuration>

    <IntervalSeconds>900</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>Property[@Name='StorageUsageFillFactor']</Value>

    <Threshold>50</Threshold>

    <TimeoutSeconds>300</TimeoutSeconds>

    <ScriptDelayMsec>0</ScriptDelayMsec>

  </Configuration>

</UnitMonitor>