Home
  •  

[Deprecated] MSSQL 2016: Memory-Optimized Data Garbage Collection Fill Factor (\%)

Microsoft.SQLServer.2016.CollectionRule.DBFileGroupFx.GarbageCollectionFillFactor (Rule)

Collects Garbage Collection 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) for Memory-Optimized Data Filegroup. Note: This rule is disabled by default. Please use overrides to enable it when necessary.
This rule is considered to be obsolete in this Management Pack.

Knowledge Base article:

Summary

This rule is considered to be obsolete in this Management Pack.

The rule collects Garbage Collection 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) for Memory-Optimized Data Filegroup.

Garbage Collection Fill Factor 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 rule 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=799285

‘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=799286

Overrideable Parameters

Name

Description

Default Value

Enabled

Enables or disables the workflow.

No

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

Timeout (seconds)

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

300

Element properties:

TargetMicrosoft.SQLServer.2016.DBFileGroupFx
CategoryPerformanceCollection
EnabledFalse
Alert GenerateFalse
RemotableTrue

Member Modules:

ID Module Type TypeId RunAs 
DS DataSource Microsoft.SQLServer.2016.DataSource.DBFileGroupFx.GarbageCollectionPerf Default
WriteToDB WriteAction Microsoft.SystemCenter.CollectPerformanceData Default
WriteToDW WriteAction Microsoft.SystemCenter.DataWarehouse.PublishPerformanceData Default

Source Code:

<Rule ID="Microsoft.SQLServer.2016.CollectionRule.DBFileGroupFx.GarbageCollectionFillFactor" Target="SQL2016Core!Microsoft.SQLServer.2016.DBFileGroupFx" Enabled="false">

  <Category>PerformanceCollection</Category>

  <DataSources>

    <DataSource ID="DS" TypeID="Microsoft.SQLServer.2016.DataSource.DBFileGroupFx.GarbageCollectionPerf">

      <IntervalSeconds>900</IntervalSeconds>

      <SyncTime/>

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

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

      <ObjectName>Memory-Optimized Filegroup</ObjectName>

      <CounterName>Memory-Optimized Data Garbage Collection Fill Factor (%)</CounterName>

      <InstanceName/>

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

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

      <TimeoutSeconds>300</TimeoutSeconds>

      <ScriptDelayMsec>0</ScriptDelayMsec>

    </DataSource>

  </DataSources>

  <WriteActions>

    <WriteAction ID="WriteToDB" TypeID="SC!Microsoft.SystemCenter.CollectPerformanceData"/>

    <WriteAction ID="WriteToDW" TypeID="SCDW!Microsoft.SystemCenter.DataWarehouse.PublishPerformanceData"/>

  </WriteActions>

</Rule>