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

Description

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. 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