Meaning of compilation
--------------------------------------------------------------------------------
When SQL Server receives any instruction, including queries (query), batch processing (batch), stored procedures, triggers (trigger)
, precompiled directives (prepared statement), and dynamic SQL statements, which complete the syntax interpretation, the statement interpretation,
Then "compile (compile)" To generate the "Execution plan (execution plans)" that will run. During compilation, SQL Server estimates the possible execution plans, as well as their costs (cost), based on the schema of the objects involved, statistics, and the specific contents of the directive, and finally selects the execution plan that SQL Server considers to be the lowest cost. After the execution plan is generated, SQL Server usually caches them in memory, and the term is collectively called "plan Cache" after the same statement executes, and SQL Server can use the same execution plans without having to compile again.
This behavior is called "reuse (reuse) or reuse execution plan". But sometimes, even the exact same statement, the next execution of SQL will be compiled again.
This behavior is called "recompile (RECOMPILE)". The compilation and recompilation of the execution plan are all resources consuming.
If the execution plan can be reused, then SQL Server does not need to perform the above process to speed up the execution of instructions, and many of the statement tuning articles refer to the database reuse execution plan that means
pros and cons of execution plan reuse
--------------------------------------------------------------------------------
The quality of the execution plan certainly determines the final execution speed of the statement. For the same statement, a good execution plan may be hundreds of times times faster, or even hundreds of thousand, than the bad one.
So from this point of view, every time you run a statement, it is certainly best to compile it first. He was able to guarantee that the execution plan used was the best that SQL Server could find.
But SQL Server may run hundreds of instructions per second. If each is compiled again, it is a waste of resources. So SQL Server here is also trying to find a balance point,
Use limited compile/recompile to get the best overall performance
Run the following instructions to see what SQL Server's current cache execution plan is (please do not run directly on the production server because there are often large caches on it)
Copy Code code as follows:
SELECT * FROM Sys. [Syscacheobjects]
the scenario where the recompile occurred
--------------------------------------------------------------------------------
But sometimes, SQL Server compiles a copy of the site in order to ensure that the correct value is returned, or if there is a performance concern, and intentionally does not reuse the cached execution plan in memory.
This behavior is referred to as recompilation (recompile). The following are some of the more common scenarios where recompilation occurs:
1. An architecture (Schema) change occurs for any object (table or view) involved in an instruction or batch process
For example, you add or delete a field on a table or view, add or delete an index, add or delete a constraint (constraints) on a table, and so on.
The definition has changed, the original execution plan is not necessarily correct, of course, to recompile
2, run over sp_recompile
Once the user has run a sp_recompile on a stored procedure or trigger, the next time they run they will have a recompile.
If a user runs sp_recompile on a table or view, all stored procedures referenced to this table (or view) will be recompiled before the next run
3, some actions will erase the memory of all the execution plan, forcing everyone to do recompile
For example, the following actions will clear all the execution plans for the entire SQL Server cache:
(1) Detach a Database
(2) The database has been upgraded, on the new server, the execution plan will be emptied
(3) The DBCC FREEPROCCACHE was run
(4) Run the RECONFIGURE statement
(5) The ALTER database was run. The COLLATE statement modifies the character set of a database (collation)
The following actions clear the execution plan for a database that is cached by SQL Server servers:
DBCC flushprocindb
Clear the stored procedure cache content for a database in SQL Server 2000 Server memory
Copy Code code 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 closed
At the end of the DBCC CHECKDB statement
4. When the following set switch values change, the previous execution plans can not be reused
Copy Code code 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 behavior of the execution of the statement, and even bring different results. They've changed, SQL Server is going to redo the execution plan based on the new settings.
5. When the statistical information on the table or view has changed
When statistics are manually updated, or SQL Server discovers that a statistic needs to be updated automatically, SQL Server compiles the statements involved
It is important to note that in SQL Server, execution plan reuse is not necessarily a good thing, and compile/recompile is not necessarily a bad thing.
Planning reuse can help SQL Server save compile time, and is good for reducing CPU usage and blocking, but the disadvantage is that each reuse plan is not necessarily the most appropriate plan.
Parameter sniffing parameter sniffing is the negative effect of the typical planning reuse. Compiling and recompiling can of course bring the most accurate execution of the plan to the currently running statement, but for frequently run statements, especially those that execute faster, the compilation time may be a significant proportion of the final total time. It's a huge waste of resources.
In general, SQL Server is a good balance between compilation and recompilation, and most of the time it's not a problem.
