Performance monitoring and diagnostic tools

To improve database performance, you must determine if the existing database is performing at optimum levels. This section provides information about using SQL Anywhere analysis tools to analyze and correct database performance.

SQL Anywhere provides several diagnostic tools for the detection of production database performance issues. Most of the tools rely on the diagnostic tracing infrastructure; a system of tables, files, and other components that capture and store diagnostic data. You can use diagnostic tracing data to perform diagnostic and monitoring tasks such as application profiling.

There are several methods for analyzing SQL Anywhere performance data including:

The Application Profiling Wizard This wizard, available from Application profiling mode in Sybase Central, provides a fully-automated method of checking performance. At the end of the wizard, improvement recommendations are provided.
The Database Tracing Wizard This wizard, available from Application Profiling mode in Sybase Central, provides the ability to customize the type of performance data gathered. This allows you to monitor the performance of specific users or activities.
Request trace analysis This feature allows you to narrow diagnostic data gathering to requests (statements) issued by specific users or connections.
Index Consultant This feature analyzes the indexes in the database and provides recommendations for improvement. You can access this tool through Application Profiling mode, or as a standalone tool.
Procedure profiling This feature allows you to determine how long it takes procedures, user-defined functions, events, system triggers, and triggers to execute. Procedure profiling is available as a feature in Sybase Central.
You can also use system procedures to implement procedure profiling.
Execution plans This feature allows you to use an execution plan to access information in the database related to a statement. You can view the execution plan in Interactive SQL or use SQL functions. You can retrieve an execution plan in several different formats and the plan can be saved.

Note

In the documentation, the terms application profiling and diagnostic tracing are used interchangeably. Diagnostic tracing is advanced application profiling.

Note

You can use Sybase Central to connect to a version 9 database server. However, the layout of windows in Sybase Central reverts to the version 9 layout, which does not include Application Profiling mode. Refer to your version 9 documentation for information about locating and using the Index Consultant in Sybase Central.