ASE Performance and Tuning

Length: 5 Days
Prerequisites:  Solid understand of SQL, Prior knowledge and experience working with Sybase ASE. Our "Introduction to SQL" and
Introduction to Sybase ASE provide a solid base for this course


Overview

To get the best performance from a high-performance vehicle, you'll likely need to tune the engine and application. Before you can begin
tuning a high-performance engine you need to understand its internals and how they work. what to tune and when to tune it. This course
teaches students how Adaptive Server a high-performance engine works. Topics cover when and what to tune to achieve the best database
performance.

Key Benefits:  When you complete this course you learn how to Analyze and Solve Subtle Performance Problems. You will leave ready to
work with a Performance Team. This course also provides a solid understanding of Sybase Internals.

Outline


Chapter 1. Introduction to Tuning

•  Defining Performance
•  RDBMS
• Setting Expectations
• Concurrency
• Defining and Tracking Down Bottlenecks
• Tuning

Chapter 2. Physical Issues

• Normalization
• Performance and Tuning Issues in Converting a Logical Design to a Physical Design
• Tuning Through Denormalization
• Design Tradeoffs
• Views

Chapter 3.  Physical Storage Structures

• Allocation pages and Object Allocation Map
• Data Pages
• Text and Image Pages
• Indexes and the B-Tree Structure
• Updates and Performance
• Using Segments
• Table Partitioning
• VLDB Design Issues: Performance, Recovery, Physical Resource Dissemination

Chapter 4. Physical Design

• Storage Structures
• Space Allocation
• Page Utilization
• Determining Table Size
• Page Splitting
• Object Placement and Segments
• Heap Table Partitioning

Chapter 5. Design Indexes for Performance

• Why Use Indexes?
• Indexes and Performance
• Index Selection
• Clustered & Non-clustered
• Evaluating Index Usefulness
• Index Design Guidelines

Chapter 6. Techniques For Determining Query Paths

• Basic Analysis and Optimization
• Showplan
• Statistics IO and Time
• Analyzing Query Optimization
• Analyzing 301/310 Output

 

 Chapter 7.  tembdb Performance

• Alternative Storage Devices
• Named Cache for tembdb
• Locking and tempdb
• Cursor Performance
• Replacing Cursors

Chapter 8.  Index Selection

• Clustered & Non-clustered Index
• Indexing for Multiple Queries
• Ors  Index Covering
• Index Statistics
• Understanding the Distribution Page
• Index Design Guidelines

Chapter 9.  Optidag Programming

• What optdiag Does
• Tables that Store Statistics
• Viewing Statistics
• Histogram Displays
• Changing Statistics
• Editing Histograms
• Simulated Statistics
• Effects of SQL Statement on Statistics

Chapter 10. Cache Strategies

• Named Caches
• Buffer Pools
• User Log Cache
• Log I/O size
• Fetch Strategies
• Metadata Cache
• Guidelines for Cache Configuration
• Metadata Cache

Chapter 11.  Understand the Query Optimizer

• Optimizing Steps
• Reading the Query Plans
• Overriding the Optimizer
• Review Optimization Methods
• DBCC trace flag Usage
• Understanding DBCC Output

Chapter 12.  Parrallel Query Processing

• What is Parallel Query Processing?
• Configuring Parallel Query Processing
• Impacts of Parallel Query Processing
• Parallel Query Optimization
• Parallel Sorting

Chapter 13.  Locking

• Consistency Levels
• Lock Isolation Levels
• Lock Granularity
• Types of Page Locks
• Data Locking Mechanisms: Allpages, Datapages, Datarows, max_row_per_page

Chapter 14.  Deadlock Detection & Avoidance

• Understanding Deadlocks
• Gathering Deadlock Information
• Learn How to Analyze Data
• Deadlock Avoidance
• Deadlock Phases
• Deadlock Output

Chapter 15.  Configuration Issues

• Tuning Memory & Data Cache
• Sizing Procedure Cache
• Network Packet Size
• Speeding Up Index Creation
• SMP Architecture

Chapter 16.  Performance Metrics

• What needs to be Measured
• Set of Performance Metrics for a Specific Platform
• Performance Monitoring Tools

Chapter 17.  Configuration

• Tuning Memory & Data Cache
• Sizing Procedure Cache
• Network Packet Size
• Speeding Up Index Creation
• SMP Architecture

Chapter 18. Tuning Auditing System

• How to

Chapter 19. Abstract Query

• BCP
• Execution Priority
• ODBC

Chapter 20. What Abstract Plans Are

• Relationship between query text & query plans
• Full vs. Partial Plans
• How Abstract Plans Are Associated With Queries
• Managing Abstract Plans

Back to Course Listing