This is the first post from my complex tutorial project called “Understanding Query Execution Plans”. This is an introductory post, in which I will guide you through the concepts you need to know before starting to analyze the plans.
So we will start with the beginning and take things step by step,. This way, we will make sure that, by the end of this tutorial, you will not only know to read a query plan but you will also understand why things are happening the way they do inside a query plan and you will be able to fix some problematic queries.
The first step in this long (but very exciting) path to query execution plan enlightenment is to understand the high-level architecture of SQL Server.
Hopefully, if you read my post carefully, this is what you’ll know by the end:
- How does the architecture of SQL Server look like
- What are the most important components of SQL Server
- What role is played by each component
- What is the query execution engine
- What functions does the query execution perform
SQL Server Architecture
This is the general high-level architecture of SQL Server.
As you can see, there are four major components:
- The protocol layer
- The query processor, which is actually the relational engine (most of you are more familiar with this second name)
- The storage engine
- And the SQL OS
Whenever we interact with SQL Server, whether from SQL Server Management Studio or from any other application, the following things happen:
- If, during the execution, data is needed, a call to action will be initiated for the storage engine. The storage engine takes care of all data that’s stored in a SQL Server and it does it by transaction-based commands, bulk operations (like backup, bulk insert, etc.) and some DBCC commands.
- Parse
- Compile
- Optimize queries
- And then it will watch over the execution of the batch
- Our request is first handled by the protocol layer and translated into a form that’s familiar to the relational engine (or the query processor). It also works the other way around: when a query is executed by the query engine and results are returned (no matter if they are query results, status messages or errors), the protocol layer translates the message to into a form that’s understandable by the client application and sends it back to the client.
- The query processor accepts the messages from the protocol layer and decides what should happen to them. For example, for T-SQL queries, it will
- Last, but not least, SQL Server has its own operating system (SQLOS), to handle some processes differently than Windows. Some of these processes are: scheduling, memory management (including the buffer pool), synchronization primitives, deadlock detection and so on.
Query Processor
Going back to the query processor, you can see that it has two major components:
- The query optimizer (this is where the query plan is generated)
- And the query execution engine.
Query Optimizer
The query optimizer receives the query from the protocol layer. The query is not exactly how we wrote it; it is translated to something that can be understood by the optimizer. This new form taken by the query is called the query tree.
The Query Optimizer takes the query tree and prepares it for optimization.
Several operations are applied to the query tree during the optimization process. The, the query tree is compiled and the final result is an execution plan.
This is the moment when we approach the actual subject we’re going to tackle: the query execution plan and the query execution engine.
Query Executor (or query execution engine)
The query executor (or query execution engine) takes the query plans and, no surprise here, executes them. There are many functions performed by the query execution, some of them being:
- It acts as a dispatcher for all commands in the execution plan. It iterates through all the commands in the plan until the batch is complete
- And it interacts with the storage engine to retrieve and update data from tables and indexes.
In this tutorial we will focus on understanding query behavior. We will take a look at how the SQL Server query processor works in order to execute queries.
In conclusion, if we want to take the complex road of investigating the factors which affect query performance or how to identify possible performance issues with a query plan , it is vitally important to understand how the query execution works.
Check out the next post to find out more about the elements that compose the query plan. These elements are called iterators and they are one of the most important query execution concepts.
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 below. I will happily answer you.