SQL Server compilation and recompilation detailed _mssql

SQL Server Compilation and recompilation

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. In the process of compiling,

SQL Server estimates the possible execution plan based on the schema, statistics, and specific content of the objects involved.

And their costs (cost), and finally select a SQL Server to consider the lowest cost of the execution plan to execute. After the execution of the plan is generated,

SQL Server usually caches them in memory, the term is called "plan cache" after the same statement execution, SQL Server can use the same execution plan, without having to do another compilation.

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)

1 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

1 DECLARE @a INT
2 SELECT @a=db_id (' Gposdb ')
3 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

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.

Thank you for reading, I hope to help you, thank you for your support for this site!