Home
  •  

Average Wait Time

Microsoft.SQLServer.2016.DBEngine.AverageWaitTimeMonitor (UnitMonitor)

Average Wait Time monitor for 2016 DBs

Knowledge Base article:

Summary

Average Wait Time monitor for SQL 2016 DB

The average amount of wait time (milliseconds) for each lock request that resulted in a wait. Based on the DB engine counter, has identical values for all databases on a SQL instance.

Causes

Lock requests occurred

Resolutions

Use system DMVs such as sys.dm_exec_requests or SQL Server Management Studio Activity Monitor to identify processes which are blocking other processes.

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

300

Number of samples

Indicates how many times a measured value should breach a threshold before the state is changed.

6

Threshold

The collected value will be compared against this parameter.

250

Timeout (seconds)

Specifies the time the workflow is allowed to run before being closed and marked as failed.

300

Element properties:

TargetMicrosoft.SQLServer.2016.DBEngine
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.2016.DBEngine.AverageWaitTime.MonitorType
RemotableTrue
AccessibilityPublic
Alert Message
MSSQL 2016: DB Average Wait Time is too high
The Average Wait Time of SQL instance "{0}" on computer "{1}" is too high. See "Alert Context" tab for more details.
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2016.DBEngine.AverageWaitTimeMonitor" Accessibility="Public" Enabled="true" Target="SQL2016Core!Microsoft.SQLServer.2016.DBEngine" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2016.DBEngine.AverageWaitTime.MonitorType" ConfirmDelivery="false">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2016.DBEngine.AverageWaitTimeMonitor.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.ServerRole"]/InstanceName$</AlertParameter1>

      <AlertParameter2>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</AlertParameter2>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <IntervalSeconds>300</IntervalSeconds>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Threshold>250</Threshold>

    <NumSamples>6</NumSamples>

    <ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>

    <ObjectName>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBEngine"]/PerformanceCounterObject$:Locks</ObjectName>

  </Configuration>

</UnitMonitor>