Improving database performance

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. See Application profiling.

  • 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. See Advanced application profiling using diagnostic tracing.

  • Request trace analysis   This feature allows you to narrow diagnostic data gathering to requests (statements) issued by specific users or connections. See Perform request trace analysis.

  • 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. See Index Consultant.

  • 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. See Procedure profiling in Application Profiling mode.

    You can also use system procedures to implement procedure profiling. See Procedure profiling using system procedures.

  • 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. See Reading execution plans.

Note

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


Application profiling
Index Consultant
Advanced application profiling using diagnostic tracing
Other diagnostic tools and techniques
Monitoring database performance
Performance Monitor statistics
Performance improvement tips