DB Disk Read Latency

Microsoft.SQLServer.Windows.Monitor.Database.DBDiskReadLatency (UnitMonitor)

Monitors the disk Read latency (ms) from all logical disks which host database files.

Knowledge Base article:

Summary

Monitors the disk Read latency (ms) from all logical disks which host database files.

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.	MatchMonitorHealth
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

Element properties:

Target

Microsoft.SQLServer.Windows.Database

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.Database.DBDiskReadLatency

Remotable

True

Accessibility

Public

Alert Message

MSSQL on Windows: DB Disk Read Latency is over Low Threshold

The database "{0}" in SQL Instance "{1}" on computer "{2}" has disk read latency over threshold. See "Alert Context" tab for more details.

RunAs

Default

Source Code:

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

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.Windows.Monitor.Database.DBDiskReadLatency.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>High</AlertPriority>

    <AlertSeverity>MatchMonitorHealth</AlertSeverity>

    <AlertParameters>

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

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

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

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

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

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

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

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

    <ObjectName>$Target/Host/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/PerformanceCounterObject$:TransactionLog</ObjectName>

    <CounterName>DB Disk Read Latency (ms)</CounterName>

    <PerfInstanceName/>

    <ValuePropertyName>ReadLatency</ValuePropertyName>

    <Threshold>40</Threshold>

    <NumSamples>6</NumSamples>

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

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

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

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

    <SqlTimeoutSeconds>15</SqlTimeoutSeconds>

    <TimeoutSeconds>300</TimeoutSeconds>

    <IntervalSeconds>900</IntervalSeconds>

    <SyncTime/>

  </Configuration>

</UnitMonitor>