When a developer runs any T-SQL statement batch, SQL Server performs the following three steps:
1. Resolution Batch Processing
2. Compile Batch Processing
3. When executing a batch of T-SQL statements, the SQL Server performs the following three steps:
1. Resolution Batch Processing
2. Compile Batch Processing
3. Execute Batch Processing
Analysis
The so-called "resolution" refers to the process in which the SQL server command parsing module first checks the syntax of batch processing. If no error is found, the command parser splits the source code into multiple logical units, such as keywords, identifiers, and operators. The parser then constructs an internal structure that describes the steps required to perform request operations or extract the request result set from the source data. If the batch processing contains a query, the internal structure is called a query tree. If the batch processing contains a process, it is called a sequence tree.
Compile:
In this step, the sequence tree is used to generate an execution plan. The optimizer module analyzes various methods for retrieving information from the source table. It tries to find the fastest way to use the smallest resource, and also supplements the list of tasks required for execution (for example, check security and confirm whether the constraints are enforced, if merging is required during processing, a trigger should also be included, and so on ). The result is an internal structure called the execution plan.
Run:
The execution plan is stored in the process notification cache where it can be executed. Different steps in the execution plan will be sent to different modules in the relational engine for execution; DML manager, DDL manager, stored procedure manager, transaction processing manager, or Utility Manager. The processing result is collected and sent to the caller in the form of a result set.