Query Optimizer Kernel Analysis article 1

Query Optimizer Kernel Analysis article 1

 

Query Optimizer Kernel Analysis article 1

Query Optimizer Kernel Analysis article 2: Generate candidate execution plans& Execution plan cost estimation

Query Optimizer Kernel Analysis article 3: query execution and plan Cache& Hint prompt

Query Optimizer Kernel Analysis article 4: view the execution plan from an instance

Query Optimizer Kernel Analysis article 5: better understanding of the Execution Plan

Analysis of the kernel of the query optimizerTo correct the order of Join Operations.

Analysis of the query optimizer kernel Article 7: Data Access Operations for execution Engines--- Scan

Query Optimizer Kernel Analysis article 8: Data Access Operations for execution Engines--- Seek and Lookup

Analysis of the query optimizer kernel Article 9: Data Access Operations for execution Engines--- Bookmark Lookup

Analysis of the query optimizer kernel Article 10: Data Access Operations for execution Engines-Aggregations (aggregation)

 

The SQL Server query optimizer is a cost-based optimizer. It analyzes many candidate query plans for a given query and estimates the cost of each candidate plan, so as to select a plan with the lowest cost for execution. In fact, because the query optimizer cannot optimize each candidate plan, the query optimizer balances the optimization time with the quality of the query plan, select an "Optimal" plan as much as possible.

 

Therefore, the query optimizer becomes the most important component in SQL Server and affects the performance of SQL Server. Selecting a correct or wrong execution plan means that the query execution time may vary by milliseconds, minutes, or even hours.

 

Understanding the internal mechanism of query optimization can help DBAs and developers write better queries, or provide information to the query optimizer so that it can generate effective execution plans. This seriesArticleDescribes the internal operation knowledge of the query optimizer. In addition, it also tells you how to use the information of the query optimizer for performance diagnosis.

 

Next, let's first look at how the query optimizer works.

 

The core of the SQL Server database engine is the storage engine and query processor (also known as the relational engine ). The storage engine is responsible for reading data in the optimal way between disk and memory, while maintaining data integrity. The query processor, as its name implies, accepts all the queries submitted to SQL Server, and then executes the plan and provides the desired results to generate their best execution plan.

 

We submit the query to SQL Server AS A T-SQL. Because an SQL statement is a high-level abstract declarative language, it only defines the data to be obtained from the database, and does not tell how to obtain the data (or, no methods or steps for getting data are defined ). Therefore, for each query received by SQL Server, the first task of the query processor is to generate a plan, which describes how to execute the query, then the storage engine executes the plan.

 

To ensure that the query has been performed in the plan that the query processor considers as the best, the query processor performs different steps throughout the Query Process:

 

 

Of course, the figure above is just a simple example. Next, let's show you another figure to understand the process of processing a query:

 

We will see the detailed explanation and application of each step in subsequent articles. Next we will briefly introduce some steps in the figure (for the sake of simplicity, we will take the first image as an example)

1. Parsing and binding (resolution and binding ):After a query is submitted to the database, it is first parsed by syntax. If the query syntax is correct, the input result of the parsing process is a logical tree, each node of the logical tree represents each operation performed by the query, such as reading a table and performing inner join.

Next, let's look at a logic tree example for the following query:

 

The generated logic tree is as follows:

 

This process is a syntactic lexical parsing of the compilation principle.

After talking about parsing, the subsequent operation is binding. The operation is now named algebrizer. This operation mainly checks whether the objects in the parsing logic tree exist. For example, whether the customer is a table in the database or whether the customerid field is medium in the customer table.

After binding, another tree data structure is generated and passed to the next step.

2. Query Optimization. This process mainly involves the optimization process using the algebrizer tree in the above process. Here we can roughly divide the optimization process into two steps:

 

A. generate an execution plan. In this process, the query optimizer uses the previous tree to generate an execution plan. This process is mainly to convert logical operations on the tree into physical operations (in fact, the storage engine can call the methods, these methods are actually to read data ).

 

B. estimate the cost of each execution plan. A logical operation can correspond to many physical operations, and the cost of each physical operation is different. At the same time, it does not use the so-called "What physical operations are better than other physical operations ", everything is identifiable as needed. In this process, many candidate execution plans are generated, and the query optimizer considers many situations comprehensively. It selects a plan that is considered to be "better" and passes it to the storage engine.

 

3.Query execution and plan Cache. This process is relatively simple, mainly because the storage engine executes the execution plan. To avoid re-Compilation of similar SQL queries, the used execution plan will be cached in the Plan cache pool.

 

Basically, we can see that the query optimization process is a process of ing logical operations into physical operations.

In the next article, let's take a closer look at the generation of candidate execution plans and estimate their costs!