SQL Server Compilation and recompilation

SQL Server Compilation and recompilation

Meaning of the compilation

When SQL Server receives any one instruction, including query, batch, stored procedure, trigger (trigger)

, precompiled directives (prepared statement), and dynamic SQL statement to complete syntax interpretation, statement interpretation,

Then compile (compile) to build the execution plan (execution plan) that can run. In the process of compiling,

SQL Server estimates the possible execution plans based on the schema of the objects involved, the statistics, and the specific contents of the directives.

And their cost, and finally select a 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 "plan cache" is used to execute the same statement, and SQL Server can use the same execution plan without having to compile again.

This behavior is called "reuse (reuse) or call reuse execution plan." but sometimes, even the exact same statement, the next time SQL executes, it will compile again.

This behavior is called "recompilation (RECOMPILE)". both the compilation and the recompilation of the execution plan are resource-intensive.

If the execution plan can be reused, then SQL Server does not need to perform the above process, speed up the execution of instructions, many of the statements in the tuning of the article refers to the database reuse execution plan that means

Pros and cons of execution plan reuse

The execution of the plan, of course, determines the final execution speed of the statement. For the same statement, using a good execution plan may be hundreds of times times faster than the bad one, or even hundreds of thousands.

So from this point of view, every time a statement is run, it is of course best to compile it first. He was able to ensure that the execution plan used was the best that SQL Server could find.

However, SQL Server may run hundreds or thousands of instructions per second. If each is compiled again, it is a waste of resources. So SQL Server is also trying to find a balance here.

Get the best overall performance with a limited compile/recompile

Run the following instructions to see which SQL Server is currently caching the execution plan (do not run directly on the production server because there are often large caches)

1 SELECT * FROM sys. [Syscacheobjects]

The occurrence scenario of recompilation

However, in some cases, SQL Server intentionally does not reuse cached execution plans in memory to ensure that it returns the correct value, or has a performance concern, and compiles a copy on-site.

This behavior, known as recompilation (recompile), is called refactoring. The following are the more common scenarios where recompilation can occur:

1. Schema changes occur when any object (table or view) involved in the instruction or batch process

For example, adding or deleting a field on a table or view, adding or removing an index, adding or removing 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 a user has run sp_recompile on a stored procedure or trigger, the next time they run they will be recompiled.

If a user runs sp_recompile on a table or view, all stored procedures referencing this table (or view) will be recompiled before the next run

3. Some actions will erase all the execution plans in memory, forcing everyone to do the recompilation

For example, the following actions will erase all execution plans cached by the entire SQL Server servers:

(1) Detach a Database

(2) The database has been upgraded, on the new server, the execution plan will be emptied

(3) The DBCC FREEPROCCACHE is running

(4) Run the RECONFIGURE statement

(5) Run the ALTER DATABASE. 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

Clears the stored procedure cache contents of a database in SQL Server 2000 Server memory

1 DECLARE @a INT2 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 plan 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 have changed, and SQL Server is going to redo the execution plan based on the new settings.

5. When the statistics on the table or view change

SQL Server compiles the statements involved when the statistics are manually updated, or if SQL Server discovers that a statistic needs to be updated automatically

It is necessary to note that in SQL Server, execution of plan reuse is not necessarily a good thing, and compiling/recompiling is not necessarily a bad thing.

Plan reuse can help SQL Server save compile time, which is good for reducing CPU usage and blocking, but the downside is that each reuse plan is not necessarily the most appropriate plan.

Parameter sniffing parameter sniffing is the negative effect of typical plan reuse. Compiling and recompiling can, of course, lead to the execution of the plan as accurately as possible for the currently running statement.

However, for frequently running statements, especially those that perform faster, the compilation time may be a significant percentage of the total last time. This is a huge waste of resources.

In general, SQL Server is well-balanced between compilation and recompilation, and in most cases it's fine.

SQL Server Compilation and recompilation