When you have performance problems, determine the sources of the problems and your goals in resolving them.
To analyze performance problems:
Collect performance data to get baseline measurements. For example, you might use one or more of the following tools:
Internally developed benchmark tests 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 Series: Monitoring Adaptive Server with sp_sysmon.
Monitoring tables, which describe resource utilization and contention from a server-wide to a user- or object-level perspective.
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 may need to reevaluate system requirements and performance goals.
If testing shows that your hypothesis is correct, implement the solution in your development environment.
www.sybase.com includes whitepapers that discuss additional ways to analyze performance.
