SQL--SQL Server query optimizer (optimizers)

https://www.red-gate.com/simple-talk/sql/sql-training/the-sql-server-query-optimizer/to understand how to write SQL Server SQL code, How the query optimizer works is important. The SQL Server query optimizer is a cost-based optimizer。 It analyze many candidate execution plans for a given query, estimate the cost per plan, and choose the plan with the lowest cost to consider。 In fact, since the query optimizer cannot consider every possible plan for each query, it must actually perform a cost-based balancing operation, taking into account the cost of querying the potential plan and the cost of the plan itself. Therefore, the SQL Server component has the greatest impact on the performance of the database. Of course, a better understanding of how the query optimizer works can also help database administrators and developers write better queries and provide the query optimizer with the information it needs to generate an efficient execution plan.

1. How the SQL Server query optimizer works
   2. How does the query optimizer work?
      1. The SQL Server Database engine core is the two main components:
         1. Storage Engine
            1. Responsible for reading data between disk and memory in a way that optimizes concurrency while preserving data integrity
         2. Relational engine (query processor)
            1. Accept all queries submitted to SQL Server, design a plan for their best execution, and then execute the plan and provide the desired results
      2. For each query that SQL Server receives, the first task of the query processor is to design a plan as quickly as possible, which describes the best way to execute the query (or at least a valid way). Its second task is to execute queries based on the plan.
   3. Process of query processing
      2. SQL Statement-parsing (Parse tree), Binding (algebrized tree), Query optimization (execution Plan), Q Uery execution, Query Results
      4. queries are parsed and bound:(this stage output is a logical tree, each node in the tree represents the logical action that the query must perform)
      6. Query optimization:
         1. Generate a possible execution plan: Using the logical tree, the query optimizer designs many possible ways to execute a query, that is, the number of execution plans
         2. Cost estimates for each plan: Although the query optimizer does not generate every possible execution plan, it evaluates the resource and time cost of each plan it generates. The query optimizer considers the plan with the lowest evaluation cost to be selected and passed to the execution engine
         3. Query execution, plan cache: The query is executed by the execution engine according to the selected schedule, which can be stored in memory in the plan cache
      8. Parsing parsing:
         1. Parsing ensures that the T-SQL query has a valid syntax and transforms the SQL query into an initial tree representation (that is, the logical operator tree that represents the advanced steps required to execute the related query)
      10. Binding bindings:
          1. The binding ensures that all object names exist and that each table name and column name on the analysis tree is associated with the corresponding object in the system directory, outputs an algebraic tree, and then sends it to the query optimizer
      12. Query Optimization Optimization:
          1. SQL Server uses a cost-based optimizer and uses a cost estimation model to estimate the cost of each candidate plan
             1. In essence, query optimization is the process of mapping a logical query operation expressed in a tree representation to a physical operation that can be performed by the execution engine.
             2. The final product of the query optimization process is an execution plan: a tree of multiple physical operators that contains the algorithm to be executed by the execution engine in order to obtain the desired results from the database
   5. Generate a candidate execution plan
      1. In theory, in order to find the best execution plan for a query, the cost-based query optimizer should generate all possible execution plans that exist in that search space and correctly estimate the cost of each plan. However, some complex queries may have thousands of or even millions of possible execution plans, while the SQL Server query optimizer can typically consider a large number of candidate execution plans, but not exhaustive searches for all possible plans for each query.
      2. The query optimizer must strike a balance between optimization time and planned quality.
3. Background and challenges for the SQL Server query optimizer
   1. Cardinality and cost estimates-how do you read and understand them?
   2. Connection sequencing-One of the most complex problems in query optimization
   3. Description: How to join tables in a valid sequence can improve query performance, but can exponentially increase the number of executions?

SQL--SQL Server query optimizer (optimizers)