T-SQL Performance Tuning (i)--Compiling and recompiling

Concept Introduction

There are two main parts of the query that we call in SQL Server:

1. Compile query, mainly includes five links (cache lookup, analysis, Algebra, optimization, cache new plan)

Process Description: First, in the plan cache to find whether to include a new query, if included is directly to the execution engine to execute the cache plan, skip the compilation phase.

Second, if there is no match, perform the analysis phase (including parameterization, and convert the SQL text into a logical tree as input to the next stage).

Check that the cache is included again, and that the inclusion is given to the execution engine, otherwise proceed to the next step.

Thirdly, algebra.

Four, optimize and give the new plan to the execution engine.

Here's a concept to be introduced: execution plan Cache ,

SQL Server has a pool of memory that is used to store execution plans and data buffers. The percentage allocated to the execution plan or data buffer within the pool fluctuates dynamically with the system state.

The portion of the memory pool used to store execution plans is called the procedure cache.

The execution plan cache primarily contains the query plan and execution context.

2. Execute the query plan (Generate execution plan-Generate query results), as described in the next article.

Compiling and recompiling

SQL Server has an efficient algorithm to find an existing execution plan for any particular SQL statement. SQL Server will reuse any existing plans found, saving the overhead of recompiling the SQL statement. If there is no existing execution plan, SQL Server generates a new execution plan for the query. So what we want to do is how to efficiently apply the cache of the execution plan, and the appropriate recompile execution plan, to improve the efficiency of the query and reduce the loss of performance.

First we need to know what the situation will be to have a new execution plan, I briefly summarize a few more common situations:

1. If you use with Repcompile or call Sp_repcompile, the schedule will not be cached.

2. Schema changes such as tables or attempts to add indexes, adding or deleting columns, and so on.

, a recompile is thrown when the schema name is different, the execution plan cache is not reusable, and the problem requires attention from the developer, often with a move to add or reduce the schema name unconsciously. Not only does it affect performance, but when moving code between servers causes too many problems, it is recommended that you use Schema.object (dbo. Excutiontest) such a uniform specification.

If the index is additions and deletions, according to the actual situation will generate a completely different execution plan.

After indexing, a table scan becomes an index lookup, which I have already introduced in the chapter of the clustered index.

3.SET options, including: Ansi_nulls\ansi_warnings\cursor_close_on_commit_inplicit_transactions\ansi_padding\quoted_ IDENTIFIER, the change in the state of these options will cause the recompilation of the execution plan. It is recommended that you use the default settings of the database as much as possible, and do not make unnecessary set options in the stored procedure.

4. Based on the level of optimization of the plan: If a large number of changes are made to the key (use the INSERT, update, or DELETE statement to modify the table referenced by the query).

Analysis, Algebra and optimization

1. Parsing , the SQL text is converted to a logical tree, and a logical tree is created for each statement.

2. Algebra , the main completion of check semantics is correct.

There are also three tasks to complete:

• Name Resolution: Query whether the name of each object exists correctly and whether the scope is visible;
• Type derivation: The field type for each node in the analysis tree, such as the field type after the table link.
• Aggregation binding: The actual operation of the aggregation is determined according to the syntax in the host query.
• Packet binding: Verifies that the group by syntax is correct (the nonclustered column after the select must be behind group by).

3. Optimization

Query optimization is the most complex part of the query, which is done automatically by the system, and if the previous link tells us what to do, then the optimizer describes how to do it, and the query optimizer wants to choose an efficient execution plan whenever possible. This section will be a topic at the end of this series. A detailed description of the process is presented here (simplification – Exploration – implementation).

Summarize:

This paper introduces the process of compiling query, and the concrete content of generating cache, reusing cache, recompiling, etc. There are a number of things to note about optimizing our T-SQL statements, and the more hits we have on the execution plan cache, the lower our query consumption will be, but this is not absolute. Finally, add a hyperlink to the tool and command to recompile, there are many easy to query analysis execution plan is efficient or even exist problems function and method, http://www.cnblogs.com/wenBlog/p/4966991.html.

T-SQL Performance Tuning (i)--Compiling and recompiling