Cover image for SQL Server 2012 Query Performance Tuning, Third Edition

SQL Server 2012 Query Performance Tuning, Third Edition

Author Grant Fritchey
Release Date: 2012/06/01
ISBN: 9781430242031
Topic:
0%
27
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

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
    18.224.53.202