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.
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.
| Send feedback about this page via email or DocCommentXchange | Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 |