When there are performance problems, you need to determine the sources of the problems and your goals in resolving them. The steps for analyzing performance problems are:
Collect performance data to get baseline measurements. For example, you might use one or more of the following tools:
Benchmark tests developed in-house or industry-standard third-party tests.
sp_sysmon, a system procedure that monitors Adaptive Server performance and provides statistical output describing the behavior of your Adaptive Server system.
See Performance and Tuning Guide: Tools for Performance Statistics for information on using sp_sysmon.
Adaptive Server Monitor provides graphical performance and tuning tools and object-level information on I/O and locks.
Any other appropriate tools.
Analyze the data to understand the system and any performance problems. Create and answer a list of questions to analyze your Adaptive Server environment. The list might include questions such as:
What are the symptoms of the problem?
What components of the system model affect the problem?
Does the problem affect all users or only users of certain applications?
Is the problem intermittent or constant?
Define system requirements and performance goals:
How often is this query executed?
What response time is required?
Define the Adaptive Server environment—know the configuration and limitations at all layers.
Analyze application design—examine tables, indexes, and transactions.
Formulate a hypothesis about possible causes of the performance problem and possible solutions, based on performance data.
Test the hypothesis by implementing the solutions from the last step:
Adjust configuration parameters.
Redesign tables.
Add or redistribute memory resources.
Use the same tests used to collect baseline data in step 1 to determine the effects of tuning. Performance tuning is usually a repetitive process.
If the actions taken based on step 7 do not meet the performance requirements and goals set in step 3, or if adjustments made in one area cause new performance problems, repeat this analysis starting with step 2. You might need to reevaluate system requirements and performance goals.
If testing shows that your hypothesis is correct, implement the solution in your development environment.
