Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization

Book description

Dive deep into the internals of query tuning and optimization in SQL Server 2005 with this comprehensive reference. Understanding the internals of SQL Server helps database developers and administrators to better create, access, and effectively process information from enterprise data. Written by experts on SQL Server, this volume from the Inside Microsoft SQL Server series of books focuses on query tuning and optimization. You'll take an in-depth look at the best ways to make queries more efficient and effective, while maximizing existing resources. Includes extensive code samples and table examples to help database developers and administrators understand the intricacies and help promote mastery of query tuning and optimization.

Table of contents

  1. Copyright
  2. Foreword
  3. Acknowledgments
  4. Introduction
  5. A Performance Troubleshooting Methodology
    1. Factors That Impact Performance
    2. Troubleshooting Overview
    3. Summary
  6. Tracing and Profiling
    1. SQL Trace Architecture and Terminology
    2. Security and Permissions
    3. Getting Started: Profiler
    4. Server-Side Tracing and Collection
    5. Troubleshooting and Analysis with Traces
    6. Tracing Considerations and Design
    7. Auditing: SQL Server’s Built-in Traces
    8. Summary
  7. Query Execution
    1. Query Processing and Execution Overview
    2. Reading Query Plans
    3. Analyzing Plans
    4. Summary
  8. Troubleshooting Query Performance
    1. Compilation and Optimization
    2. Detecting Problems in Plans
    3. Monitoring Query Performance
    4. Query Improvements
    5. Query Processing Best Practices
    6. Summary
  9. Plan Caching and Recompilation
    1. The Plan Cache
    2. Caching Mechanisms
    3. Plan Cache Internals
    4. Objects in Plan Cache: The Big Picture
    5. Multiple Plans in Cache
    6. When to Use Stored Procedures and Other Caching Mechanisms
    7. Troubleshooting Plan Cache Issues
    8. Summary
  10. Concurrency Problems
    1. New Tools for Troubleshooting Concurrency
    2. Troubleshooting Locking
    3. Troubleshooting Blocking
    4. Troubleshooting Deadlocking
    5. Troubleshooting Row-Versioning–Based Snapshot-Based Isolation Levels
    6. Summary
  11. Additional Resources and References
  12. About the Authors
    1. Kalen Delaney
    2. Sunil Agarwal
    3. Craig Freedman
    4. Adam Machanic
    5. Ron Talmage
  13. Additional Resources for Developers: Advanced Topics and Best Practices
  14. Additional SQL Server Resources for Developers
  15. Prepare for Certification with Self-Paced Training Kits: Official Exam Prep Guides—Plus Practice Tests
  16. 2007 Microsoft® Office System Resources for Developers and Administrators
  17. Additional Resources for Developers from Microsoft Press
    1. Visual Basic 2005
    2. Visual C# 2005
    3. Web Development
    4. Data Access
    5. SQL Server 2005
    6. Other Developer Topics
  18. More Great Developer Resources from Microsoft Press
    1. Developer Step by Step
    2. Developer Reference
    3. Focused Topics
  19. Index

Product information

  • Title: Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization
  • Author(s): Kalen Delaney, Sunil Agarwal, Craig Freedman, Ron Talmage, Adam Machanic
  • Release date: September 2007
  • Publisher(s): Microsoft Press
  • ISBN: 9780735621961