Home
  •  

Average Wait Time

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

Average Wait Time monitor for 2014 DBs

Knowledge Base article:

Summary

Average Wait Time monitor for SQL 2014 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

Enabled

 

Yes

Generates Alerts

 

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.2014.DBEngine
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.2014.DBEngine.AverageWaitTime.MonitorType
RemotableTrue
AccessibilityPublic
Alert Message
MSSQL 2014: 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.2014.DBEngine.AverageWaitTimeMonitor" Accessibility="Public" Enabled="true" Target="SQL2014Core!Microsoft.SQLServer.2014.DBEngine" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2014.DBEngine.AverageWaitTime.MonitorType" ConfirmDelivery="false">

  <Category>PerformanceHealth</Category>

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

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.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="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/PerformanceCounterObject$:Locks</ObjectName>

  </Configuration>

</UnitMonitor>