Advantages and disadvantages of stored procedure recompilation, determination of raised statements

Stored procedures improve the reusability of execution plans by explicitly converting the variable portions of the query to parameters. This enables the execution plan to be reused when the query is resubmitted with the same or different values in a mutable part. A stored procedure contains a complex set of SQL statements, which makes the execution plan of the build stored procedure somewhat expensive. Therefore, it is often advantageous to reuse the execution plan of a stored procedure instead of generating a new plan. However, sometimes existing plans may not be applicable or may not provide the best processing strategy during reuse. SQL Server compiles the statements in the stored procedure to generate a new execution plan to resolve this issue.

first, the benefits of re-compiling

The recompilation of stored procedures has both advantages and disadvantages, and sometimes it may be more economical to consider a new processing strategy for a query rather than reusing an existing plan. In particular, when the distribution of data in tables (or corresponding statistics) changes or when new indexes are added to the table. recompilation in SQL Server 2008 is at the statement level. Statement-level recompilation reduces the overhead because only individual statements are recompiled, not all statements in the stored procedure. The recompile method before SQL Server 2005 causes the stored procedure to be completely recompiled again and again.

Create a stored procedure and execute it with the following code:

CREATE PROC Dbo.getperson  as SELECT  from Personhunderthousand WHERE = ' Xiahong ' EXEC Dbo.getperson

　　The execution plan currently executing the current stored procedure is as follows:

　　

If we have built an overlay index:

CREATE nonclustered INDEX  on Personhunderthousand (Name) INCLUDE (age)

Obviously, executing the query again, using an indexed execution plan is more advantageous:

　　

SQL Server automatically recompiled the stored procedure to efficiently use the index.

In this case, it is advantageous to spend an extra CPU cycle to recompile the stored procedure to build a better execution plan.

　　SQL Server automatically detects the conditions that need to recompile an existing plan. SQL Server determines when the existing plan needs to be recompiled, based on specific rules. If a particular implementation of a stored procedure enters the scope of the recompile rule (Execution plan aging, SET option change, and so on), the stored procedure will recompile every time the recompilation requirement is met, and SQL Server may not be able to generate a better execution plan.

ii. Disadvantages of recompiling stored procedures

In SQL Server Profiler, the Sp_recompile event is used to track statement recompilation. You can also use the Sql:stmtrecompile event, which is a stored procedure event.

The index created above is beneficial to the statements inside the stored procedure, this time we delete the overwrite index that we just created and replace it with a useless index to the query statement:

EXEC Dbo.getperson GO CREATE nonclustered INDEX  on Personhunderthousand (InCome) GO EXEC Dbo.getperson

Let's look at the output of SQL Server Profiler monitoring:

　　

We see that recompiling is useless for this stored procedure. Unfortunately, it goes into a condition that causes SQL Server to recompile the stored procedure each time it executes. This reduces the buffer efficiency of the stored procedure and wastes the CPU cycle on rebuilding the same plan. Therefore, it is important to know the conditions that cause the stored procedure to recompile and to avoid these conditions in all ways when implementing the stored procedure. Of course, this example is a bit far-fetched, who is nothing will be built index AH.

third, confirm the statement that causes the recompilation

SQL Server can recompile a single statement or the entire stored procedure in a stored procedure. Therefore, in order to find the cause of the recompilation, it is important to confirm that the SQL statements cannot be reused for the existing plan.

You can use the Profiler tool to track stored procedure recompilation. You can also use the Profiler tool to confirm the statements that cause the stored procedure to recompile.

Analyze the events and data columns that the stored procedure recompiled:

Event	Data columns
sp:completed	EventClass
Sp:recompile	TextData
Sp:starting	EventSubClass
sp:stmtcompleted (optional)	SPID
Sp:stmtstaring (optional)	StartTime

Consider the following simple stored procedure:

CREATE PROC dbo. InsertPersonas CREATETABLEINT)INSERT into T1 (C1) VALUES (a);    --Data modification results in recompilation

The output is as follows:

　　

As you can see from the output, there is a recompile event (Sp:recompile) that indicates that the stored procedure has been re-compiled. When the stored procedure executes for the first time, SQL Server compiles the stored procedure and generates an execution plan.

Execution plan values are maintained in volatile memory, and they are discarded when SQL Server restarts. When the next time the stored procedure executes after the server restarts, SQL Server compiles the stored procedure again and generates the execution plan. These compilations are not considered recompilation of stored procedures because the plan does not exist in buffering for reuse, and the Sp:recompile event indicates that the plan already exists but cannot be reused. To understand the recompilation caused by that statement, you need to look at the TextData column in the Sp:recompile event, which explicitly describes the statement that was recompiled. You can also use a combination of the Sp:stmtstarting event and the recompile event to confirm the stored procedure statement that caused the recompilation. The Sp:recompile event occurs immediately after the Sp:stmtstarting event indicating that the stored procedure statement caused the recompilation. Using the TextData column is simpler, but in a very complex process, using the Sp:stmtstarting event may make more sense.

Note that after the stored procedure is recompiled, the stored procedure statement that causes the recompilation is started again to execute the new schedule. You can use the Sp:stmtstarting event or the Sp:stmtcompleted event to confirm the stored procedure statement that caused the recompilation.

Advantages and disadvantages of stored procedure recompilation, determination of raised statements