Query Execution Plan inside SQL Server

Performance is a huge topic when dealing with databases, but for each case you will need a different approach for your data. And as always it is good to understand how the things work under the hood. Today we will be talking about Query Execution Plan inside a SQL Server instance

---

Disclaimer: This is not an advanced tutorial. We will cover just the basics over here

---

As we know SQL is present in our lives maybe even since our birth depending on when you have born. In my case I was born close to the 2000's so I am definitely in one of my governments SQL databases since then. And sometimes it is good to understand how the things work, I say sometimes but you usually need that when you are trying to improve something, and so that you can improve it you need to understand it.

A simple SELECT

Inside this article over here from Microsoft, they describe: Process a SELECT statement

In this section we can see clearly the steps behind one SQL statement execution.

The basic steps that SQL Server uses to process a single SELECT statement include the following:

1. The parser scans the SELECT statement and breaks it into logical units such as keywords, expressions, operators, and identifiers.
2. A query tree, sometimes referred to as a sequence tree, is built describing the logical steps needed to transform the source data into the format required by the result set.
3. The Query Optimizer analyzes different ways the source tables can be accessed. It then selects the series of steps that return the results fastest while using fewer resources. The query tree is updated to record this exact series of steps. The final, optimized version of the query tree is called the execution plan.
4. The relational engine starts executing the execution plan. As the steps that require data from the base tables are processed, the relational engine requests that the storage engine pass up data from the rowsets requested from the relational engine.
5. The relational engine processes the data returned from the storage engine into the format defined for the result set and returns the result set to the client.

Looking at this list we can see that the first two steps are just some manipulation so that the third step can happen. At the final of the third step we will have the execution plan.

---

The article above also describe a whole other world of details about everything that I am writing for you guys, so definitely check that out!

---

There are some interesting things about the plan that Microsoft describes in this article. They explain the general idea behind the the execution plan and give some interesting examples. Let's go through some facts about the execution plan:

• It is chosen by its cost (That is measured by amount of computing resources)
• It chooses the lowest estimated cost (In cases of thousands of execution plans it uses some complex algorithms to choose the best one without searching too much)
• Tries to be the fastest as possible

A cool example with SQL Server 2022

I have these two tables over here:

CREATE Table tblAuthors
(
   Id int identity primary key,
   Author_name nvarchar(50),
   country nvarchar(50)
)
CREATE Table tblBooks
(
   Id int identity primary key,
   Auhthor_id int foreign key references tblAuthors(Id),
   Price int,
   Edition int
)

I just copied them from here

I inserted some data to both of the tables and executed the following query:

SELECT TOP(50) * FROM tblAuthors ta JOIN tblBooks tb ON tb.Auhthor_id = ta.Id 

And boom I got the following execution plan:

And look how amazing these algorithms are, they have chosen to start from the join table, look at how many rows we have fetched from tblBooks and how many from tblAuthors. They have chosen this way due to the index available in that table, in this case we could just get 50 records from tblBooks and make a index scan in tblAuthors with those 50 records.

If you guys want to test an make your won execution plan download SQL Server Management Studio, unfortunately it is just avaiable for Windows. In linux over here I downloaded DBeaver Lite Edition, and unfortunately it is under license this version, but anyway, there is a lot of things that we can do with the DBeaver Comunity version too.

And why make all of these plans and stuff??

The Microsoft it self explains an example: In a construction, there are two companies responsible for building a house, each of them with the same blueprint, which one will be faster? The one that plans before it starts or the one that starts building without planning?

Cover Image from DCStudio