SQL Server Execution Plans

Comments 14

Share to social media

821-ExecutionPlansCover.jpg

Every day, out in the various SQL Server forums, the same types of questions come up again and again: why is this query running slow? Why isn’t my index getting used? And on and on. In order to arrive at the answer you have to ask the same return question in each case: have you looked at the execution plan?” — Grant Fritchey, Author.

Download the eBook (To download the eBook you need to be a member of Simple Talk, the download link will take you to the registration page)

Buy the printed book from Amazon

Download the Source Code

Table of Contents

  • Chapter 01: Execution Plan Basics
  • Chapter 02: Reading Graphical Execution Plans for Basic Queries
  • Chapter 03: Text and XML Execution Plans for Basic Queries
  • Chapter 04: Understanding More Complex Query Plans
  • Chapter 05: Controlling Execution Plans with Hints
  • Chapter 06: Cursor Operations
  • Chapter 07: XML in Execution Plans
  • Chapter 08: Advanced Topics

Why read this book?

Execution plans show you what’s going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including:

  • Which indexes are getting used and where no indexes are being used at all.
  • How the data is being retrieved, and joined, from the tables defined in your query.
  • How aggregations in GROUP BY queries are put together.
  • The anticipated load, and the estimated cost, that all these operations place upon the system.

All this information makes the execution plan a fairly important tool in the tool belt of database administrator, database developers, report writers, developers, and pretty much anyone who writes TSQL to access data in a SQL Server database.

Given the utility and importance of the tool, you’d think there’d be huge swathes of information devoted to this subject. To be sure, fantastic information is available from various sources, but there really isn’t any one place you can go to for focused, practical information on how to use and interpret execution plans.

This is where my book comes in. My goal was to gather as much useful information on execution plans as possible into a single location, and to organize it in such as way that it provided a clear route through the subject, right from the basics of capturing plans, through their interpretation, and then on to how to use them to understand how you might optimize your SQL queries, improve your indexing strategy, and so on.

Specifically, I cover:

  • How to capture execution plans in graphical, as well as text and XML formats
  • A documented method for interpreting execution plans, so that you can create these plans from your own code and make sense of them in your own environment
  • How SQL Server represents and interprets the common SQL Server objects – indexes, views, derived tables etc – in execution plans
  • How to spot some common performance issues such as bookmark lookups or unused/missing indexes
  • How to control execution plans with hints, plans guides and so on, and why this s a double-edged sword
  • How XML code appears in execution plans
  • Advanced topics such as parallelism, forced parameterization and plan forcing.

Along the way, I tackle such topics as SQL Server internals, performance tuning, index optimization and so on. However, my focus is always on the details of the execution plan, and how these issues are manifest in these plans. If you are specifically looking for information on how to optimize SQL, or build efficient indexes, then you need a book dedicated to these topics. However, if you want to understand how these issues are interpreted within an execution plan, then this is the place for you.

Book Details

  • Paperback: 250 pages
  • Publisher: Red Gate Books

<span class="icon--download"></span>Downloads

  • CODE File size:73 KB

About the author

Grant Fritchey

See Profile

Grant Fritchey is a Data Platform MVP with over 30 years' experience in IT, including time spent in support and development. He has worked with SQL Server since 6.0 back in 1995. He has also developed in VB, VB.NET, C#, and Java. Grant has written books for Apress and Simple-Talk. Grant presents at conferences and user groups, large and small, all over the world. Grant volunteers for PASS and is on the Board of Directors as the Immediate Past President. He joined Redgate Software as a product advocate January 2011.

Grant Fritchey's contributions