learnex

 
SQL Server 2008 Performance Tuning

SQL Server 2008 Training Outline

  • Performance Tuning Overview
    • The Performance-Tuning Process
    • Performance vs. Price
    • Performance Baseline
    • Where to Focus Efforts
    • SQL Server Performance Killers
  • System Performance Analysis
    • Performance Monitor Tool
    • Dynamic Management Views
    • Hardware Resource Bottlenecks
    • Memory Bottleneck Analysis
    • Memory Bottleneck Resolutions
    • Disk Bottleneck Analysis
    • Disk Bottleneck Resolutions
    • Processor Bottleneck Analysis
    • Processor Bottleneck Resolutions
    • Network Bottleneck Analysis
    • Network Bottleneck Resolutions
    • SQL Server Overall Performance
    • Creating a Baseline
    • System Behavior Analysis Against Baseline
  • SQL Query Performance Analysis
    • The SQL Profiler Tool
    • Trace Automation
    • Combining Trace and Performance Monitor Output
    • SQL Profiler Recommendations
    • Query Performance Metrics Without Profiler
    • Costly Queries
    • Execution Plans
    • Query Cost
  • Index Analysis
    • What Is an Index?
    • Index Design Recommendations
    • Clustered Indexes
    • Nonclustered Indexes
    • Clustered vs. Nonclustered Indexes
    • Advanced Indexing Techniques
    • Special Index Types
    • Additional Characteristics of Indexes
  • Database Engine Tuning Advisor
    • Database Engine Tuning Advisor Mechanisms
    • Database Engine Tuning Advisor Examples
    • Database Engine Tuning Advisor Limitations
  • Bookmark Lookup Analysis
    • Purpose of Bookmark Lookups
    • Drawbacks of Bookmark Lookups
    • Analyzing the Cause of a Bookmark Lookup
    • Resolving Bookmark Lookups
  • Statistics Analysis
    • The Role of Statistics in Query Optimization
    • Statistics on an Indexed Column
    • Statistics on a Nonindexed Column
    • Analyzing Statistics
    • Statistics Maintenance
    • Analyzing the Effectiveness of Statistics for a Query
    • Recommendations
  • Fragmentation Analysis
    • Causes of Fragmentation
    • Fragmentation Overhead
    • Analyzing the Amount of Fragmentation
    • Fragmentation Resolutions
    • Significance of the Fill Factor
    • Automatic Maintenance
  • Execution Plan Cache Analysis
    • Execution Plan Generation
    • Execution Plan Caching
    • Components of the Execution Plan
    • Aging of the Execution Plan
    • Analyzing the Execution Plan Cache
    • Execution Plan Reuse
    • Query Plan Hash and Query Hash
    • Execution Plan Cache Recommendations
  • Stored Procedure Recompilation
    • Benefits and Drawbacks of Recompilation
    • Identifying the Statement Causing Recompilation
    • Analyzing Causes of Recompilation
    • Avoiding Recompilations
  • Query Design Analysis
    • Query Design Recommendations
    • Operating on Small Result Sets
    • Using Indexes Effectively
    • Avoiding Optimizer Hints
    • Using Domain and Referential Integrity
    • Avoiding Resource-Intensive Queries
    • Reducing the Number of Network Round-Trips
    • Reducing the Transaction Cost
  • Blocking Analysis
    • Blocking Fundamentals
    • Understanding Blocking
    • Database Locks
    • Isolation Levels
    • Effect of Indexes on Locking
    • Capturing Blocking Information
    • Blocking Resolutions
    • Recommendations to Reduce Blocking
    • Automation to Detect and Collect Blocking Information
  • Deadlock Analysis
    • Deadlock Fundamentals
    • Using Error Handling to Catch a Deadlock
    • Deadlock Analysis
    • Avoiding Deadlocks
  • Cursor Cost Analysis
    • Cursor Fundamentals
    • Cursor Cost Comparison
    • Default Result Set
    • Analyzing SQL Server Overhead with Cursors
    • Cursor Recommendations
  • Database Workload Optimization
    • Workload Optimization Fundamentals
    • Workload Optimization Steps
    • Capturing the Workload
    • Analyzing the Workload
    • Identifying the Costliest Query
    • Determining the Baseline Resource Use of the Costliest Query
    • Analyzing and Optimizing External Factors
    • Analyzing the Internal Behavior of the Costliest Query
    • Optimizing the Costliest Query
    • Analyzing the Effect on Database Workload
    • Iterating Through Optimization Phases
  • Conclusion