SQL Applications Test

Sometimes the database performs poorly due, not to blocking, but to particularly heavy loads. Often the DBA will determine that the database simply cannot support the work that it is being asked to do and maintain adequate performance. This does not necessarily mean it is time to create more indexes or throw more hardware at the problem. One cannot always assume that periods of high utilization represent legitimate work. There could be problems in the applications that are running, or even problems caused by the user. Maybe the application has a data paging functionality, but the user has opted to receive the entire 100,000 row DataSet every time, even though she/he has applied a sort which gives her the one row she needs first with each query. Regardless, it is important to identify performance issues and eliminate them. The SQL Applications test identifies which program has more connections open to (i.e., processes running in) the SQL database. Simply looking at the CPU cycles taken up by a process will not indicate which of these processes has been most active recently. For example, the SQL Server internal processes may have been running for days and will probably always show up as the processes that have taken the most CPU time since the database booted up. Hence, it is more helpful to find processes that have used lots of CPU for the majority of the time that they have been connected. This value represents how “expensive” a process is with respect to the SQL database server.

For each program that connects to the database server, the SQL Applications test reports the total CPU cycles for each second that the program is connected to the database. This value, represented by the CPU cycles rate measure, is an aggregate of all the CPU cycles consumed by every instance of the program while it is connected to the database server. The Avg CPU cycles rate measure represents the CPU cycles rate averaged across the number of processes in the database for the program under consideration. The Avg CPU cycles rate quantifies how bad a program is compared to the others, by dividing the CPU cycles rate by the number of connected instances. A high value for this value would indicate that every instance of the program was CPU-intensive. A lower value would indicate that the program may have some instances that cause performance problems, but also has instances that are mostly idle.

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.

Note:

This test is applicable only to Microsoft SQL Server 2005 (and above).

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 program on the MS 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. excludepattern - Provide a comma-separated list of programs/processes on the SQL server that need to be excluded from monitoring. The default value is none, indicating that all processes are monitored by default. To make sure that the test ignores a few processes, specify the process names as a comma-separated list. For example: SQL_Query_Analyzer,jTDS. You can also use wild card patterns in your specification - for instance, SQL*,*TDS,Microsoft*. 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
Number of processes:	Indicates the number of database processes associated with a specific program.	Number	A comparison of this value across programs will indicate which program is initiating most connections to the database. Comparison of this value over time can provide indications of potential changes in database activity characteristics of a program.
CPU cycles rate:	Indicates the number of CPU cycles consumed by all processes of a program, per minute of login.	Cycles/sec	The higher the value, the more CPU resources that the program is taking in the database.
Avg CPU cycles rate:	Indicates the number of CPU cycles consumed by a process of a program, per minute of login.	Cycles/Conn	This value is the ratio of the CPU cycles rate to the number of processes for a program.

The detailed diagnosis of the CPU cycles rate measure of this test provides details of the most expensive queries to the database - i.e., what host is a program running from, who is running it, and what application is running it, which database the program is accessing, etc (see Figure 1).

Figure 1 : The detailed diagnosis of the CPU cycles rate measure