Performance and Tuning: ASE Query Optimiser

Course Overview

You need to know how to tune queries and stored procedures for optimal performance-this course thoroughly covers Adaptive Server Query Optimizer. You will learn techniques to write high performance queries, allowing the optimizer to use the most efficient query plan, and diagnose and correct performance problems with already existing queries. Additionally, you will learn to interpret optimizer statistics and how to use simulated statistics. Finally, you are presented with a benchmarking tuning methodology.

Audience

• SQL Developers and Database Administrators (involved in tuning queries)
• Software Covered Adaptive Server Enterprise 12.2

Skills Gained

• Use Sybase utilities to troubleshoot query performance
• Determine how the optimizer chooses access methods (indexes, table scans, parallel vs. serial)
• Write queries which help the Optimizer choose the most efficient query plan
• Interpret optimizer statistics and how to use simulated statistics, when necessary
• Tune insert, update, and delete operations
• Optimize stored procedure performance
• Work with different locking schemes to promote better concurrency

Prerequisites

• Fast Track to Adaptive Server Enterprise
• Advanced Transact-SQL

Course Outline

• The query optimizer
• Showplan and other Sybase utilities
• Optimizing joins
• Optimizer statistics
• Tuning insert, update, and delete operations
• Parallel query plans
• Using indexes to reduce I/O
• Locking and concurrency issues
• Stored procedures

Performance and Tuning: ASE Query Optimiser / EDB631