Introduction
Introduction to SQL Optimizer for SAP ASE
New in This Release
Database Versions Supported
User Logon Privileges
Adaptive Server Configuration
Enter Authorization Key
Connect to the Database
Synchronize the Data Dictionary
Tutorials
SQL Inspector Tutorial
SQL Collector for Monitor Server Tutorial
SQL Scanner Tutorial
Performance Monitor Tutorial
SQL Optimizer Tutorial
Index Advisor Tutorial
Abstract Plan Manager Tutorial
SQL Repository Tutorial
Save SQL to the SQL Repository from other modules Tutorial
Configuration Analyzer Tutorial
Migration Analyzer Tutorial
Index Impact Analyzer Tutorial
Index Usage Analyzer Tutorial
Database Explorer Tutorial
Object Extractor Tutorial
SQL Worksheet Tutorial
Code Finder Tutorial
SQL Formatter Tutorial
User-Defined Temp Tables Tutorial
SQL History Tutorial
Preferences
Preferences Overview
Optimization for Pre-ASE 15 Tab
Editor Functions
Optimization Settings for Pre-ASE 15
Intelligence (Pre-ASE 15)
Optimization
Forces (Pre-ASE 15)
Quota (Pre-ASE 15)
Abstract Plan
Optimization for ASE 15 Tab
Optimization Settings for ASE 15
Intelligence (ASE 15)
Optimization
Forces/Goal (ASE 15)
Criteria
Quota (ASE 15)
Abstract Plan
Index Advisor Tab
Directory Setup Tab
Activity Log Tab
SQL History Tab
Plan Tab
SQL Worksheet Tab
SQL Scanner Tab
SQL Classification Tab
General Tab
Editor Tab
Database Setting Tab
DDL Tab
Performance Monitor
Syntax Highlight
Auto-Correction
Member and Argument Lookup
Indent and Outdent Text
Comment and Uncomment Text
Change Text to Uppercase and Lowercase
Bracket Pairing
Customize Editable Panes
Editor Functions
Editor Panes Shortcuts
SQL Information and Functions
Send SQL Statement to SQL Optimizer
Copy to SQL Worksheet
Send to Index Advisor
Open SQL from SQL Repository
Save SQL to SQL Repository
Create Benchmark Factory Import File
SQL Navigation
Modify Data
Copy Data from a Single Cell
Comments
Parameters
Parameters Window
Filter Database Objects
SQL Information Pane
Query Plan
Performance Monitor
Query Plan Overview
Review Query Plans
Query Plan Options
Animate Query Plans
Copy Query Plans
Plan Detail
Search Menu
Performance Monitor Overview
Performance Monitor Privileges
Supported Monitoring Tables
Performance Monitor Window
SQL Inspector
Performance Monitor Window
Right Pane for Database Statistics Summary
Right Pane for Monitoring Statistics
Right Pane for Information
Status Bar
Open the Performance Monitor
Connect and Disconnect from Database
Start and Stop the Performance Monitor
Manual Refresh Data
Hide and Show Console Tree
Chart Information
Display View for Performance Monitor Charts
Chart Drill Down
Lock and Unlock Chart
Chart Zoom
Copy Chart
Print Chart
Save Chart
Performance Monitor Functions
Performance Monitor Preferences
SQL Inspector Overview
SQL Inspector Privileges
Performance Statistics - Monitoring Tables
Performance Statistics - QP Metrics
Data Directory Setting
Open SQL Inspector
SQL Inspector Window
Add/Modify Inspector
SQL Collector for Monitor Server
Add/Modify Inspector
General Information
Monitor Process (Monitoring Tables)
Retrieval Criteria (Monitoring Tables)
Schedule Settings (Monitoring tables)
Retrieval Criteria (QP Metrics)
Schedule Time (QP Metrics)
Inspect
Ad-hoc Inspect
Abort Inspect
Scan Inspector
Find Inspector
Delete Marked Inspector
View Inspector Properties
Place Bookmarks in SQL Inspector Window
Create Benchmark Factory Import File
View and Add Charts
Scan Inspected SQL
Sum Statistics for a Stored Procedure
SQL Inspector Functions
SQL Collector for Monitor Server Overview
Adaptive Server Configuration Parameters for SQL Collector
Data Directory Setting
SQL Collector Window
Open SQL Collector for Monitor Server
Add or Modify a Collector
Query Plan
Monitored SQL Statement Types
Monitor
Ad-hoc Monitoring
Abort Monitor
Why use the SQL Scanner to Identify Problematic SQL Statements
Scan Collectors
Place Bookmarks in SQL Collector
View Particular Types of SQL Statement
Find Specific Information in Collectors
Find SQL Statement
Generate a Report for Monitored SQL Statements
Collector Properties
SQL Collector Functions
SQL Scanner
SQL Scanner Overview
SQL Scanner Window
Group Manager Window
View Group Summary
View Group Properties
Embedded and Dynamic SQL Statements
What files does SQL Scanner scan?
Data Directory Setting
Open SQL Scanner
Add Scanner Jobs
Index Advisor
Add Jobs to SQL Scanner
Add Database Objects (Add Jobs Wizard)
Add Abstract Plan Groups (Add Jobs Wizard)
Add SQL Collectors from the SQL Collector (Add Jobs Wizard)
Add SQL Inspectors (Add Jobs Wizard)
Add Source Code (Add Jobs Wizard)
Summary (Add Jobs Wizard)
Scan
Scan Source Code with Temp Tables
Review Scanned SQL
Find Jobs
Find SQL Using a Text String
View Job Properties
Open Group Manager while SQL Scanner is Open
Check Scanned SQL
Mark and Unmark All Jobs
Move or Copy Jobs to Other Group
Switch Database and User
Modify a Job
Placing Bookmarks in SQL Scanner
Open in SQL Collector
Open in SQL Inspector
Create Benchmark Factory Import File
View a Particular SQL Type
Generate Report for Scanned SQL
Save Abstract Plan
Create Benchmark Factory Import File
SQL Scanner Functions
SQL Conversion
Index Advisor Overview
Index Advisor Window
SQL for Cursor Checkbox
Run Time Tab Page
Statistics Tab Page
Chart Tab Page
Filter the Run Time Results
Advise Index Alternatives
Index Advising Details
Add Index Alternatives
Batch Show Plan
Create Indexes
Analyze Impact of New Indexes
Clear All Index Alternatives
Index Advisor Functions
Batch Run
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
SQL Worksheet
SQL Optimizer Window
Enter Original SQL Statement
Retrieve Query plan
Show Default Plan
Optimize Original SQL Statement
SQL for Cursor Checkbox
Optimize Using Abstract Plan Only
Insert User-Defined SQL
Open Optimized SQL
Create Benchmark Factory Import File
Optimized SQL
Optimized SQL
Optimization Details Window
Input Parameter Values
Analyze Time and Statistics Results
Analyze Run Time Results
Analyze Statistics Results
Analyze Chart Results
Filter Run Time Results
Find SQL Using a Text String
Generate a Report for Optimized SQL
Verify Correctness of Optimized SQL Statements
Insert User-Defined SQL
Check Abstract Plan Compatibility
Save Abstract Plan
Test for Scalability
Save Optimized SQL
Batch Run
Activity Log
Retrieve Run Time for a Group of SQL
Selected SQL
SQL Termination (Options)
SQL Termination (Percentage Delay)
Batch Termination
Run Time Mode
Batch Run Schedule
Commit or Rollback
View Batch Run Details
Run Time and Run Result
Run Time
Retrieve Run Time
Terminate a Run Time SQL Statement
Retrieve Run Result
Commit or Rollback
Terminate Retrieval of Run Result
SQL Comparer
SQL Worksheet Overview
SQL Worksheet Window
Execute SQL or Transact SQL
Retrieve Description of Tables and Views
Modify the Data
New Database Session
Command List
Bookmarks
SQL Worksheet Functions
SQL Formatter
SQL Formatter Overview
SQL Formatter Window
Format a SQL statement
Parameters within SQL Formatter
SQL Formatter Functions
Database Explorer
Database Explorer Overview
Database Explorer Window
Database Explorer Privileges
Open Database Explorer
Modify Data
Copy column names to another window
Database Explorer Functions
Code Finder
Code Finder Overview
Code Finder Window
Search Database Objects and Files
Regular Expression
Open Database Object or File in SQL Worksheet
Extract Object DDL to File
Save Search Results
Code Finder Functions
Object Extractor
Object Extractor Overview
Object Extractor Window
Extract Object DDL and Dependencies
Extraction Criteria
Object Extractor Functions
SQL Repository
SQL Repository Overview
SQL Repository Window
Add SQL to SQL Repository
Index Impact Analyzer
Add SQL to SQL Repository
Add SQL Wizard: General Page
Add SQL Wizard: SQL Information Page
Add SQL Wizard: Settings Page
Refresh SQL Query Plan
Modify SQL
Rename SQL
Delete SQL
Create Benchmark Factory Import File
SQL Repository Functions
Index Impact Analyzer Overview
Index Impact Analyzer Window
Index Usage Analyzer
Index Impact Analyzer Window
Right Pane for Analyzers
Right Pane for Scenarios Analyzed Folder
Right Pane for Scenario
Right Pane for SQL Analyzed Folder
Right Pane for SQL Statements
Create an Index Impact Analyzer
Create an Index Impact Analyzer
New Analysis Wizard: Analyzer Page
New Analysis Wizard: Select SQL Page
New Analysis Wizard: Index Page
Add Index Wizard
New Analysis Wizard: Abstract Plan Page
Drop Indexes
View Index Impact Analysis Details
Add SQL to an Index Impact Analysis
Add a Scenario
Delete an Index Impact Analysis
Modify an Index Impact Analyzer
Regenerate
Rename an Index Impact Analyzer
Index Impact Analyzer Functions
Index Usage Analyzer Overview
Index Usage Analyzer Window
Configuration Analyzer
Index Usage Analyzer Window
Right Pane for Analyzers
Right Pane for Tables Analyzed
Right Pane for Analyzed Table Information
Right Pane for SQL Analyzed Folder
Right Pane for SQL Statements
Create an Index Usage Analysis
Create an Index Usage Analysis
New Analysis Wizard: Analyzer Page
New Analysis Wizard: Select SQL Page
Update an Index Usage Analysis
View Index Usage Analysis Details
Modify an Index Usage Analysis
Rename an Index Usage Analysis
Delete an Index Usage Analysis
Index Usage Analyzer Functions
Configuration Analyzer Overview
Configuration Analyzer Window
Migration Analyzer
Configuration Analyzer Window
Right Pane for Analyzers
Right Pane for Scenarios Analyzed
Right Pane for Scenarios
Right Pane for SQL Analyzed Folder
Right Pane for SQL Statement
Create a Configuration Analysis
Create a Configuration Analysis
New Analysis Wizard: Analyzer Page
New Analysis Wizard: Select SQL page
New Analysis Wizard: Configuration Page
New Analysis Wizard: Abstract Plan
Regenerate a Configuration Scenario
Add a Scenario
View Configuration Analysis Details
Roll Back Changes to Configuration Parameters
Add SQL to a Configuration Analysis
Modify a Configuration Analysis
Rename a Configuration Analysis or Scenario
Delete a Configuration Analysis or Scenario
Modify a Configuration Scenario
Configuration Analyzer Functions
Migration Analyzer Overview
Migration Analyzer Window
Abstract Plan Manager
Migration Analyzer Window
Right Pane for Analyzers
Right Pane for Scenarios Analyzed
Right Pane for Scenarios
Right Pane for SQL Analyzed Folder
Right Pane for SQL Statement
Create a Migration Analysis
Create a Migration Analysis
New Analysis Wizard: Analyzer Page
New Analysis Wizard: Select SQL Page
New Analysis Wizard: Migration Page
New Analysis Wizard: Destination Configuration Page
New Analysis Wizard: Abstract Plan
View Migration Analysis Details
Migration Analysis Results
Add a Scenario
Roll Back Changes to Configuration Parameters
Add SQL to a Migration Analysis
Modify a Migration Analysis
Regenerate a Migration Scenario
Delete a Migration Analysis or Scenario
Rename a Migration Analysis or Scenario
Modify a Migration Scenario
Migration Analyzer Functions
Abstract Plan Manager Overview
Abstract Plan Group
Why Save the Abstract Plan?
Abstract Plan Compatibility with Original SQL
Abstract Plan Manager Window
Open the Abstract Plan Manager
Abstract Plan Group Functions
Export a Group to a Table
Import an Abstract Plan for each User
Abstract Plan ID Functions
Use Saved Abstract Plans
User-Defined Temp Tables
User-Defined Temp Table Overview
Create Temporary Tables
Parameters in Temporary Table SQL
View SQL scripts of temporary tables
Drop Temporary Tables
Example of Temporary Tables in SQL Scanner
Copy SQL with Temporary Tables to SQL Optimizer
Create Alternative SQL Which Uses Temporary Tables
Preference Settings for Handling Temporary Tables
SQL History
SQL History Overview
SQL History Window
Filter SQL Statements
Recall SQL Statements
SQL History Functions
Legal Information
Batch Run Schedule
SQL Optimizer > Optimized SQL > Batch Run > Batch Run Schedule
View Batch Run Criteria Window--Batch Run Schedule
The Batch Run Schedule criteria enable you to schedule when to start and stop the Batch Run.
Schedule setting
Start
Now
Specify to start the Batch Run immediately.
Date and Time
Specify to start the Batch Run at a specific date and time.
After
Specify to start the Batch Run after it has been running for a specified number of hours and minutes.
Until
Finished
Specify to run the entire Batch Run until all SQL statements are executed.
Date and Time
Specify to terminate the Batch Run on the specified date and time.
Duration
Specify to terminate the Batch Run after it has executed a specified number of hours and minutes.
Terminate immediately (Date/Time and Duration only)
Specify to terminate the currently executing SQL statement and all remaining unexecuted SQL statements.
Terminate after currently executing SQL is finished (Date/Time and Duration only)
Specify to finish running the currently executing SQL statement and do not run the remaining unexecuted SQL statements.
Commit or Rollback
SQL Optimizer > Optimized SQL > Batch Run > Commit or Rollback
In order to obtain the correct result during the Batch Run or Run Time of alternative SQL statement, the SQL statement executes against the database. However, when executing UPDATE, INSERT, DELETE statements, any changes to the database are rolled back.
Note: For UPDATE, INSERT and DELETE SQL statements, while retrieving the run time and run result you may encounter the following Adaptive Server error message:
Can't allocate space for object 'syslogs' in database 'database_name' because the 'logsegment' segment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment
This is due to the lack of space in system table (syslogs), in which all changes to the database are recorded. Empty the transaction log in the database and re-execute. Use the following command in the SQL Workshop module:
DUMP TRANSACTION database_name WITH TRUNCATE_ONLY
go
View Batch Run Details
SQL Optimizer > Optimized SQL > Batch Run > View Batch Run Details
The Batch Run Details window displays a summary of the run time information for all SQL statements executed. The Batch Run Details window displays after the batch run process is completed unless the Show details on next batch run checkbox in the Batch Run Details window is unchecked.
To review the Batch Run Details window after a Batch Run
Select View | Show Batch Run Details when the SQLOptimizer window is active.
If a SQL statement has a database error during the Batch Run, an explanation of the error is included in the Batch Run details.