Understanding Query Execution Plans

Have you ever needed to investigate performance issues in SQL Server and didn’t know where to start?
Do you hear from others that they analyze the query execution plans and you only have vague notions about what this means?
Or maybe you just want to refresh you memory or to have a good read about what’s happening during query execution? Then you are in the right place.

Within this page, I will start a mini series of tutorials related to the SQL Server’s Query Execution Plans.

This is a very important topic for developers, database administrators and also other IT professionals. Why, you ask? Because knowing what happens during the execution of a query gives you the possibility to troubleshoot eventual performance issues and to work on improving your queries.

Before diving into explanations about the fundamental concepts related to the query execution, let’s see what you’re going to learn if you follow my tutorial:

  • You will learn what the query execution engine does and how it works
  • You will find out what iterators are and what are some of their most important properties
  • I will then show you a few ways to read the query plans
  • Then, we are going to go deeper into the query plans and find out how to analyze what we see in there. I will present the main operations that translate to iterators in the query plan, like:
    • Scans and seeks
    • Lookups
    • Joins
    • Aggregations and so on. And I will also take each of the most important operators one by one and explain what they mean and how they are used
  • And last, but not least, I will go through some of the most common performance troubleshooting patterns related to the execution plan.

So, if any of these appeal to you and you want to learn more, please continue following these posts.
If you have suggestions or feedback, you want me to explain something or you just want to say hi, you can do that in the comments of each blog post. I will happily answer you.

And check out the posts on this page, to start the exciting path of learning about the query execution plan:

Query Execution Engine Introduction

Iterators in the Query Execution Plan

Properties of Iterators

Reading Query Plans with the Showplan Options