You are previewing SQL Server 2012 Query Performance Tuning, Third Edition.
O'Reilly logo
SQL Server 2012 Query Performance Tuning, Third Edition

Book Description

Queries not running fast enough? Tired of the phone calls from frustrated users? Grant Fritchey's book SQL Server 2012 Query Performance Tuning is the answer to your SQL Server query performance problems. The book is revised to cover the very latest in performance optimization features and techniques. It is current with SQL Server 2012. It provides the tools you need to approach your queries with performance in mind.

SQL Server 2012 Query Performance Tuning leads you through understanding the causes of poor performance, how to identify them, and how to fix them. You'll learn to be proactive in establishing performance baselines using tools like Performance Monitor and Extended Events. You'll learn to recognize bottlenecks and defuse them before the phone rings. You'll learn some quick solutions too, but emphasis is on designing for performance and getting it right, and upon heading off trouble before it occurs. Delight your users. Silence that ringing phone. Put the principles and lessons from SQL Server 2012 Query Performance Tuning into practice today.

  • Establish performance baselines and monitor against them

  • Troubleshoot and eliminate bottlenecks that frustrate users

  • Plan ahead to achieve the right level of performance

What you'll learn

  • Establish performance baselines and monitor against them

  • Recognize and eliminate bottlenecks leading to slow performance

  • Deploy quick fixes when needed, following up with long term solutions

  • Implement best-practices in T-SQL so as to minimize performance risk

  • Design in the performance that you need through careful query and index design

  • Take advantage of the very latest performance optimization features in SQL Server 2012

Who this book is for

SQL Server 2012 Query Performance Tuning is aimed at developers and database administrators having responsibility for application performance in SQL Server environments. Programmers and administrators alike will find the book a trove of good insight into bottlenecks, how to recognize them, how to eliminated them. SQL Server 2012 Query Performance Tuning provides the tools and techniques readers need to create good-performing applications that delight their users.

Table of Contents

  1. Title Page
  2. Contents at a glance
  3. Contents
  4. About the Author
  5. About the Technical Reviewer
  6. Acknowledgments
  7. Introduction
  8. CHAPTER 1: SQL Query Performance Tuning
    1. The Performance Tuning Process
    2. Performance vs. Price
    3. Performance Baseline
    4. Where to Focus Efforts
    5. SQL Server Performance Killers
    6. Summary
  9. CHAPTER 2: System Performance Analysis
    1. Performance Monitor Tool
    2. Dynamic Management Objects
    3. Hardware Resource Bottlenecks
    4. Memory Bottleneck Analysis
    5. Additional Memory Monitoring Tools
    6. Memory Bottleneck Resolutions
    7. Disk Bottleneck Analysis
    8. Additional I/O Monitoring Tools
    9. Disk Bottleneck Resolutions
    10. Processor Bottleneck Analysis
    11. Other Tools for Measuring CPU Performance
    12. Processor Bottleneck Resolutions
    13. Network Bottleneck Analysis
    14. Network Bottleneck Resolutions
    15. SQL Server Overall Performance
    16. Considerations for Monitoring Virtual Machines
    17. Creating a Baseline
    18. Summary
  10. CHAPTER 3: SQL Query Performance Analysis
    1. Extended Events Wizard
    2. Extended Events Automation
    3. Extended Events Recommendations
    4. Other Methods for Query Performance Metrics
    5. Costly Queries
    6. Execution Plans
    7. Summary
  11. CHAPTER 4: Index Analysis
    1. What Is an Index?
    2. Index Design Recommendations
    3. Clustered Indexes
    4. Nonclustered Indexes
    5. Clustered vs. Nonclustered Indexes
    6. Advanced Indexing Techniques
    7. Special Index Types
    8. Additional Characteristics of Indexes
    9. Summary
  12. CHAPTER 5: Database Engine Tuning Advisor
    1. Database Engine Tuning Advisor Mechanisms
    2. Database Engine Tuning Advisor Examples
    3. Database Engine Tuning Advisor Limitations
    4. Summary
  13. CHAPTER 6: Lookup Analysis
    1. Purpose of Lookups
    2. Drawbacks of Lookups
    3. Analyzing the Cause of a Lookup
    4. Resolving Lookups
    5. Summary
  14. CHAPTER 7: Statistics Analysis
    1. The Role of Statistics in Query Optimization
    2. Statistics on a Nonindexed Column
    3. Analyzing Statistics
    4. Statistics Maintenance
    5. Statistics Maintenance Status
    6. Analyzing the Effectiveness of Statistics for a Query
    7. Recommendations
    8. Summary
  15. CHAPTER 8: Fragmentation Analysis
    1. Causes of Fragmentation
    2. Fragmentation Overhead
    3. Analyzing the Amount of Fragmentation
    4. Analyzing the Fragmentation of a Small Table
    5. Fragmentation Resolutions
    6. Significance of the Fill Factor
    7. Automatic Maintenance
    8. Summary
  16. CHAPTER 9: Execution Plan Cache Analysis
    1. Execution Plan Generation
    2. Components of the Execution Plan
    3. Aging of the Execution Plan
    4. Analyzing the Execution Plan Cache
    5. Execution Plan Reuse
    6. Ad Hoc Workload
    7. Query Plan Hash and Query Hash
    8. Execution Plan Cache Recommendations
    9. Summary
  17. CHAPTER 10: Query Recompilation
    1. Benefits and Drawbacks of Recompilation
    2. Identifying the Statement Causing Recompilation
    3. Analyzing Causes of Recompilation
    4. Avoiding Recompilations
    5. Summary
  18. CHAPTER 11: Query Design Analysis
    1. Query Design Recommendations
    2. Operating on Small Result Sets
    3. Limit the Number of Columns in select_list
    4. Avoiding Optimizer Hints
    5. Using Domain and Referential Integrity
    6. Avoiding Resource-Intensive Queries
    7. Reducing the Number of Network Round-Trips
    8. Reducing the Transaction Cost
    9. Summary
  19. CHAPTER 12: Blocking Analysis
    1. Blocking Fundamentals
    2. Understanding Blocking
    3. Locks
    4. Lock Operations and Modes
    5. Isolation Levels
    6. Effect of Indexes on Locking
    7. Capturing Blocking Information
    8. Blocking Resolutions
    9. Partition the Contended Data
    10. Recommendations to Reduce Blocking
    11. Automation to Detect and Collect Blocking Information
    12. Summary
  20. CHAPTER 13: Deadlock Analysis
    1. Deadlock Fundamentals
    2. Deadlock Analysis
    3. Avoiding Deadlocks
    4. Summary
  21. CHAPTER 14: Cursor Cost Analysis
    1. Cursor Fundamentals
    2. Default Result Set
    3. Cursor Overhead
    4. Summary
  22. CHAPTER 15: Database Performance Testing
    1. Database Performance Testing
    2. Capturing Data with the Server Side Trace
    3. Distributed Replay for Database Testing
    4. Conclusion
  23. CHAPTER 16: Database Workload Optimization
    1. Workload Optimization Fundamentals
    2. Capturing the Workload
    3. Analyzing the Workload
    4. Identifying the Costliest Query
    5. Analyzing and Optimizing External Factors
    6. Analyzing the Effect on Database Workload
    7. Iterating Through Optimization Phases
    8. Summary
  24. CHAPTER 17: SQL Server Optimization Checklist
    1. Database Design
    2. Query Design
    3. Configuration Settings
    4. Database Administration
    5. Database Backup
    6. Summary
  25. Index