Goals

  • 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 optimization steps on the Optimizer

Audience

  • Developer
  • Developer Consultant

Prerequisites

Essential

Recommended

  • None

Course based on software release

  • SAP ASE 16.0

Content

  • Principles of Performance and Tuning
    • Defining Performance and Tuning Terms
    • Defining Tuning Methodology
  • Query Processing and Execution
    • Identifying the Client and Server Relationship
    • Defining the Query Scheduling Process
    • 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
    • Designing an Index
    • Creating Function-Based Indexes
    • Defining an Index Partitioning Strategy
  • Optimizer Statistics
    • Creating Optimizer Statistics
    • Examining Optimizer Statistics
    • Managing Optimizer Statistics
    • Tuning Statistic Maintenance
  • Query Optimization
    • Identifying the Phases of the Optimization Process
    • Controlling the Optimizer
    • Determining Compatibility Issues
  • Access Method Optimization
    • Optimizing Access Methods
    • Determining Query Strategies
    • Estimating the Costs of Queries
  • Joins
    • Analyzing Joins
    • Tuning Joins
  • Parallelism
    • Examining Parallel Query Plans
    • Configuring Parallelism
    • Examining Runtime Adjustments
    • Troubleshooting Parallelism Issues
  • Additional Optimization Techniques
    • Using Optimization Techniques
    • Defining Optimization Components
    • Optimizing Operators
    • Using Aggregate Processing
  • Data Modifications
    • Inserting Tables
    • Deleting 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