SQL Waits Test

This test reports key statistics pertaining to wait status. This test is specific to Microsoft SQL Server 2005 (or above), and will hence not report any measure for any of the other versions of the Microsoft SQL server.

This test has been disabled by default. To enable this test, go to the enable / disable tests page using the menu sequence : Agents -> Tests -> Enable/Disable, pick Microsoft SQL as the Component type, Performance as the Test type, choose this test from the disabled tests list, and click on the << button to move the test to the ENABLED TESTS list. Finally, click the Update button.

Target of the test : A Microsoft SQL server 2005 (or above)

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every type of wait on the Microsoft SQL Server 2005 (or above) being monitored.

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The IP address of the Microsoft SQL server.
  3. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433.
  4. ssl – If the Microsoft SQL server being monitored is an SSL-enabled server, then set the ssl flag to Yes. If not, then set the ssl flag to No.
  5. instance - In this text box, enter the name of a specific Microsoft SQL instance that is to be monitored. The default value of this parameter is “default”. To monitor a Microsoft SQL instance named “CFS”, enter this as the value of the INSTANCE parameter.
  6. useperfmon – By default, this flag is set to Yes, indicating that this test uses the Windows Perfmon utility by default to pull out the metrics of interest. To instruct the test to use queries for metrics collection and not Perfmon, set this flag to No. Typically, when monitoring a Microsoft SQL server in an agent-based manner, its best to go with the default setting – i.e., use Perfmon for metrics collection. However, when monitoring the Microsoft SQL server in an agentless manner, its ideal to use queries instead of Perfmon to collect the required metrics. In such cases, set this flag to No. 
  7. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as "Not applicable" by the agent if the server is not up.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Avg wait time:

Indicates the average duration of this wait type.

Seconds

If a particular wait type is found to have persisted for a long time, it could indicate a processing overhead.

Waits in progress:

Indicates the number of processes currently waiting on this wait type.

Number

Closely monitoring Waits in progress along with Avg wait time over a period of time will reveal wait types that are locking critical system resources.

Waits started:

Indicates the number of waits started per second of this wait type.

Waits/sec

 

Cumulative waits:

Indicates the percentage of time during the last measurement period wait events of this type occurred.

Percent

Compare the value of this measure across wait types to know which type of waits have occurred frequently during the last measurement period.