SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query Performance

✍ Scribed by Grant Fritchey

• Publisher: Apress
• Year: 2018
• Tongue: English
• Leaves: 947
• Edition: 5th ed.
• Category: Library

✦ Synopsis

Identify and fix causes of poor performance. You will learn Query Store, adaptive execution plans, and automated tuning on the Microsoft Azure SQL Database platform. Anyone responsible for writing or creating T-SQL queries will find valuable the insight into bottlenecks, including how to recognize them and eliminate them.

This book covers the latest in performance optimization features and techniques and is current with SQL Server 2017. If your queries are not running fast enough and you’re tired of phone calls from frustrated users, then this book is the answer to your performance problems.

SQL Server 2017 Query Performance Tuning is about more than quick tips and fixes. You’ll learn to be proactive in establishing performance baselines using tools such as Performance Monitor and Extended Events. You’ll 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. The goal is to head off trouble before it occurs.

What You'll Learn

• Use Query Store to understand and easily change query performance
  
• 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 to minimize performance risk
  
• Design in the performance that you need through careful query and index design
  
• Utilize the latest performance optimization features in SQL Server 2017
  
• Protect query performance during upgrades to the newer versions of SQL Server
  

Who This Book Is For

Developers and database administrators with responsibility for application performance in SQL Server environments. Anyone responsible for writing or creating T-SQL queries will find valuable the insight into bottlenecks, including how to recognize them and eliminate them.

✦ Table of Contents

Front Matter ....Pages i-xxxi
SQL Query Performance Tuning (Grant Fritchey)....Pages 1-22
Memory Performance Analysis (Grant Fritchey)....Pages 23-50
Disk Performance Analysis (Grant Fritchey)....Pages 51-68
CPU Performance Analysis (Grant Fritchey)....Pages 69-85
Creating a Baseline (Grant Fritchey)....Pages 87-102
Query Performance Metrics (Grant Fritchey)....Pages 103-130
Analyzing Query Performance (Grant Fritchey)....Pages 131-183
Index Architecture and Behavior (Grant Fritchey)....Pages 185-230
Index Analysis (Grant Fritchey)....Pages 231-271
Database Engine Tuning Advisor (Grant Fritchey)....Pages 273-291
Query Store (Grant Fritchey)....Pages 293-318
Key Lookups and Solutions (Grant Fritchey)....Pages 319-336
Statistics, Data Distribution, and Cardinality (Grant Fritchey)....Pages 337-402
Index Fragmentation (Grant Fritchey)....Pages 403-450
Execution Plan Generation (Grant Fritchey)....Pages 451-470
Execution Plan Cache Behavior (Grant Fritchey)....Pages 471-509
Parameter Sniffing (Grant Fritchey)....Pages 511-528
Query Recompilation (Grant Fritchey)....Pages 529-568
Query Design Analysis (Grant Fritchey)....Pages 569-605
Reduce Query Resource Use (Grant Fritchey)....Pages 607-629
Blocking and Blocked Processes (Grant Fritchey)....Pages 631-698
Causes and Solutions for Deadlocks (Grant Fritchey)....Pages 699-719
Row-by-Row Processing (Grant Fritchey)....Pages 721-752
Memory-Optimized OLTP Tables and Procedures (Grant Fritchey)....Pages 753-781
Automated Tuning in Azure SQL Database and SQL Server (Grant Fritchey)....Pages 783-823
Database Performance Testing (Grant Fritchey)....Pages 825-837
Database Workload Optimization (Grant Fritchey)....Pages 839-881
SQL Server Optimization Checklist (Grant Fritchey)....Pages 883-909
Back Matter ....Pages 911-932

✦ Subjects

Computer Science; Database Management; Microsoft and .NET