SQL Server Execution Plans

If a query is performing poorly, and you can’t understand why, then that query’s execution plan will tell you not only what data set is coming back, but also what SQL Server did, and in what order, to get that data. It will reveal how the data was retrieved, and from which tables and indexes, what types of joins were used, at what point filtering, sorting and aggregation occurred, and a whole lot more. These details will often highlight the likely source of any problem.

I wrote this book with the singular goal of teaching you how to read SQL Server Execution plans

It will explain, among many other things, the following:

• How to capture execution plans using manual and automatic methods
• A documented method for reading and interpreting execution plans
• How common SQL Server objects, such as indexes, views, stored procedures, and so on, appear in execution plans
• How to control execution plans with hints and plan guides, and why this is a double-edged sword
• How the Query Store works with, and collects data on, execution plans

With this knowledge, you’ll have everything you need to read the execution plan, for any query of your own, regardless of complexity, and understand what it does and what is causing the bad performance. It is still your job to work out how best to fix it, but your new understanding of execution plans will give a much better chance of success!

Table of Contents

Chapter 1: Introducing the Execution Plan

Chapter 2: Getting Started Reading Plans

Chapter 3: Data Reading Operators

Chapter 4: Joining Data

Chapter 5: Sorting and Aggregating Data

Chapter 6: Execution Plans for Data Modifications

Chapter 7: Execution Plans for Common T-SQL Statements

Chapter 8: Examining Index Usage

Chapter 9: Exploring Plan Reuse

Chapter 10: Controlling Execution Plans with Hints

Chapter 11: Parallelism in Execution Plans

Chapter 12: Batch Mode Processing

Chapter 13: The XML of Execution Plans

Chapter 14: Plans for Special Data Types and Cursors

Chapter 15: Automating Plan Capture

Chapter 16: The Query Store

Chapter 17: SSMS Tools for Exploring Execution Plans

About the Author

Grant Fritchey, Microsoft Data Platform MVP, has more than thirty years experience in IT. That time was spent in technical support, development and database administration. Grant currently works as a Product Advocate at Red Gate Software. Grant writes articles for publication at SQL Server Central and Simple-Talk. He has published books including, “SQL Server Execution Plans” and “SQL Server 2022 Query Performance Tuning.” Grant co-authored the book “Introduction to PostgreSQL” along with Ryan Booz. Grant presents on a variety of topics at user groups and conferences around the world.