Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Introduction to monitoring and improving performance

Improving database performance involves evaluating the current performance of your database, and considering all your options before changing anything. By re-evaluating your database schema and application design using the performance features and analysis tools provided in SQL Anywhere, you can diagnose and correct performance problems and keep your database performing at its optimum level.

Ultimately, how well your database performs depends heavily on its design. One of the most basic of ways of improving performance is with good schema design. The database schema is the framework of your database, and includes definitions of such things as tables, views, triggers, and the relationships between them. Re-evaluate your database schema and make note of the areas where small changes can offer impressive gains. For more information about designing your database schema, see Designing Your Database.

Once your database is in production, SQL Anywhere provides several advanced tools for detecting and diagnosing performance issues that arise. The majority of these tools rely on the diagnostic tracing infrastructure—a system of tables, files, and other components that capture and store diagnostic data. You can then use this data to perform various diagnostic and monitoring tasks such as application profiling.

There are several approaches to generating and analyzing performance data in SQL Anywhere:

• Application profiling using 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.

• Advanced application profiling using the Database Tracing wizard  This wizard, available from Application Profiling mode in Sybase Central, provides the ability to customize the types of performance data gathered. This allows you to focus on specific users or activities that require attention. 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 Performing request trace analysis.

• Index Consultant  This feature analyzes the indexes in the database and provides improvement recommendations. This tool can be accessed either through Application Profiling mode, or as a standalone tool. See Index Consultant.

• Procedure profiling  This feature allows you to see 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. A more simple implementation is also available using system procedures. See Procedure profiling in Application Profiling mode.

  For more information on how to perform procedure profiling tasks using commands, see Procedure profiling using system procedures.