Azure SQL Current Request Statistics Test

This test tracks the resource usage of the sessions to the target Microsoft Azure SQL database. In the process, the test turns the spotlight on resource-intensive sessions and the queries executed by such sessions that may require fine-tuning. Additionally, the test also reports the average wait time of sessions, leads you to that session that has been waiting for the maximum time, and points you to the exact query that the session has been taking too long to execute. Inefficient queries are thus revealed, enabling you to quickly initiate query optimization measures.

Target of the test : A Microsoft Azure SQL database

Agent deploying the test : A remote agent

Outputs of the test : One set of results for the user who is logged into the Azure SQL database that is configured for monitoring

Configurable parameters for the test
Parameters	Description
Test Period	How often should the test be executed.
Host	The host for which the test is to be configured.
Port	The port at which the specified Host listens.
Database Name	Specify the name of the Azure SQL database that is to be monitored.
User Name and Password	Against the User Name and Password parameters, specify the credentials of the user who is vested with DBOWNER rights to the configured Database Name.
Confirm Password	Confirm the specified Password by retyping it here.
SSL	If the Azure SQL database service being monitored is SSL-enabled, then set the SSL flag to Yes. If not, then set the SSL flag to No.
Domain	By default, none is displayed in this text box. If the ‘SQL server and Windows’ authentication has been enabled for the Azure SQL database being monitored, then the Domain parameter 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 monitored database exists. Also, in such a case, the User Name and Password that you provide should be that of a 'domain user' with DBOWNER rights to the configured Database Name.
IS NTLMv2	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, this flag is set to No, indicating that NTLMv2 is not enabled by default for the target Microsoft Azure SQL database. Set this flag to Yes if NTLMv2 is enabled for the target database.
Detailed Diagnosis	To make diagnosis more efficient and accurate, the eG Enterprise embeds an optional detailed diagnostic capability. With this capability, the eG agents can be configured to run detailed, more elaborate tests as and when specific problems are detected. To enable the detailed diagnosis capability of this test for a particular server, choose the On option. To disable the capability, click on the Off option. The option to selectively enable/disable the detailed diagnosis capability will be available only if the following conditions are fulfilled: The eG manager license should allow the detailed diagnosis capability Both the normal and abnormal frequencies configured for the detailed diagnosis measures should not be 0.

Measurements made by the test
Measurement	Description	Measurement Unit	Interpretation
Avg memory usage	Indicates the average amount of memory that is currently used by all sessions of target Azure SQL database.	KB	A high value indicates that one/more Microsoft SQL sessions are consuming high memory. Use the detailed diagnosis of the Max memory usage measure to identify which session is consuming maximum memory. To reduce the memory consumption of the session, you may have to optimize the query displayed in the SQL TEXT column of the detailed diagnosis. To optimize the query, you would be required to do any one of the following: Add additional indexes to the target database; Include a hint to the query which would considerably reduce the memory usage of the database.
Max memory usage	Indicates the maximum memory used by the target Azure SQL database.	KB	The detailed diagnosis of this measure, if enabled, displays the session ID, the name of the database accessed by the session, the login name of the user who initiated the session, the login time of the user, the request start time, when the session was established, the session wait time and type, the session duration, the time for which the session hogged the CPU, the memory usage of the session, the session status, the total number of reads, writes, and logical reads performed by the session on the database, and the query executed by the session. From this information, you can easily identify the session that is consuming the maximum CPU/memory, the session that has been waiting for the maximum time for the query to execute, the session that has performed the maximum I/O activities, and the query that is responsible for all such resource-intensive tasks.
Avg CPU time	Indicates the average time for which this user's SQL sessions to the target Azure SQL database used the CPU resources .	Secs	A high value indicates that one/more Microsoft SQL sessions are hogging the CPU. Use the detailed diagnosis of the Max CPU time measure to identify which session is consuming the CPU resources excessively. To reduce the CPU consumption of a session, you may have to optimize the query displayed in the SQL TEXT column of the detailed diagnosis. To optimize the query, you would be required to do any one of the following: Add additional indexes to the Azure SQL database; Include a hint to the query which would considerably reduce the memory usage of the database.
Max CPU time	Indicates the maximum time for which the SQL sessions used the CPU.	Secs	The detailed diagnosis of this measure, if enabled, displays the session ID, the name of the database accessed by the session, the login name of the user who initiated the session, the login time of the user, the request start time, when the session was established, the session wait time and type, the session duration, the time for which the session hogged the CPU, the memory usage of the session, the session status, the total number of reads, writes, and logical reads performed by the session on the database, and the query executed by the session. From this information, you can easily identify the session that is consuming the maximum CPU/memory, the session that has been waiting for the maximum time for the query to execute, the session that has performed the maximum I/O activities on the SQL server, and the query that is responsible for all such resource-intensive tasks.
Avg wait time	Indicates the average time for which the SQL sessions were waiting.	Secs	A high value indicates that one/more Microsoft SQL sessions are waiting too long to perform a task – typically, to execute a query. Use the detailed diagnosis of the Max wait time measure to identify which session is taking too long for query execution. To reduce the wait time of a session, you may have to optimize the query displayed in the SQL TEXT column of the detailed diagnosis. To optimize the query, you would be required to do any one of the following: Add additional indexes to the Azure SQL database or; Include a hint to the query which would considerably reduce the memory usage of the database
Max wait time	Indicates the maximum time for which the SQL sessions waited.	Secs	The detailed diagnosis of this measure, if enabled, displays the session ID, the name of the database accessed by the session, the login name of the user who initiated the session, the login time of the user, the request start time, when the session was established, the session wait time and type, the session duration, the time for which the session hogged the CPU, the memory usage of the session, the session status, the total number of reads, writes, and logical reads performed by the session on the database, and the query executed by the session. From this information, you can easily identify the session that is consuming the maximum CPU/memory, the session that has been waiting for the maximum time for the query to execute, the session that has performed the maximum I/O activities on the SQL database, and the query that is responsible for all such resource-intensive tasks.
Avg I/O time for current queries	Indicates the average time taken by current queries for I/O processing.	Secs	A low value is desired for this measure. A high value indicates that one/more queries are I/O-intensive.
Max I/O time for current queries	Indicates the maximum time that the current queries took for I/O processing.	Secs	If the value of this measure exceeds 10 seconds, you will have to check the disk I/O subsystem for the proper placement of files – LDF and MDF on separate drives, tempDB on a separate drive, hot spot tables on separate filegroups. I/O can also be reduced if the Azure SQL database uses cover index instead of cluster index.