Query processing plots

Recently purchased the "SQL Server 2008 kernel anatomy and Troubleshooting" This book, the 5th chapter of the CPU and query processing, 5.4 query processing, this length of the content is illustrated, hoping to help you better understand the query.

SQL Server processes a query in 4 steps: Parse, bind, optimize, execute.

parsing, binding, and optimization are all done in the relational engine. Optimizes the output of the scheduled execution plan and calls the storage engine here.

Analysis

Parsing: syntax checking, such as incorrect reserved words, columns, and table names. The parse tree is generated without errors, and stops and returns an error if there is an error.

binding

The binding process is also known as normalization, and a query tree is generated when the binding ends.

1. Name resolution: Checks that all objects exist and are visible in the user's security context. That is, check the table and column names to make sure they are present and that the user has access to them.
2. Type deduction: Determines the final type of each node in the parse tree.
3. Aggregation bindings: Determine which places can be aggregated.
4. Composite binding: Binds an aggregation to the correct selection list.

The binding process can check out all syntax errors and stop and return errors to the user if there is a syntax error.

Query Optimization

Choose a good enough, not the best plan.

Optimization Model Summary:

• is there a valid cache for this program? there is a plan to use the cache;
• is this plan an ordinary plan? Yes, the general plan is used;
• perform simplification. The standardizes the query tree and performs some basic conversions to further simplify the query tree.
• is the cost of the plan small enough? Yes, use this plan. Not start optimization.
• phase 0: test basic rules, and hash and nested join options. Is the plan cost less than 0.2? Yes, use the plan. Not continuation Phase 1.

• Phase 1: verify more rules, and transform join order. If the best (least expensive) plan cost is less than 1.0. Then use this plan. If not, continue to judge. If maxdop>0 and this system is an SMP system, and the minimum cost plan is greater than the cost threshold for parallelization, the parallel plan is used. Compare the overhead of the parallel plan with the overhead of the best serial plan and pass the less expensive plan to phase 2
• Phase 2: Verify all possible plans, and choose the plan with the least cost when the test time limit is reached.

Outputs an executable plan and caches the plan.

Query processing plots