Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the difference between compilation and recompilation in SQL Server. Many people may not know much about it. In order to let everyone know more, Xiaobian summarized the following contents for everyone. I hope everyone can gain something according to this article.

Pros and cons of implementing 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 can be hundreds or even thousands of times faster than a bad one.

So from this point of view, every time you run a statement, it is of course the best to compile it first. It can guarantee that the execution plan used is the best SQL SERVER can find.

SQL SERVER can execute hundreds of instructions per second. If each is compiled once, it 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 SQL SERVER currently caches (please do not run directly on the production server because there are often large caches).

SELECT * FROM sys. [syscacheobjects]

recompiled occurrence scenario

However, there are times when SQL SERVER intentionally does not reuse an execution plan cached in memory and compiles a copy on the spot to ensure that it returns the correct value, or because of performance concerns.

This behavior is called recompilation. Here are some of the more common situations in which recompilation occurs:

1. When any object (table or view) involved in the instruction or batch process has a schema change

For example, a field is added or deleted to a table or view, an index is added or deleted, constraints are added or deleted to a table, and so on.

Definition changes, the original execution plan may not be correct, of course, to recompile

2. Run sp_recompile

When a user runs sp_recompile on a stored procedure or trigger, a recompile occurs the next time they are run.

If the user runs sp_recompile on a table or view, all stored procedures that reference that table (or view) are recompiled before they are run again

Some actions will clear all execution plans in memory, forcing everyone to do recompilation.

For example, the following action clears all execution plans from the entire SQLSERVER server cache:

(1)Detach a database

(2)An upgrade was made to the database, and on the new server, an execution plan purge occurs

(3)DBCC freeproccache was run

(4)Run reconfigure statement

(5)Run alter database.. The collate statement modifies the character set of a database (collection)

The following action clears the execution plan for a database cached by the SQLSERVER server:

DBCC FLUSHPROCINDB

Clears the stored procedure cache contents of 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 shuts down automatically

DBCC CHECKDB statement ends

When the following SET switch values change, the previous execution plans cannot 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 can affect the behavior of statement execution and even lead to different results. They change, SQLSERVER will have to redo the execution plan according to the new settings

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

When statistics are manually updated, or SQLSERVER finds that some statistics need to be updated automatically, SQLSERVER will recompile all the statements involved.

It should be noted that in SQL SERVER, performing planned reuse is not necessarily a good thing, and compilation/recompilation is not necessarily a bad thing.

Planned reuse can help SQL SERVER save compilation time, reduce CPU usage and reduce blocking, but the disadvantage is that each reuse plan is not necessarily the most appropriate plan. Parameter sniffing is a typical negative effect of planned reuse. Compile and recompile certainly bring as accurate an execution plan as possible to the currently running statement, but for frequently run statements, especially those that execute faster, the compilation time may account for a significant proportion of the total final time. This is a huge waste of resources.

After reading the above, do you have any further understanding of the difference between compilation and recompilation in sql server? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.