Performance monitoring and diagnostic tools

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. Some 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:

Tool	Details
Diagnostic tracing tools	Database Tracing Wizard This wizard provides the ability to customize the type of performance data gathered. This wizard allows you to monitor the performance of specific users or activities. 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 for single queries in Interactive SQL. Application profiling Application profiling generates data that you can use to understand how applications interact with the database and to identify and eliminate performance problems. Two methods are available for generating profiling information: 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. This tool is recommended for development environments and is optimized for tracking query performance. 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 Application Profiling mode in Sybase Central. You can also use system procedures to implement procedure profiling.
Other tools	Request logging Request logging logs to a text file individual requests received from, and responses sent to, an application. It is most useful for determining what the database server is being asked to do by the application. Request logging is also a good starting point for performance analysis of a specific application when it is not obvious whether the database server or the client is at fault. You can use request logging to determine the specific request to the database server that might be responsible for problems. The request log provides a subset of the information that is provided by diagnostic tracing and event tracing. Event tracing Event tracing is recommended for production environments and provides fine-grained control over the information that is logged. You can log both user- and system-defined trace events for both the database server and your application and customize the trace events to identify performance issues. Execution plans This feature allows you to examine the 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.

Tool

Details

Diagnostic tracing tools

Database Tracing Wizard This wizard provides the ability to customize the type of performance data gathered. This wizard allows you to monitor the performance of specific users or activities.
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 for single queries in Interactive SQL.
Application profiling Application profiling generates data that you can use to understand how applications interact with the database and to identify and eliminate performance problems. Two methods are available for generating profiling information:
- 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. This tool is recommended for development environments and is optimized for tracking query performance.
- 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 Application Profiling mode in Sybase Central.
  You can also use system procedures to implement procedure profiling.

Other tools

Request logging Request logging logs to a text file individual requests received from, and responses sent to, an application. It is most useful for determining what the database server is being asked to do by the application. Request logging is also a good starting point for performance analysis of a specific application when it is not obvious whether the database server or the client is at fault. You can use request logging to determine the specific request to the database server that might be responsible for problems. The request log provides a subset of the information that is provided by diagnostic tracing and event tracing.
Event tracing Event tracing is recommended for production environments and provides fine-grained control over the information that is logged. You can log both user- and system-defined trace events for both the database server and your application and customize the trace events to identify performance issues.
Execution plans This feature allows you to examine the 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.