SQL Server performance optimization execution plan in-depth analysis section 1 SQL Execution Process, it is also an important aspect of database performance tuning and provides a lot of data basis for optimization. This series Article There will be dozens of articles in front, back and forth, which will give a brief introduction to the Execution Plan, as well as how to use the execution plan for database tuning.
In order to make everyone better understand, digest this knowledge, I will regularly write content online video lectures, friends can participate in this group: http://www.agilesharp.com/c/sqlprofiler.aspx
Registration activities started: http://www.agilesharp.com/Event.aspx/T-2
The topics in this article are as follows:
- Query plan Overview
- Query resolution
- Query Optimizer
- Query execution
- Reuse of query plans
Query plan Overview
Most of the time, when we are using SQL Server, it is very simple to do: enter an SQL statement, execute it, and finally obtain the result. Next, in order to make everyone better understand the internal mechanism of SQL Server, we will re-examine the execution of an SQL statement. After the SQL statement is mentioned, the database executes a series of internal processing. We can roughly divide the internal processing into two stages according to the execution sequence: 1. operations in the relational Engine 2. operations in the storage engine are performed in the relational engine of the database. The SQL query statement is parsed and the parsed result is passed to the query optimizer. The query optimizer is responsible for generating the execution plan. Then, the execution plan (in the second-level format) will be passed to the storage engine, and finally the underlying data will be returned or updated. The database storage engine performs many operations, such as locking, index maintenance, and transaction processing. Because this series of articles mainly analyze execution plans, our focus will be on the relational engine. Next, we will discuss in detail the execution process of an SQL query statement.
Query resolution
As we have just mentioned: After an SQL statement is submitted to the database, the SQL statement is first passed into the relational engine. When an SQL statement reaches the relational engine, the first operation is to check whether the SQL statement format is correct. This process is what we often call "resolution. The result of the parsing process is to generate a parsing tree, or a query tree. The query tree reflects the logical steps to perform a query. The structure of the query tree is similar to the figure below: in fact, from the perspective of the Compilation Principle, the parsing process is the parsing of grammar and lexical, finally, the syntax tree is generated. Note that if the submitted SQL statement is not a data operation statement (the data operation statement refers to the select, insert, and update statements), the statement will not be optimized. For example, if the SQL statement submitted is to create a data table, the statement will not be optimized, but will be executed directly. If the data operation statement is submitted, the parsing tree created by the relational engine will be passed
Algebrizer
Components Execute the binding process. During the binding process
Algebrizer The component checks whether the table name in the resolution tree is associated with the reference of the corresponding table or object in the database. At the same time,
Algebrizer The component is also responsible for determining whether the types of each node in the resolution tree are consistent with those in the database.
Algebrizer The component traverses the tree from bottom to top, that is, starting from the page-level node, that is, the column and constant. Binding and parsing is a very important process. In this process, we will also identify some aliases we have defined. After this process is completed, a binary "query processing Tree" is generated, which is passed to the query optimizer.
Query Optimizer
The query optimizer uses the Query Processing tree and related statistics to generate an execution plan. In other words, the query optimizer points out how best to execute the submitted SQL statement. The query optimizer determines whether indexes can be used to access data. It is better to use that type of join operation (for example, although we sometimes write left join in SQL, the Query Optimizer may use inner join after analysis to ensure the same results ). The query optimizer is an optimizer for basic cost analysis. This means that it will try to generate the lowest cost execution plan for each SQL statement. In addition, we will briefly analyze the statistics used by the optimizer. The so-called statistical data refers to the data used to describe the column and index-related information in the database, that is, the data or "metadata ". The optimizer estimates the cost by combining the statistical data and Query Processing tree. By default, statistics are automatically updated in the database (Manual updates can be performed during optimization ). It should be mentioned that the table variables do not have any statistical data. That is to say, if the data in the table variables are queried, the optimizer will not make any optimization. However, temporary tables have corresponding statistics. One thing to note is that the above cost is only "estimation. Some complex statements may have many execution plans as candidates. In this case, the query optimizer does not analyze all the combinations, but finds an execution plan close to the theoretical minimum. The planned cost is estimated the time required to complete the query. The lowest estimated cost is not necessarily the lowest resource cost.
Query execution
Once an execution plan is generated, the operation is transferred to the storage engine, which is also the place where the query is actually executed and the production of the actual execution plan generated based on estimation of the execution plan.
Reuse of query plans
From the previous steps, we can see that SQL Server generates
Actual execution plan It requires a lot of steps and a lot of costs (the excessive compilation of execution plans is often a big performance problem), and the Execution Plan must be reused as much as possible (if not described in the following article, an execution plan refers to an actual execution plan. Therefore, once an execution plan is generated in the database, it is cached in the memory (called a plan buffer ). As mentioned earlier, when the optimizer generates an estimated execution plan, the plan will be passed to the storage engine. In fact, before passing the estimated execution plan to the storage engine, the query optimizer will go to the "Plan buffer" to find the actual execution plan corresponding to the current estimated execution plan. If it is found, the query optimizer uses the execution plan for subsequent operations. This avoids re-generating the actual execution plan. Generally, only one execution plan for each query is saved, unless the query optimizer knows that parallel execution can produce better performance, the execution plan for parallel query is cached, that is to say, the same query has two execution plans in the plan buffer. The execution plan is not always stored in the memory. They will also expire. SQL Server is based on the least recently used Algorithm To remove unnecessary execution plans. The following table lists the conditions for the execution plan to be removed: 1. The system generates memory pressure and requires more memory. In this case, the SQL Server is forced to release its occupied memory. 2. The recent usage of the execution plan in the memory is 0. 3. The execution plan is not referenced by the current database connection.
Note: If you are familiar with. net, you can compare it with. Net's garbage collection mechanism. This is the next article. We will analyze the execution plan more!
More articles available at the beginning: It entrepreneurship push Platform
Www.agilesharp.com Registration activities started: http://www.agilesharp.com/Event.aspx/T-2
