Quest SQL Optimizer for Sybase Modules

Quest SQL Optimizer for Sybase contains the following modules:

Performance Diagnostics

The SQL Inspector identifies SQL statements with performance problems by extracting SQL statements and SQL performance statistics from the Adaptive Server Monitoring tables (Adaptive Server 12.5.0.3 or later) or from the QP Metrics (Adaptive Server 15 or later). The SQL Inspector graphically displays and compares SQL activity statistics to diagnose performance bottlenecks. With the SQL Inspector you can identify the top N most resource intensive SQL statements.

The SQL Collector for Monitor Server allows you to capture from the Sybase Monitor Server any currently executing SQL statements according to your user-defined criteria.  Each SQL statement captured is categorized according to suspected levels of performance problem.

The SQL Scanner extracts SQL statements in existing applications, database objects or files and then analyzes the SQL query plans to proactively identify inefficient SQL statements without execution of programs.

The Performance Monitor facilitates monitoring Adaptive Server’s performance. It is based on the Adaptive Server’s sophisticated build-in monitoring tables (12.5.0.3 and later), which provides database performance statistics and performance diagnostics metrics. With the Performance Monitor you can take snapshots, monitor in real time and schedule monitors of Adaptive Server’s performance statistics.

Performance Optimization

The SQL Optimizer applies advanced SQL transformation technology to generate a list of semantically equivalent SQL statements. To identify the most efficient SQL statement for your database environment you can benchmark test the SQL alternatives in your database.

The Index Advisor analyzes the syntax of a SQL statement and the database structure and then proposes new index Scenarios to help improve performance. It provides detailed information on the suggested indexes, such as, space requirements and selectivity. The index recommendations can be benchmarked to identify which index yields the greatest performance gain. It enables you to create your own indexes for testing.

The Abstract Plan Manager simplifies abstract plans maintenance. It provides a graphical interface for you to easily view, create, delete and modify abstract plans and abstract plan groups. Abstract plans are available in Adaptive Server 12.0 and later.

Performance Management

The Configuration Analyzer evaluates the effect on SQL performance when changing Adaptive Server parameter settings.  It enables you to analyze whether the database performance may improve before you make configuration parameter changes permanent.

The Index Usage Analyzer identifies unused indexes by analyzing query plans from SQL statements in your SQL Repository.  These unused indexes can then be deleted to free up space and improve the speed of DML statements.

The Index Impact Analyzer evaluates the effect of the creation of the indexes in the database system. It shows which SQL statements are impacted by the new indexes and identifies the index set alternative that yields the highest performance gain with the least impact on the database system.

The Migration Analyzer helps you to preempt performance degradation when performing database migrations, upgrades and application rollouts.  It ensures reliable database performance by tracking query plan and cost changes.  You have an option to integrate abstract plan management to help stabilize SQL performance during migrations.

Database Administration

The SQL Worksheet enables you to create and execute Transact-SQL code, database objects and SQL commands.

The Database Explorer accesses information on database objects from the database server and provides details on tables, views, procedures, triggers, rules, defaults, etc. The information displayed is dependent on the object type and the logon user’s privilege to the database.

The Object Extractor extracts objects’ DDL from the database. As an option, object dependencies can also be retrieved ordering them by the lowest dependencies first.

Development

The SQL Formatter ensures that the format of SQL statements is consistent throughout the application. The syntax of the SQL statement entered in the SQL Formatter is checked. The result is a SQL statement that is indented in a consistent way with comments, parameters, and optimizer forces highlighted. The SQL Formatter increases the maintainability of SQL statements and also saves you time from the tedious task of manually formatting complicated SQL statements.

The Code Finder identifies database objects and files that contain specific text. For example, you can easily identify which database objects or files use the EMPLOYEE table.

Functionality

The User-Defined Temp Table window automates the definition of a temporary table that is used by the database session for your current logon. Temp tables can be used for both Syntactical SQL Optimizer and SQL Scanner modules.

The SQL History saves the SQL statements from each time you optimize, each time you retrieve the query plan, run time or run results, and each time your execute a SQL statement.

The SQL Repository stores the SQL statements that you have identified as critical to your application performance.  These SQL statements are used for analysis to help you determine how changes to your database environment will affect or have affected your SQL query plans.