SQL Server compilation and recompilation scenarios and advantages and disadvantages of reuse

Meaning of compilation
--------------------------------------------------------------------------------
When SQLSERVER receives any command, including query, batch, stored procedure, trigger)
Pre-compiled commands (prepared statement) and dynamic SQL statements (dynamic SQL Statement) must complete syntax and statement interpretation,
Then, compile to generate the executable plan that can be run )". During compilation, SQLSERVER estimates possible execution plans based on the schema, statistical information, and instructions of the objects involved, and their cost (cost), and finally select an execution plan that SQL Server considers as the lowest cost to execute. After an execution plan is generated, SQLSERVER usually caches the plan in the memory. The term "plan cache" is used to execute the same statement, so that SQLSERVER can use the same execution plan, you do not need to compile it again.

This behavior is called "reuse" or "reuse Execution Plan ". But sometimes, even if it is the same statement, the SQL statement will be compiled again in the next execution.

This behavior is called recompile )". Both compilation and recompilation of execution plans consume resources.
If the execution plan can be reused, SQLSERVER does not need to execute the above process to speed up the execution of commands. This is what I mean when I mention the database reuse execution plan in many statement optimization articles.

Advantages and disadvantages of execution plan reuse
--------------------------------------------------------------------------------
The Execution Plan determines the final execution speed of the statement. For the same statement, a good execution plan may be hundreds or even thousands of times faster than the difference.
From this point of view, it is best to compile each statement first. He can ensure that the execution plan used is the best that SQL server can find.
However, SQLSERVER may run hundreds of commands every second. Every compilation is a waste of resources. So SQLSERVER is also trying to find a balance point here,
Use limited compile/recompile to get the best overall performance
Run the following command to see what execution plans are currently cached by SQLSERVER (do not run them directly on the production server because there is usually a large cache)Copy codeThe Code is as follows: SELECT * FROM sys. [syscacheobjects]

Recompilation scenarios
--------------------------------------------------------------------------------
However, in some cases, SQLSERVER compiles a copy of the Execution Plan cached in the memory to ensure that the correct value is returned or to have performance concerns.
This behavior is called recompile ). Below are common recompilation scenarios:

1. When any object (table or view) involved in instruction or batch processing changes in schema
For example, you have added or deleted a field on a table or view, added or deleted an index, and added or deleted a constraint on the table.
The definition has changed, and the original execution plan is not necessarily correct. Of course, re-compilation is required.

2. Run sp_recompile.
When you run sp_recompile in a stored procedure or trigger, the next time you run it, a re-compilation occurs.
If you run sp_recompile on a table or view, all stored procedures that reference this table (or view) must be recompiled before the next operation.

3. Some actions will clear all execution plans in the memory and force everyone to re-compile
For example, the following actions will clear all execution plans cached by the entire SQLSERVER Server:
(1) Detach a database
(2) after upgrading the database, the execution plan is cleared on the new server.
(3) Running DBCC freeproccache
(4) run the reconfigure statement.
(5) run the alter database .. collate statement to modify the character set (collation) of a database)

The following actions will clear the execution plan of a database cached by the sqlserver Server:
DBCC FLUSHPROCINDB
Clears the Stored Procedure cache content of a database in SQL Server 2000 Server Memory.Copy codeThe Code is as follows: DECLARE @ a INT
SELECT @ a = DB_ID ('gposdb ')

DBCC flushprocindb (@ a) 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 the following SET switch values change, the previous execution plans cannot be reused.Copy codeThe Code is as follows: ansi_null_dflt_off,
Ansi_null_dflt_on,
Ansi_nulls,
_ Ansi_padding
Ansi_warnings,
Arithabort,
Concat_null_yields_null,
Datefirst, dateformat,
Forceplan,
Language,
No_browsetable,
Numeric_roundabort,
Quoted_identifier

This is because these SET switches affect the execution of statements and even lead to different results. They have changed, and SQLSERVER will redo the execution plan according to the new settings.

5. When the statistical information on the table or view changes
After the statistics are updated manually, or SQLSERVER finds that a statistical information needs to be updated automatically, SQLSERVER will re-compile all the statements involved.

It should be noted that in SQLSERVER, execution plan reuse is not necessarily a good thing, but compilation/recompilation is not necessarily a bad thing.
Plan reuse can help SQLSERVER save Compilation Time and reduce CPU usage and blocking. However, the disadvantage is that each reuse plan is not necessarily the most suitable plan.

Parameter sniffing is a negative effect of typical plan reuse. Compilation and recompilation can certainly bring the execution plan as accurate as possible for the currently running statements, but for frequently running statements, especially some statements with relatively fast execution speed, the Compilation Time may account for a considerable proportion of the total time. This is a great waste of resources.

In general, SQLSERVER can balance compilation and recompilation well. In most cases, there is no problem.