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

  • EDB358or 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