Performance and Tuning: Sybase Adaptive Server Enterprise 16 Query Optimization (ASE4)

This course teaches practical query and stored procedure tuning to reach optimal application performance. You will learn how the server processes SQL, how query scheduling and processing work, and how the optimizer uses statistics to pick efficient execution plans.

Hands-on exercises show how to use Sybase diagnostic tools and commands to find why queries are slow and how to accelerate them. You will practice query acceleration techniques, learn to read optimizer statistics, and create simulated statistics for better planning.

Location, current course term

Contact us

Custom Customized Training (date, location, content, duration)

The course:

Hide detail
  • Principles of Performance and Tuning
    1. Define performance and tuning terminology
    2. Describe a tuning methodology
  • Query Processing and Execution
    1. Identify the client and server relationship
    2. Describe the query scheduling process
    3. Explain the optimization process
  • The Tuning Toolbox
    1. Use diagnostic tools for tuning
    2. Use non-session tools for tuning
    3. Use syntax commands for tuning
  • Table Structures
    1. Identify table structures
    2. Partition tables
  • Index Structures
    1. Design an index
    2. Create function-based indexes
    3. Define an index partitioning strategy
  • Optimizer Statistics
    1. Create optimizer statistics
    2. Examine optimizer statistics
    3. Manage optimizer statistics
    4. Tune statistic maintenance
  • Query Optimization
    1. Identify phases of the optimization process
    2. Control the optimizer
    3. Diagnose compatibility issues
  • Access Method Optimization
    1. Optimize access methods
    2. Determine query strategies
    3. Estimate query costs
  • Joins
    1. Analyze joins
    2. Tune joins
  • Parallelism
    1. Examine parallel query plans
    2. Configure parallelism
    3. Review runtime adjustments
    4. Troubleshoot parallelism issues
  • Additional Optimization Techniques
    1. Apply optimization techniques
    2. Define optimization components
    3. Optimize operators
    4. Use aggregate processing
  • Data Modifications
    1. Insert into tables
    2. Delete from tables
    3. Update tables and indexes
  • Stored Procedure Optimization
    1. Optimize stored procedures
    2. Troubleshoot stored procedure issues
    3. Cost temporary tables in stored procedures
    4. Tune stored procedures
  • Optimizer Statistics Tuning
    1. Tune column statistics
    2. Create simulated statistics
  • Abstract Plans
    1. Define abstract plans
    2. Create abstract plans
    3. Tune abstract plans
  • Performance Monitoring
    1. Force the optimizer
    2. Monitor query plan performance
Assumed knowledge:
Completion of Fast Track to ASE and System and Database Administration ASE, or equivalent experience writing queries and stored procedures.
Schedule:
5 days (9:00 AM - 5:00 PM )
Education Partner:
MIBCON DIMA s.r.o.
Language: