SAP Sybase ASE - training


PERFORMANCE AND TUNING: ADAPTIVE SERVER ENTERPRISE 16 QUERY OPTIMIZATION


Duration: 5 days

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

Content:
1. Principles of Performance and Tuning
   Defining Performance and Tuning Terms
   Defining Tuning Methodology
2. Query Processing and Execution
   Identifying the Client and Server Relationship
   Defining the Query Scheduling Process
   Defining the Optimization Process
3. The Tuning Toolbox
   Using Diagnostic Tools for Tuning
   Using Non-Session Tools for Tuning
   Using Syntax Commands for Tuning
4. Table Structures
   Identifying Table Structures
   Partitioning Tables
5. Index Structures
   Designing an Index
   Creating Function-Based Indexes
   Defining an Index Partitioning Strategy
6. Optimizer Statistics
   Creating Optimizer Statistics
   Examining Optimizer Statistics
   Managing Optimizer Statistics
   Tuning Statistic Maintenance
7. Query Optimization
   Identifying the Phases of the Optimization Process
   Controlling the Optimizer
   Determining Compatibility Issues
8. Access Method Optimization
   Optimizing Access Methods
   Determining Query Strategies
   Estimating the Costs of Queries
9. Joins
   Analyzing Joins
   Tuning Joins
10. Parallelism
   Examining Parallel Query Plans
   Configuring Parallelism
   Examining Runtime Adjustments
   Troubleshooting Parallelism Issues
11. Additional Optimization Techniques
   Using Optimization Techniques
   Defining Optimization Components
   Optimizing Operators
   Using 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
   Defining Abstract Plans
   Creating Abstract Plans
   Tuning Abstract Plans
16.Performance Monitoring
   Forcing the Optimizer
   Monitoring Query Plan Performance