DB Engine Disk Read Latency

Microsoft.SQLServer.Windows.Monitor.DBEngine.DiskReadLatency (UnitMonitor)

Targets to DB Engine and monitors the disk Read latency (ms) from all logical disks that host database files. The monitor is disabled by default. Use overrides to enable it when necessary. Note that the Linux platform is not supported.

Knowledge Base article:

Summary

The monitor support of 'Exclude List' override for mount points that must be excluded from monitoring. The list entries must be separated by a semicolon, for example, 'E:\;C:\'.

Causes

The problem may be caused by a disk bottleneck or disk contention.

Resolutions

This issue may be resolved by either:

Determine if disk performance has degraded or workload has increased.
Evaluate SQL Server Storage Best practices.
Enabling autogrowth for at least one file
Determine if storage for database files has been overcommitted e.g. shared storage in SAN or Virtual Machines.

Overridable Parameters

Name	Description	Default Value
Alert Priority	Defines Alert Priority.	High
Alert Severity	Defines Alert Severity.	Match monitor’s health
Critical Threshold	The monitor will change its state to Critical if the value exceeds this threshold.	40
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
Number of samples	Indicates how many times a measured value should breach a threshold before the state is changed.	6
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
Exclude List	A semicolon-separated list of mount points to be excluded from monitoring. Example: E:\;C:\

Element properties:

Target

Microsoft.SQLServer.Windows.DBEngine

Parent Monitor

System.Health.PerformanceState

Category

PerformanceHealth

Enabled

False

Alert Generate

True

Alert Severity

MatchMonitorHealth

Alert Priority

High

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.Windows.MonitorType.DBEngine.DiskLatency

Remotable

True

Accessibility

Public

Alert Message

MSSQL on Windows: DB Engine Disk Read Latency is over low threshold

The mount point "{2}" on SQL Server instance "{1}", computer "{0}" has disk read latency over the threshold. See the "Alert Context" tab for more details.

RunAs

Default

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.Windows.Monitor.DBEngine.DiskReadLatency" Accessibility="Public" Enabled="false" Target="SqlDiscW!Microsoft.SQLServer.Windows.DBEngine" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Windows.MonitorType.DBEngine.DiskLatency" ConfirmDelivery="false">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.Windows.Monitor.DBEngine.DiskReadLatency.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>High</AlertPriority>

    <AlertSeverity>MatchMonitorHealth</AlertSeverity>

    <AlertParameters>

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

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

      <AlertParameter3>$Data/Context/Property[@Name='ReadLatencyMountPoint']$</AlertParameter3>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

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

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

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

    <ValuePropertyName>ReadLatency</ValuePropertyName>

    <Threshold>40</Threshold>

    <NumSamples>6</NumSamples>

    <ExcludeList/>

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

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

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

    <MonitoringType>$Target/Property[Type="SqlDiscW!Microsoft.SQLServer.Windows.DBEngine"]/MonitoringType$</MonitoringType>

    <SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>

    <SqlTimeoutSeconds>15</SqlTimeoutSeconds>

    <TimeoutSeconds>300</TimeoutSeconds>

    <IntervalSeconds>900</IntervalSeconds>

    <SyncTime/>

  </Configuration>

</UnitMonitor>