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

Content

• 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