Query processing plots

Source: Internet
Author: User

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.


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.


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.