Empty Bucket percent in the hash index

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

This monitor checks Hash Index Empty Buckets Count in the SQL Database.

Knowledge Base article:

Summary

This monitor checks the Empty Bucket percent in the hash index for the tables in 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.

Configuration

You must specify a value for the bucket_count parameter when you create the memory-optimized table. Monitor raises a warning alert if empty_bucket_percent is less than Empty Bucket Percent Threshold, the bucket_count is too low and should be increased.

External

See the detailed information:

Indexes for Memory-Optimized Tables

Overridable Parameters

Name

Description

Default Value

Alert Priority

Defines Alert Priority.

Normal

Alert Severity

Defines Alert Severity.

Warning

Display index count

The display count of not configured indexes according to the best practice.

5

Empty Bucket Percent Threshold

Threshold for Empty Bucket Percent that indicates the number of empty buckets in the hash index.

10

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:

TargetMicrosoft.SQLServer.Linux.DBFilegroupFx
Parent MonitorSystem.Health.ConfigurationState
CategoryConfigurationHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.Linux.MonitorType.DBFilegroupFx.HashIndexEmptyBucketsCount
RemotableTrue
AccessibilityPublic
Alert Message
MSSQL on Linux: Empty Bucket percent in the hash index
Database "{2}" on SQL Server instance "{1}", computer "{0}" has tables with indexes where the bucket count is too low and should be increased. {3}
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.Linux.Monitor.DBFilegroupFx.HashIndexEmptyBucketsCount" 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.HashIndexEmptyBucketsCount" ConfirmDelivery="false">
<Category>ConfigurationHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.Linux.Monitor.DBFilegroupFx.HashIndexEmptyBucketsCount.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>
<EmptyBucketPercentThreshold>10</EmptyBucketPercentThreshold>
<ErrorTopLines>5</ErrorTopLines>
</Configuration>
</UnitMonitor>