PERFORMANCE AND TUNING: CONFIGURING ADAPTIVE SERVER ENTERPRISE 15.7
Duration: 5 days
Goals:
1. Discuss tradeoffs involved in performance tuning
2. Use sp_sysmon and other utilities to tune the server for performance gains
3. Properly configure memory for optimal performance
4. Manage multiple engines
5. Configure, tune, and size data, procedure, and partitioned caches
6. Identify and correct problems in database physical design, including table partitions
7. Use statistics to manage space on DOL tables and indexes
8. Choose the appropriate table locking scheme for a given application
9. Reconfigure Adaptive Server dynamically
10. Improve performance of tempdb by sizing, placement, and solving locking
Content:
1. Principles of Performance and Tuning
Defining Performance and Tuning Principles
Tuning with Benchmarks
2. Architectural Overview
Outlining the Adaptive Server Architecture
Describing the Task Execution Process in ASE
3. 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
4. Locking Principles
Configuring a Locking Scheme
Managing Contention
5. Fragmentation
Identifying Fragmentation Causes and Prevention
Diagnosing and Resolving Fragmentation
6. Multiple Engines
Comparing the Process Kernel and Threaded Kernel
Describing the Process Kernel
Describing the Threaded Kernel
Monitoring Spinlocks and Contention
7. Named Caches
Analyzing Named Cache Concepts and Behavior
Configuring Named Caches
Configuring Large I/O
Monitoring and Tuning Caches
Configuring Metadata Caches
8. Specialty Cache Settings
Changing the MRU - LRU rules
Controlling Asynchronous Prefetch
Using Cache Partitioning
9. Procedure Cache and Statement Cache Tuning
Outlining Procedure Cache
Utilizing Statement Cache
10. Device Usage
Examining Database Space Usage
Tuning and Troubleshooting I/O Issues
Tuning Temporary Storage
11. Table Partitioning
Partitioning Tables
Identifying Benefits of Partitioning
12. Optimizer Statistics
Viewing Optimizer Statistics
Creating, Updating, and Tuning Optimizer Statistics
Upgrading from Prior Versions
13. Parallelism
Outlining Parallelism Concepts and Syntax
Monitoring Parallel Access
Executing Parallel Sort
14. Logical Process Manager
Configuring Execution Classes
Binding Objects and Precedence
Optimizing Performance Using the Logical Process Manager
15. The Bulk Copy Program (bcp)
Tuning bcp for Improved Performance