SQL Azure Wait Statistics Test

In Microsoft SQL Azure database server, wait types represent the discrete steps in query processing, where a query waits for resources as the instance completes the request. By analyzing wait types and their wait times, administrators can receive quick and objective evidence of performance bottlenecks and their probable causes. The SQL Azure Wait Statistics 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 Azure database server

Agent deploying the test : An external/remote agent

Outputs of the test : One set of results for every type of wait on the target Microsoft SQL Azure Database server being monitored.

Configurable parameters for the test
Parameter Description

Test Period

How often should the test be executed.

Host

The host for which the test is to be configured.

Port

The port number through which the target SQL Azure database server communicates. The default port is 1433.

Instance

Enter the name of a specific SQL Azure database server instance that is to be monitored. The default value of this parameter is "default". To monitor an SQL Azure database server instance named "CFS", enter this as the value of the Instance parameter.

Note:

If you specify a particular instance name in this text box, ensure that you do not modify the Port parameter.

Database Name

Specify a comma-separated list of database instances that need to be monitored.

User

Provide the name of the Microsoft SQL Azure database server user.

Note:

Ensure that the user you have specified is capable of logging into the database server instances mentioned in the Database Name text box. Please be noted that you can specify only one User against multiple Database Names.

Password

Here, specify the password corresponding to the user.

Confirm Password

Confirm the password by retyping it in this text box.

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 SQL Azure database exists. Also, in such a case, the User and Password that you provide should be that of a user authorized to access the monitored SQL Azure database.

ISNTLMNV2

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 ISNTLMNV2 flag is set to No, indicating that NTLMv2 is not enabled by default on the target Microsoft SQL Azure database server host. Set this flag to Yes if NTLMv2 is enabled on the target host.

SSL

If the target Microsoft SQL Azure database server being monitored is an SSL-enabled server, then set the SSL flag to Yes. If not, then set the SSL flag to No.

Measurements made by the test
Measurement Description Measurement Unit Interpretation

Tasks maximum wait time

Indicates the maximum time taken for a wait of this task type during the last measurement period.

Seconds

Use the detailed diagnosis of this measure to figure out the tables that are consuming the maximum disk space.

Tasks signal wait time

Indicates the total signal wait time (across wait types) during which wait events of this type waited for a signal.

Seconds

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 wait time

Indicates the total wait time (across wait types) during which wait events of this type occurred during the last measurement period.

Seconds

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.

Waiting tasks

Indicates the number of waits of this task type during the last measurement period.

Number

This counter is incremented at the start of each wait.