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
Monitor Process
SQL Collector for Monitor Server > Add or Modify a Collector > Monitor Process
View Add Collector - Monitor Process Page
The Monitor Process page consists of the following settings:
| Item | Description |
|
Whole Server |
Monitor all SQL statements currently running from the database server. |
|
Process ID |
Monitor all SQL statements currently running which have the specified process ID (spid). The Process ID grid displays information about all current users and processes of the database server, displaying the process ID (spid), login name, host name, database name and the command currently executed (cmd). Click the row of the grid to select the process ID you want to monitor. To select all the process IDs, simply click the Select All checkbox. |
|
Connection Identity |
Monitor all currently running SQL statements with specified login name, host name, database name and program name. |
Collecting Criteria
SQL Collector for Monitor Server > Add or Modify a Collector > Collecting Criteria
The Collecting Criteria page has the following settings:
|
Section |
Item |
Description |
| SQL Type | All SQL | Monitor all SQL statements currently running. |
| Selected Type | To monitor the selected SQL classification type (Problematic or Complex). | |
|
Complex Range |
Number of table scan operations |
Specify the range of numbers for the table references in the query plan for Complex SQL statement definition. |
|
With full index scan |
Specify whether SQL statements with full index scan will be classified as Complex SQL statements. | |
|
Problematic Range
|
With full table scan |
Specify whether SQL statements with full table scan will be classified as Problematic SQL statements. |
|
With number of worktables greater than or equal to (Range = 1 to 99) |
Specify that SQL statements involving the number of worktables greater than or equal to the defined number are classified as Problematic SQL statements. | |
|
With number of Reformattings greater than or equal to (Range = 1 to 99) |
Specify that SQL statements whose query plan uses reformatting are classified as Problematic SQL. Reformatting is the process of generating a worktable with a clustered index and performing a nested-loop join. The Adaptive Server optimizer may choose this strategy when the table is large and does not have any useful index for a join. |
Note: The default settings for the values are taken from the SQL Classification tab in the Preferences window.
Schedule Settings
SQL Collector for Monitor Server > Add or Modify a Collector > Schedule Settings
The Schedule Settings page consists of the following settings:
| Item | Description |
|
Start Time |
Monitor start time. Default is the current time. |
|
Until (time) |
Monitor end time. Default is 5 minutes after the start time. |
|
Until (Duration) |
Monitoring duration in hours and minutes. Default is 5 minutes. |
|
Interval |
Monitoring interval time in seconds. Default is 3 seconds. |
Notes:
- Decreasing the interval time may affect the database server performance and network traffic.
- The ad-hoc monitoring from the right-click menu does not use the start, until, and duration time. Monitor will start immediately and is terminated by clicking Abort Monitor.
Query Plan
SQL Collector for Monitor Server > Query Plan
SQL statements captured in the SQL Collector for Monitor Server modules are displayed with corresponding query plan using the sp_showplan stored procedure. If the query plan is not available, then the Query Plan pane window remains blank and the SQL is classified as SQL without Plan
Note: It is not guarantee that using the sp_showplan stored procedure to retrieve the query plan will obtain a query plan that corresponds to the SQL statement.