Duration
5 days
What you will learn
- Learn how to tune ASE database for optimal performance and explore ASE architecture in depth.
Goals
- Discuss tradeoffs involved in performance tuning
- Use sp_sysmon and other utilities to tune the server for performance gains
- Properly configure memory for optimal performance
- Manage multiple engines
- Configure, tune, and size data, procedure, and partitioned caches
- Identify and correct problems in database physical design, including table partitions
- Use statistics to manage space on DOL tables and indexes
- Choose the appropriate table locking scheme for a given application
- Reconfigure Adaptive Server dynamically
- Improve performance of tempdb by sizing, placement, and solving locking
Audience
- Database Administrator
- System Administrator
Prerequisites
Essential
- EDB358 or equivalent experience
Recommended
- None
Course based on software release
- SAP ASE 15.7
Course Topics
- Principles of Performance and Tuning
- Defining Performance and Tuning Principles
- Tuning with Benchmarks
- Architectural Overview
- Outlining the Adaptive Server Architecture
- Describing the Task Execution Process in ASE
- The Tuning Toolbox
- Using client apps to characterize system performance
- Interpreting the output of set commands
- Analyzing Server Behavior with MDA Tables and QPM
- Using procedures to monitor the server
- Locking Principles
- Configuring a Locking Scheme
- Managing Contention
- Fragmentation
- Identifying Fragmentation Causes and Prevention
- Diagnosing and Resolving Fragmentation
- Multiple Engines
- Comparing the Process Kernel and Threaded Kernel
- Describing the Process Kernel
- Describing the Threaded Kernel
- Monitoring Spinlocks and Contention
- Named Caches
- Analyzing Named Cache Concepts and Behavior
- Configuring Named Caches
- Configuring Large I/O
- Monitoring and Tuning Caches
- Configuring Metadata Caches
- Specialty Cache Settings
- Changing the MRU - LRU rules
- Controlling Asynchronous Prefetch
- Using Cache Partitioning
- Procedure Cache and Statement Cache Tuning
- Outlining Procedure Cache
- Utilizing Statement Cache
- Device Usage
- Examining Database Space Usage
- Tuning and Troubleshooting I/O Issues
- Tuning Temporary Storage
- Table Partitioning
- Partitioning Tables
- Identifying Benefits of Partitioning
- Optimizer Statistics
- Viewing Optimizer Statistics
- Creating, Updating, and Tuning Optimizer Statistics
- Upgrading from Prior Versions
- Parallelism
- Outlining Parallelism Concepts and Syntax
- Monitoring Parallel Access
- Executing Parallel Sort
- Logical Process Manager
- Configuring Execution Classes
- Binding Objects and Precedence
- Optimizing Performance Using the Logical Process Manager
- The Bulk Copy Program (bcp)
- Tuning bcp for Improved Performance
Related Curricula
- SAP ASE Administration
- Sybase Adaptive Server Enterprise (ASE) - Administration
- SAP Sybase ASE Administration
Programme scope, delivery arrangements and suitability are confirmed during enquiry.