SQL Server Execution Plans

There are some questions that those who are familiar with the T-SQL dialect of SQL ask themselves:

1. Why is my query working slowly?
2. Does the query I wrote use an index?
3. Why is the index I created not used?
4. Why is this query slower (faster) than the other one, despite returning the same result?

If you want answers to these questions, one of the tools that must be used to understand them is Execution plans.

What is an Execution Plan?

EThe executionPlan is a description of the most efficient execution of the query we wrote, calculated by the optimizer. That is, Execution Plan shows us in which version your query is implemented, and using which algorithm it is executed.

In short, how SQL Server will or has executed our query.

Let's look at everything from the beginning,

After we write a query, the execution of the request goes through several stages. Those stages are concentrated into 2 large groups:

1. Events in relation engine (RE)
2. Events in Storage Engine (SE)

In the RE(relation engine), the query is parsed and executed by the query optimizer. The query optimizer prepares an execution plan. This Execution Plan is then sent in binary format to the SE(storage engine), which uses this plan during the execution of queries. Transaction and index operations are realized in the storage engine.

IToexplain what we said in a consistent and simple form, let's first open SQL SERVER and let such a request be executed. (Queries will be applied to the AdventureWorks2012 database)

use AdventureWorks2012
GO
SELECT
         hremphist.businessentityid
       , person.person.firstname
       , person.person.lastname
       , humanresources.employee.jobtitle
       , humanresources.department.name AS DepartmentName
       , hremphist.startdate
FROM (
                 SELECT
                         businessentityid
                       , departmentid
                       , startdate
                       , ROW_NUMBER() OVER (PARTITION BY businessentityid ORDER BY startdate DESC) AS rn
                 FROM humanresources.employeedepartmenthistory
         ) AS hremphist
         JOIN humanresources.department
                 ON humanresources.department.departmentid = hremphist.departmentid
         JOIN humanresources.employee
                 ON humanresources.employee.businessentityid = hremphist.businessentityid
         JOIN person.person
                 ON person.businessentityid = hremphist.businessentityid
WHERE hremphist.rn = 1;

We can see the Execution Plan of the query by pressing Ctrl+L

Let's see together what execution processes the above request goes through,

1) Query Parsing

As soon as we execute the query we wrote, it is taken by RE and checked whether it is true or false from the syntactic side (Parsing). If there are no syntactic problems, the query has been successfully parsed. It should be remembered that if the query is DDL, it is not optimized! Because CREATE TABLE, which is a DDL operation of the example, has only one syntax. Only DML operations can be optimized. The result of the query parsing process gives us a parse tree (sequence tree, query tree).

2) Algebrizer

If the DML request has passed the parsing process successfully, it is sent to Algebrizer, a special mechanism. Algebrizer performs logical processes such as the existence of objects used by us, the names of table columns, compatibility of types, etc. As a result of these processes, Algebrizer returns us a binary format. Returns a "query processor tree", which results are processed by the query optimizer.

3) Query Optimizer prepares Execution Plan for us.

4) Storage Engine 

Physical processing of the request is performed at this stage, and the request is executed based on the Execution Plan.

The SQL Query Optimizer performs the generation and preparation of Execution Plans. When preparing an Execution Plan, it necessarily refers to statistics and indexes and uses the information collected there by the execution plan maker. In the next step, the Execution Plan given by the Query Optimizer is sent to the storage engine; based on this, our query is physically executed. It should be remembered that when an Execution Plan is prepared, the prepared plan is added to the cache (processor cache) so that it is stored in the memory. During the next similar Execution Plan preparation, the Optimizer does not have to do additional work and can use the previously generated plan.

Depending on the complexity of the query, T-SQL QUERY OPTIMIZER generates several Execution Plans for us and tries to choose the fastest one among them. However, it is necessary to consider that there are often queries for which Execution Plans can be generated for their implementation, which may take several minutes. Therefore, Query Optimizer makes the necessary selection among the first generated plan combinations, not all. Gave us the perfect Execution Plan. Our EPs will be stored in the plan cache not to generate a plan for similar questions every time.

To delete cached data, run the following command:

DBCC FREEPROCCACHE

We can also delete the required plan by passing sql_handle and plan_handle specifically.

What can cause the current Execution Plan to change?

1. Execution of parallel queries
2. The statistics had changed or became outdated when the Execution Plan was made.
3. Entering information into the temporary table

It should be remembered that there are 2 main forms of EP(Execution Plan) in T-SQL,

1. The estimated Execution Plan (Estimated execution plan) is the plan the SQL Optimizer estimates before the query is executed.
2. Actual Execution Plan (Real execution plan) – is a plan received only after the request is released for execution.

Although these plans store a completely different set of data, they do not differ from each other at first glance.

There are 3 main forms of execution plans,

1. Graphical description (Graphical plans)
2. Description in text format (Text Plans)
3. In XML format (XML Plans)

The most commonly used representation form of execution plans is a graphical plan. Although the graphic description form does not reflect all the details at first glance, detailed information can be seen in the outer panel.

Although Text Plans have been declared "deprecated" by SQL Server, we can still use them. In the Text version, you can get detailed information about the plan the first time, speed it up, and edit it in a text editor.

SET SHOWPLAN_ALL ON;
GO
SELECT TOP 100 [BusinessEntityID]
      ,[NationalIDNumber]
      ,[LoginID]
      ,[OrganizationNode]
      ,[OrganizationLevel]
      ,[JobTitle]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      ,[HireDate]
      ,[SalariedFlag]
      ,[VacationHours]
      ,[SickLeaveHours]
      ,[CurrentFlag]
      ,[rowguid]
      ,[ModifiedDate]
FROM [ADV_Works].[HumanResources].[Employee]

In the test code example above, we enabled the detailed description of the "estimated" plan in text form. To disable this mode, write OFF instead oExecutionrun the query.

SET SHOWPLAN_TEXT ON;--active
GO
SET SHOWPLAN_TEXT OFF;--deactive
GO
SET STATISTICS PROFILE ON;--active text mode for actual plan

XML Plans - XML plans have 2 forms of description:

1. SHOWPLAN_XML-is generated until execution
2. STATISTICS_XML is generated after query execution

The most used form of EPs is a graphical form (Graphical Execution plans).

1. Ctrl+M (for Actual) Ctrl+L (for Estimated)
2. By selecting the "Include Actual Execute plan" or "Display Estimated Execute Plan" button on the toolbar
3. By right-clicking on the session, we wrote the query and selected "Include Actual Execution Plan" or "Display Estimated Execute plan" from the drop-down menu.