Performance and Tuning: Adaptive Server Enterprise 16.0 Query Optimization



In this course, you will learn how to measure the performance of T-SQL queries and tune your query development for the specific requirements of your business. Take advantage of the monitoring and evaluation ASE tools, and interpret their diagnoses into specific plans of action.

This course is based on software release SAP ASE 16.0.


  • Developers
  • Developer consultants


EDB116 or equivalent knowledge

Learning Objectives

  • Optimization tools and their use
  • 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 optimization steps on the Optimizer

1. Principles of Performance and Tuning

  • Performance and Tuning Terms
  • Tuning Methodology

2. Query Processing and Execution

  • Client and Server Relationship
  • Query Scheduling Process
  • Optimization Process

3. The Tuning Toolbox

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

4. Table Structures

  • Table Structures
  • Partitioning Tables

5. Index Structures

  • Designing an Index
  • Creating Function-Based Indexes
  • Index Partitioning Strategy

6. Optimizer Statistics

  • Creating Optimizer Statistics
  • Optimizer Statistics
  • Managing Optimizer Statistics
  • Tuning Statistic Maintenance

7. Query Optimization

  • Phases of the Optimization Process
  • Controlling the Optimizer
  • Compatibility Issues

8. Access Method Optimization

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

9. Joins

  • Analyzing Joins
  • Tuning Joins

10. Parallelism

  • Parallel Query Plans
  • Configuring Parallelism
  • Runtime Adjustments
  • Troubleshooting Parallelism Issues

11. Additional Optimization Techniques

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

12. Data Modifications

  • Inserting Tables
  • Deleting Tables
  • Updating Tables and Indexes

13. Stored Procedure Optimization

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

14. Optimizer Statistics Tuning

  • Tuning Column Statistics
  • Creating Simulated Statistics

15. Abstract Plans

  • Abstract Plans
  • Creating Abstract Plans
  • Tuning Abstract Plans

16. Performance Monitoring

  • Forcing the Optimizer
  • Monitoring Query Plan Performance