Performance and Tuning: Adaptive Server Enterprise 15.7 Query Optimization

Duration: 5 days
  • Explain optimization tools and their use
  • Discuss the steps involved in executing code from input to return of result set
  • Use code to pass hints to the Optimizer
  • Perform tasks to evaluate the behavior of the optimizer
  • Implement the most efficient data-change and data-retrieval T- SQL code in your queries and stored procedures
  • Force optimisation steps on the Optimizer

This course is geared towards developers and developer consultants who have the knowledge similar as explain on the SAP-EDB116 course

Please find the course objectives below:

Principles of Performance and Tuning 

  • Defining Performance and Tuning Terms 
  • Defining Tuning Effort

Query Processing and Execution 

  • Identifying the Client/Server Relationship 
  • Defining the Optimization Process

The Tuning Toolbox 

  • Using Diagnostic Tools for Tuning 
  • Using Non-Session Tools for Tuning 
  • Using Syntax Commands for Tuning

Table Structures 

  • Identifying Table Structures 
  • Partitioning Tables

Index Structures 

  • Determining Index Design Requirements 
  • Creating Function-Based Indexes 
  • Defining an Index Partitioning Strategy

Optimizer Statistics 

  • Creating Optimizer Statistics 
  • Viewing Optimizer Statistics 
  • Managing Optimizer Statistics 

Query Optimization 

  • Identifying the Phases of the Optimization Process 
  • Controlling the Optimizer 

Access Method Optimization 

  • Optimizing Access Methods 
  • Determining Query Strategies 
  • Estimating the Costs of Queries

Joins 

  • Analyzing Join Processing 
  • Tuning Joins

Parallelism 

  • Examining Parallel Query Plans 
  • Configuring Parallelism 

Additional Optimization Techniques 

  • Using Optimization Techniques 
  • Defining Optimization Components 
  • Optimizing Operators 
  • Using Aggregate Processing

Data Modifications 

  • Inserting ASE Tables 
  • Deleting ASE Tables 
  • Updating Tables and Indexes

Stored Procedure Optimization 

  • Optimizing Stored Procedures 
  • Troubleshooting Stored Procedure Issues 
  • Costing Temporary Tables in Stored Procedures 
  • Tuning Stored Procedures

Optimizer Statistics Tuning 

  • Tuning Column Statistics 
  • Creating Simulated Statistics

Abstract Plans 

  • Defining Abstract Plans 
  • Creating Abstract Plans 
  • Tuning Abstract Plans

Performance Monitoring 

  • Forcing the Optimizer 
  • Monitoring Query Plan Performance
For an onsite course please contact us