Principles of SQL Server compilation, recompilation, and execution plan reuse

When SQL server receives any command, including query, batch processing, stored procedures, triggers, pre-compiled commands, and dynamic SQL Server statements, it must complete Syntax Parsing and semantic analysis, then, compile the program to generate an executable plan ". During compilation, SQL Server estimates possible execution plans and their costs based on the architecture, statistics, and specific instructions of the objects involved, finally, select a statement that SQL Server considers as the lowest cost.

After the execution plan is generated, SQL Server usually caches the plan to the memory. They are collectively referred to as "plane cache ". After the same statement is executed, SQL Server can use the same execution plan without further compilation. This behavior is called "reuse ". But sometimes, even if it is the same statement, SQL Server will continue to compile it again in the next execution. This behavior is called "recompilation ". Compilation and re-Compilation of execution plans are both resource-consuming.

The Execution Plan determines the statement execution speed. For the same statement, a good execution plan may be several hundred times faster than the difference, or even several thousand times. From this point of view, it is best to compile a statement without running it. It ensures that the execution plan used is optimal for SQL Server. However, SQL Server may run hundreds of thousands of commands per second. Every compilation is a serious waste of resources. Therefore, SQL Server tries to find a balance point here, using the preferred complie/recomplie to get the best overall performance.

To view the execution plan cached by SQL Server, run the following statement: Select * From SYS. syscacheobjects

The execution plan cache and reuse mechanisms of SQL Server vary with instruction calling methods. The following describes the most common types:

1. adhoc statements

A group of batch processing commands that contain select, insert, update, and delete. For such commands, the execution plan can be reused only when the front and back ends are completely consistent, including uppercase and lowercase letters, spaces, and line breaks. SQL Server considers them to be two identical statements. Therefore, this requirement is quite high.

2. Run dynamic SQL Server statements in Exec () Mode

Some applicationsProgramTo be flexible in development, a statement string is dynamically spliced during the program running, and then executed in Exec () mode. This call method is called "dynamic SQL ". Its advantage is that it is flexible and can dynamically generate commands based on the customer's choice, not just the predefined ones. However, its disadvantage is that it is too flexible. The statements sent by the customer are different each time, or the statement body is the same, but the parameters are different. SQL server must compile the statements. This is the same as the ADHOC statement.

3. Automatic parameterized Query

For some comparisonsSimple QuerySQL Server 2005 can automatically parameterize itself and replace the parameters in the statement with a variable, but this is limitedSimple Query.

4. commands called using sp_executesql

Automatic query parameterization is not supported in many conditions, and it still needs to generate an adhoc execution plan for each sentence query. So it is not the most effective way to reduce the ratio. Using sp_executesql can increase execution plan reuse more effectively.

5. Stored Procedure

It is recommended that you use commands frequently called to make them into a stored procedure, which facilitates management, standardizes scripts, and greatly improves execution plan calling. From the perspective of SQL Server, it is recommended that the vast majority of commands can be called in the form of stored procedures, as little as possible to use dynamic SQL.

However, in some cases, SQL Server intentionally does not reuse the execution plan cached in the memory to ensure the return of the correct value or have performance concerns, and compiles a copy on the site. This behavior is recompiled. Below are common recompilation scenarios:

1. When the schema of any object (table or attempt) involved in instruction or batch processing changes

For example, adding or deleting another field, adding or deleting an index, and adding or deleting a constraint on a table or view. If the definition changes, the original execution plan may not be correct. Of course, re-compile the plan.

2. After running sp_recomplie

After you run sp_recomplie on a stored procedure or trigger, the next time you run them, a re-compilation occurs. If you run sp_recomplie on a table or attempt, all stored procedures that reference this table or view will be recompiled before the next operation.

3. Some actions will clear all execution plans in the memory and force everyone to re-compile

• Detach a database
• An upgrade is made to the database. The execution plan is cleared on the new server.
• The DBCC freeproccache statement is run.
• Run the reconfigure statement.
• Run the alter database... modify filegroup statement.
• Modified the character set of a database using the alter database... collate statement.

The following actions clearly show the execution plan of a database cached by the SQL Server server:

• DBCC flushprocindb statement
• Alter database... modify name statement
• Alter database... Set Online statement
• Alter database... Set offline statement
• Alter database... set emergency statement
• Drop database statement
• When a database is automatically disabled
• When DBCC checkdb statement ends

4. When some set switch values change, the previous execution plans cannot be reused.

5. When the statistical information on the table or attempt changes

After the statistics are updated manually, or when SQL server finds that a statistical information needs to be updated automatically, SQL Server will re-compile all the statements involved.

It must be noted that in SQL Server, execution plan reuse is not necessarily a good thing, but compilation and re-compilation are not necessarily a bad thing. In SQL Server, the main functions that affect program reuse and compilation/re-compilation include: 1. Use stored procedures or sp_executesql to call statements that will be reused, rather than using ad-hoc statements or dynamic SQL.
2. Reference an object (table, view, stored procedure, etc.) in a statement to the schema name with it, not just the name of the object itself.

3. Set the database parameterization attribute to forced.This attribute enables mandatory database parameterization. That is to say, for most statements running in this database, SQL Server will first parameterize and then run. If the application often uses ADHOC to call the same statement, force parameterization may be helpful.4. update statisticsAfter the statistics are manually or automatically updated, the execution plans related to the statistics are no longer reusable, and re-compilation is generated.

5. create procedure... with recompile options and exce... the with recomplie option uses "with recomplie" When recreating or calling a stored procedure, which forces the SQL Server to always compile the stored procedure before calling it, run again. 6. sp_recomplie
7. when a user calls a statement, a query prompt such as "keep plan" or "keepfixed plan" is displayed.