SQL Wait Types Test

In SQL Server, wait types represent the discrete steps in query processing, where a query waits for resources as the instance completes the request. By analysing wait types and their wait times, administrators can receive quick and objective evidence of performance bottlenecks and their probable causes. The SQL Wait Types test enables this analysis. For every type of wait that is currently experienced by the server, this test reports the number, nature, and duration of waits, thereby leading you to the specific wait types that may have contributed to a general slowdown/deterioration in server performance.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every type of wait in the Microsoft SQL server monitored

Configurable parameters for the test
TEST PERIOD - How often should the test be executed Host – The IP address of the Microsoft SQL server. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433. 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. 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. USER – If a Microsoft SQL Server 7.0/2000 is monitored, then provide the name of a SQL user with the Sysadmin role in this text box. While monitoring a Microsoft SQL Server 2005 or above, provide the name of a SQL user with all of the privileges outlined in User Privileges Required for Monitoring Microsoft SQL server. password - The password of the specified user. confirm password - Confirm the password by retyping it. domain - By default, none is displayed in the DOMAIN text box. If the ‘SQL server and Windows’ authentication has been enabled for the server being monitored, then the DOMAIN can continue to be none. On the other hand, if ‘Windows only’ authentication has been enabled, then, in the DOMAIN text box, specify the Windows domain in which the managed Microsoft SQL server exists. Also, in such a case, the USER name and PASSWORD that you provide should be that of a user authorized to access the monitored SQL server. isntlmv2 - In some Windows networks, NTLM (NT LAN Manager) may be enabled. NTLM is a suite of Microsoft security protocols that provides authentication, integrity, and confidentiality to users. NTLM version 2 (“NTLMv2”) was concocted to address the security issues present in NTLM. By default, the isntlmv2 flag is set to No, indicating that NTLMv2 is not enabled by default on the target Microsoft SQL host. Set this flag to Yes if NTLMv2 is enabled on the target host. 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
Waiting tasks count:	Indicates the number of waits of this type during the last measurement period.	Number	This counter is incremented at the start of each wait.
Tasks avg wait time:	Indicates the total wait time for this wait type during the last measurement period.	MilliSecs	A low value is desired for this measure. When a user complaints that query execution takes too long, you can compare the value of this measure across wait types to know which type of wait is the key contributor to delays in query processing.
Tasks avg signal wait time:	Indicates the total signal wait time for this wait type during the last measurement period.	MilliSecs	The signal wait is the time between when a worker has been granted access to the resource and the time it gets scheduled on the CPU. A high value for this measure may imply a high CPU contention. To know which wait type registered the highest signal wait time and probably caused the CPU contention, compare the value of this measure across wait types.
Tasks avg resource wait time:	Indicates the total resource wait time for this wait type during the last measurement period.	MilliSecs	Resource wait time is the actual time a worker waited for the resource to be available. A high value for this measure indicates a delay in acquiring a resource. To know which wait type waited the longest for a resource and therefore contributed to a server slowdown, compare the value of this measure across wait types.
Tasks wait time:	Indicates the percentage of total wait time (across wait types) during which wait events of this type occurred.	Percent	When a user complaints that query execution takes too long, you can compare the value of this measure across wait types to know which type of wait is the key contributor to delays in query processing.
Tasks signal wait time:	Indicates the percentage of total signal wait time (across wait types) during which wait events of this type waited for a signal.	Percent	The signal wait is the time between when a worker has been granted access to the resource and the time it gets scheduled on the CPU. A high value for this measure may imply a high CPU contention. To know which wait type registered the highest signal wait time and probably caused the CPU contention, compare the value of this measure across wait types.
Tasks resource wait time:	Indicates the percentage of total resource wait time (across wait types) during which wait events of this type waited for a resource.	Percent	Resource wait time is the actual time a worker waited for the resource to be available. A high value for this measure indicates a delay in acquiring a resource. To know which wait type waited the longest for a resource and therefore contributed to a server slowdown, compare the value of this measure across wait types.