Course announcements

  • 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.

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

  • EDB116 or equivalent knowledge

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