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