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.
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.
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
Name | Description | Default Value |
Enabled | Enables or disables the workflow. | 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 |
Timeout (seconds) | Specifies the time the workflow is allowed to run before being closed and marked as failed. | 300 |
Target | Microsoft.SQLServer.2014.DBFileGroupFx |
Category | PerformanceCollection |
Enabled | True |
Alert Generate | False |
Remotable | True |
ID | Module Type | TypeId | RunAs |
---|---|---|---|
DS | DataSource | Microsoft.SQLServer.2014.DataSource.DBFileGroupFx.GarbageCollectionPerf | Default |
WriteToDB | WriteAction | Microsoft.SystemCenter.CollectPerformanceData | Default |
WriteToDW | WriteAction | Microsoft.SystemCenter.DataWarehouse.PublishPerformanceData | Default |
<Rule ID="Microsoft.SQLServer.2014.CollectionRule.DBFileGroupFx.GarbageCollectionFillFactor" Target="SQL2014Core!Microsoft.SQLServer.2014.DBFileGroupFx" Enabled="true">
<Category>PerformanceCollection</Category>
<DataSources>
<DataSource ID="DS" TypeID="Microsoft.SQLServer.2014.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="SQL2014Core!Microsoft.SQLServer.2014.ServerRole"]/InstanceName$</SqlInstanceName>
<ObjectName>In-Memory OLTP File Group</ObjectName>
<CounterName>Memory Optimized Data Garbage Collection Fill Factor (%)</CounterName>
<InstanceName/>
<DatabaseName>$Target/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.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>