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

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Usage » Monitoring and Improving Database Performance » Monitoring and improving performance

 

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 and creating 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 about how to perform procedure profiling tasks using commands, 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 often used interchangeably because they are essentially the same. Diagnostic tracing is a more advanced level of application profiling.