XTP Configuration

Microsoft.SQLServer.Linux.Monitor.DBFilegroupFx.XTPConfigurationMonitor (UnitMonitor)

This monitor checks the status of the SQL Database XTP configuration. Note that this monitor does not check if an XTP container and the log file are stored on different physical disks due to Linux specifics.

Knowledge Base article:

Summary

This monitor checks the XTP configuration setting for this database. Since this monitor is a part of an overall standards requirement, an alert would be generated if the setting does not meet the specified standard. Note that this monitor works only if Containers in MEMORY_OPTIMIZED_DATA Filegroup are not empty. At that, the monitor does not check if an XTP container and the log file are stored on different physical disks due to Linux specifics.

Configuration

The monitor verifies next the best practices:

Database with memory-optimized tables should be bound to a dedicated resource pool.
In addition, the dedicated pool should have both max_memory and min_memory configured. Therefore, if values min_memory_percent and max_memory_percent have default values, the monitor raises alert.
Only one database should be bound to a single dedicated pool (i.e., you should not bind multiple DBs to a pool).

External

See more detailed information:

Estimate Memory Requirements for Memory-Optimized Tables

Bind a Database with Memory-Optimized Tables to a Resource Pool

Monitor and Troubleshoot Memory Usage

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.	43200
Synchronization Time	The synchronization time specified by using a 24-hour format. May be omitted.
Timeout (seconds)	Specifies the time the workflow is allowed to run before being closed and marked as failed.	300
Timeout for query execution (seconds)	The workflow will fail and register an event, if the query execution takes longer than the specified period.	60
Timeout for database connection (seconds)	The workflow will fail and register an event, if it cannot access the database during the specified period.	15

Element properties:

Target

Microsoft.SQLServer.Linux.DBFilegroupFx

Parent Monitor

System.Health.ConfigurationState

Category

ConfigurationHealth

Enabled

True

Alert Generate

True

Alert Severity

Warning

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.Linux.MonitorType.DBFilegroupFx.XTPConfiguration

Remotable

True

Accessibility

Public

Alert Message

MSSQL on Linux: XTP configuration is invalid

XTP configuration for database "{2}" on SQL Server instance "{1}", computer "{0}" is not set according to the best practices. {3}

RunAs

Default

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.Linux.Monitor.DBFilegroupFx.XTPConfigurationMonitor" Accessibility="Public" Enabled="true" Target="SqlDiscL!Microsoft.SQLServer.Linux.DBFilegroupFx" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Linux.MonitorType.DBFilegroupFx.XTPConfiguration" ConfirmDelivery="false">

  <Category>ConfigurationHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.Linux.Monitor.DBFilegroupFx.XTPConfigurationMonitor.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</AlertParameter1>

      <AlertParameter2>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</AlertParameter2>

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

      <AlertParameter4>$Data/Context/Property[@Name='Status']$</AlertParameter4>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <MachineName>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</MachineName>

    <NetbiosComputerName>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/NetbiosComputerName$</NetbiosComputerName>

    <InstanceName>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</InstanceName>

    <ConnectionString>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/ConnectionString$</ConnectionString>

    <InstanceVersion>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Version$</InstanceVersion>

    <InstanceEdition>$Target/Host/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Edition$</InstanceEdition>

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

    <IntervalSeconds>43200</IntervalSeconds>

    <SyncTime/>

    <SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>

    <SqlTimeoutSeconds>15</SqlTimeoutSeconds>

    <TimeoutSeconds>300</TimeoutSeconds>

  </Configuration>

</UnitMonitor>