Professional SQL Server® 2005 Performance Tuning

Table of contents

1. Copyright
2. About the Authors
3. Credits
4. Acknowledgments
5. Introduction
6. Finding Bottlenecks when Something's Wrong
   1. Performance Tuning
      1. Art or Science?
      2. The Science of Performance Tuning
      3. Performance Tuning Applied
      4. Summary
   2. Monitoring Server Resources with System Monitor
      1. Why Might I Need System Monitor?
      2. When Should I Use System Monitor?
      3. Performance Monitor Overview
      4. Getting Started with System Monitor
      5. What's the Impact of Running System Monitor?
      6. How Much Data Will System Monitor Generate?
      7. Resource Utilization
      8. Identifying Bottlenecks
      9. Using System Monitor Proactively
      10. Running System Monitor on 64-bit Systems
      11. Combining System Monitor Logs and SQL Profiler Traces
      12. Monitoring Remote Servers
      13. Best Practices for System Monitor
      14. My System Monitor Counters Are Missing — What Should I Do?
      15. Built-in Log Management Tools
      16. Analyzing Log Data
      17. Summary
   3. Monitoring SQL Server Resources with System Monitor
      1. The Beginning
      2. Types of Performance Problems
      3. Types of Bottlenecks
      4. Memory Bottlenecks
      5. CPU Bottlenecks
      6. Disk Bottlenecks
      7. Monitoring Database Mirroring Performance
      8. Monitoring Wait Statistics
      9. Typical Performance Problems
      10. Using SQL Server to Analyze Performance Logs
      11. Combining Performance Monitor Logs and SQL Profiler Trace
      12. Summary
   4. SQL Server Wait Types
      1. SQL Server Waits
      2. Architecture
      3. Common or Noteworthy Resource Wait Types
      4. How to Track Waits
      5. Locking and Blocking
      6. Summary
   5. Finding Problem Queries with SQL Profiler
      1. Preparations for Setting Up a Trace
      2. Capturing Blocking Events
      3. Capturing Showplan XML Data
      4. Capturing Deadlock Graphs
      5. Identifying Long-Running Queries Using SQL Profiler
      6. Tracing Costly Queries by Using Profiler to Generate Server-Side Trace Code
      7. Correlating a Profiler Trace with System Monitor Performance Counter Data
      8. Summary
7. Removing Bottlenecks with Tuning
   1. Choosing and Configuring Hardware
      1. Server Bottlenecks
      2. Configuring the Server
      3. Summary
   2. Tuning SQL Server Configuration
      1. Considerations before Adjusting Server Level Settings
      2. Inspecting Current SQL Server Settings
      3. Important Server Settings: CPU, Memory, and I/O
      4. Summary
   3. Tuning the Schema
      1. Data Quality
      2. Data Performance
      3. Summary
   4. Tuning T-SQL
      1. Opening Move: The Optimizer's Gameplan
      2. Middle Game: Gathering the Facts
      3. End Game: Performance Tuning T-SQL
      4. Tuning T-SQL with a New Approach
      5. Tuning the T-SQL Predicate
      6. Tuning T-SQL to Use Indexes
      7. Tuning T-SQL Common Patterns or Idioms
      8. Tuning the T-SQL Statement with Hints
      9. Tuning for Deadlocking and Blocking
      10. Simulated Stress Testing for Query Plans
      11. Summary
8. Preventative Measures and Baselining Performance with Tools
   1. Capturing, Measuring, and Replaying a Workload Using SQL Profiler
      1. Capturing Workloads for Replay
      2. Measuring Workload Performance
      3. Replaying a Workload
      4. Summary
   2. Tuning Indexes
      1. Sample Database
      2. Section 1: Indexing Review
      3. Section 2: Tuning with DTA
      4. Section 3: Index Maintenance
      5. Section 4: Partitioned Tables and Indexes
      6. Summary
   3. How Fast and Robust Is Your Storage?
      1. Performance Testing, Stress Testing, and Real-Life Performance
      2. Storage Performance
      3. Storage Reliability
      4. Summary
   4. SQL Server 2005 Performance Dashboard Reports
      1. Supportability
      2. Performance Dashboard Reports
      3. Related Links
      4. Summary
9. Roadmap to Server Performance
   1. Best Practices for Designing for Performance from the Start
      1. Understanding Your Performance Requirements
      2. Evaluating Schemas for Performance Unknowns
      3. Evaluating Indexes for Performance
      4. Benchmarking for Evaluation
      5. Communicating Performance Issues
      6. Summary
   2. Successful Deployment Strategies
      1. Sizing
      2. Additional Features
      3. High Availability and Disaster Recovery
      4. Load Testing
      5. Managing Change
      6. Dealing with Large Tables
      7. Tuning Using SQL Server Profiler
      8. Tuning Using the Database Engine Tuning Advisor
      9. Consequences of Incorrectly Sizing the Production Environment
      10. Schema Issues in Production
      11. Avoiding Changing Code to Fix Issues
      12. Summary
10. Index