Chapter I (2)--Prediction and actual implementation plan

As mentioned earlier, there are two different types of execution plans, the first is the output of the optimizer, which is the predictive execution plan, where the operators or steps are logical steps, because they represent the view of the execution plan in the optimizer, but do not represent the physical aspect of the actual execution.

Another plan is to represent the output that the query actually performs. This type is called the actual execution plan, which indicates what happens when the query is actually executed.

These two plans show separate sets of results, but they can be seen as having a huge resemblance. In most cases, the same operator for the same cost will appear in both execution plans. However, when a recompile occurs, SQL Server deletes the plan in the plan cache and rebuilds it, and there is a significant difference between the two. This situation usually occurs when the statistics change, or something else happens when the storage engine processes the query. We will explain in more detail in the later part of this chapter.

The estimated execution plan (hereafter referred to as the estimate plan) is a plan that is stored in the plan cache, so we can only execute plans that are generated when the query is run by the actual execution plan. The estimate plan never accesses data directly, but it is quite effective for large, complex, potentially long-running query analysis. But the actual plan is preferred because it shows a lot of important statistics in the running process, such as the number of rows actually accessed by a particular operator. Typically, this extra information makes the actual plan the most common way to do it, but estimating the scheduling machine is important, especially since you can get it from the plan cache.