Sybase Statements Test

This test reports critical resource usage and general performance statistics related to transactions that are executed on the Sybase server. For execution, this test requires the enable monitoring, statement statistics active, per object statistics active, statement pipe max messages, and statement pipe active configuration parameters to be enabled.

Target of the test : A Sybase adaptive server (ver. 12.5 and above) on which MDA tables have been installed

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every database being monitored

Configurable parameter for the test

TEST PERIOD – How often should the test be executed Host – The IP address of the Sybase server Port – The port on which the server is listening User – To enable this test to connect to the Sybase server and collect the required metrics, it is enough if you configure the test with the name of a Sybase user who has the “mon_role”. However, for best results, it is recommended that you configure all Sybase tests with the credentials of a Sybase user who has the “mon_role”, “sa_role’, and “sybase_ts_role”. Password – The password corresponding to the above user Confirm password – Confirm the password by retyping it here. ddrowcount – Specify the number of transactions for which details will be available in the detailed diagnosis page. Such limit has been set to By default, this parameter is set to 5. This indicates that by default, the detailed diagnosis of this test will only report information related to the top-5 transactions to the database. To view details pertaining to more number of top transactions, increase the ddrowcount.  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 CPU time:	The average cpu time consumed by the transactions to this database.	MillSec	Ideally, this value should be low. If the value is high, then use the detailed diagnosis of this measure to view the top transactions in terms of CPU consumption. Such transactions can then be fine-tuned, so that they are more resource-efficient.
Max CPU time:	The maximum CPU time of transactions to this database.	MillSec
Avg logical reads:	The average number of logical reads performed by  the transactions to this database.	Number	Use the detailed diagnosis of this measure to view the complete details of transactions causing logical reads to occur on the database.
Max logical reads:	The maximum number of logical reads performed by transactions to this databases	Number
Avg physical reads:	The average number of physical reads performed by transactions to this database.	Number	Physical database reads can cause processing overheads to escalate, thereby affecting database performance. These transactions therefore have to be minimized. If the value of this measure is very high, then use the detailed diagnosis of this measure to view the details of the transactions that are causing physical reads to occur.
Max physical reads:	The maximum number of physical reads performed by transactions to this database.	Number
Avg page modified:	The average number of pages modified by transactions to this database.	Number	Use the detailed diagnosis of this measure to view the details of transactions that are modifying pages.
Max Page modified:	The maximum number pages modified by transactions to this database.	Number
Avg long waited:	The average execution time of transactions that have taken too long to execute.	Millsec	If the value of this measure is high, it could indicate that one/more inefficient queries are executing on this database. You might then have to use the detailed diagnosis of the measure to view the top transactions in terms of execution time, and identify that transaction that has taken the longest to execute. Fine-tune the transaction to prevent database slowdowns.”
Max long waited:	The maximum number of times transactions waited in a database.	MillSec

If the transactions to a database appear to be consuming too much CPU, then, you may want to identify the exact transaction that is utilizing the CPU resources excessively. For this purpose, you can use the detailed diagnosis of the Avg CPU time measure. This reveals the top 10 transactions in terms of their CPU usage, and thus enables administrators to isolate the most CPU-intensive transaction.

Figure 1 : The detailed diagnosis of the Avg CPU time measure

In the event of a sudden spike in the number of logical reads to a database, check the detailed diagnosis of the Avg logical reads measure to identify the transaction that is causing reads to rise.

Figure 2 : The detailed diagnosis of the Avg logical reads measure