SQL Current Request Statistics Test

In the database context, the connection between the user process and the server process is called a session. The server process communicates with the connected user process and performs tasks on behalf of the users.

This test tracks the resource usage of the sessions to the target Microsoft SQL server. In the process, the test turns the spotlight on resource-intensive SQL server 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 SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every 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. dd frequency - Refers to the frequency with which detailed diagnosis measures are to be generated for this test. The default is 2:1. This indicates that, by default, detailed measures will be generated every time this test runs, and also every time the test detects a problem. You can modify this frequency, if you so desire. Also, if you intend to disable the detailed diagnosis capability for this test, you can do so by specifying none against dd frequency. 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 this Microsoft SQL server.	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: Check the fragmentation activity of the disks of the Microsoft SQL server; Add additional indexes to the Microsoft SQL server or; Include a hint to the query which would considerably reduce the memory usage of the server.
Max memory usage:	Indicates the maximum memory used by the SQL server sessions.	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 on the SQL server, and the query that is responsible for all such resource-intensive tasks.
Avg CPU time:	Indicates the average time for which the SQL sessions used the CPU resources of the SQL server.	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: Check the fragmentation activity of the disks of the Microsoft SQL server; Add additional indexes to the Microsoft SQL server or; Include a hint to the query which would considerably reduce the memory usage of the server.
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: Check the fragmentation activity of the disks of the Microsoft SQL server; Add additional indexes to the Microsoft SQL server or; Include a hint to the query which would considerably reduce the memory usage of the server.
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 server, 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 SQL server uses cover index instead of cluster index.