Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.9.1 - User Guide

Introduction Tutorials Preferences Editor Functions SQL Information and Functions Performance Monitor SQL Inspector SQL Collector for Monitor Server SQL Scanner Index Advisor SQL Optimizer
SQL Optimizer Overview Optimization Engine Common Coding Errors in SQL Statements What Function Should l Use to Retrieve the Run Time? Unsatisfactory Performance Results SQL Optimizer Functions SQL Editor Optimized SQL Activity Log
SQL Worksheet SQL Formatter Database Explorer Code Finder Object Extractor SQL Repository Index Impact Analyzer Index Usage Analyzer Configuration Analyzer Migration Analyzer Abstract Plan Manager User-Defined Temp Tables SQL History Legal Information

New Analysis Wizard: Analyzer Page

In the New Analysis wizard, the Analyzer page is used to name the analysis and to select the folder where the Analyzer information will be stored.

Creating a new Analyzer and select SQL to be analyzed

Select this option to create a new analysis using a different set of SQL statements.

Continuing an existing Analyzer using the Analyzer's selected SQL

Select this option to use the same set of SQL statements that is stored in an existing Analyzer. This option adds another Scenario under an existing Analyzer and does not retrieve a new set of query plans for the SQL statements. It uses the query plans that were originally saved with the SQL statements.

Do not drop the indexes after the analysis is finished

Specific to leaves the new indexes that you create under the Index tab after the analysis is finished. Otherwise, these changes are dropped at the end of the analysis processing. After the analysis is complete, the Create/Drop Indexes button displays at the top of the Index Impact Analyzer window which displays the Create Indexes, Drop Indexes, and the Drop All Indexes panes. These panes contain the scripts to create or drop the indexes. 

Item Description

Name

Enter the name for the analysis.

Description

Enter the description for the analysis.

Last modified

Displays the last modified date and time.

Save location

Displays the folder in the tree where the Analyzer is saved.

Folder Tree

In the bottom pane, select the folder where you want to save the Analyzer.

image\B_AddFolder.gif

To create a new folder, click Add Folder.

 

Related Topic

New Analysis Wizard: Select SQL Page

In the New Analysis wizard, the Select SQL page is used to select the SQL statements whose query plans you want to analyze in order to review the impact on their performance that the creation of new indexes would have.

Select SQL to check for performance changes:

Select SQL from

The SQL statements used in an analysis can be taken from the SQL Repository or the SQL Scanner. If you select SQL statements from the SQL Scanner, these statements are added to the SQL Repository.

Select the SQL statements for the analysis. If you select SQL statements from the SQL Scanner, the bottom portion of this section displays the SQL Repository folder so that you can select the folder where they will be stored.

SQL query plans to be analyzed:

In order to analyze the impact the proposed indexes might have on the performance of the SQL statements, the Index Impact Analyzer compares the query before the indexes are created to the plans after the indexes are created. The query plans from before the analysis can be obtained two ways:

Using existing query plan saved with the SQL

This option uses the query plan that was saved with the SQL statement when it was saved in the SQL Repository or SQL Scanner.

Obtaining a new query plan under the current connection

This option retrieves the query plan with the current logon and the current database settings. This current query plan is compared to the query plan that is retrieved after the new indexes are created.

 

Related Topic

New Analysis Wizard: Index Page

In the New Analysis wizard, the Index page is used to specific the index(es) that are used for this analysis.

Query plans will be grouped under a Scenario

An individual Index Impact Analyzer consists of a group of stored SQL statements with the query plans called the "Baseline Plan." The actual Impact Analysis displays in a "Scenario." A Scenario shows the comparison information for the stored SQL statements identifying the impact on the query plan that the creation of new index will have.

Name

Enter a name for the Scenario.

Description

Enter a description.

Enter and select indexes to be analyzed

Use Add Index to add the indexes for this Scenario.

Segment

Select the segment where you would like the index created from the drop-down list.

Consumer (Default 1 Range 1-20)

Specify the number of consumer processes that should perform the sort operation for creating the index. The actual number of consumer processes used to sort the index may be smaller than the specified number, if fewer worker processes are available when Adaptive Server executes the sort.

To add an index

Click Add Indeximage\B_AddIndex.gif to bring up the Add Index wizard.

To remove an index

Select the index and click Remove Index image\B_RemoveIndex.gif.

After you have created the first Scenario, you can add more Scenarios.

 

Related Topic

Add Index Wizard

The Add Index wizard is used to create the DDL for creating of the indexes for the Index Impact Analysis. This wizard is available on the Index page in the Index Impact Analyzer's New Analysis wizard. 

To open this Add Index wizard

Click image\B_AddIndex.gif.

Item Description
Index name Enter a name for the index.

Database

Select the database where the index is to be created from the drop-down list.

Owner

Select the owner of the index from the drop-down list.

Table

Select the table where the index is to be created from the drop-down list.

Table columns

List of all the columns from the selected table.

Index columns

Move the columns to be indexed from the Table columns list by either double-clicking the column or highlighting the column and clicking image\B_AddDatabaseObjects.gif.

Unique

Specify to create a unique index.

Clustered/Nonclustered

Select to create either a clustered or a nonclustered index

 

Related Topic

Related Documents