Esat Erkec
Regressed Queries report in the Query Store feature

SQL Server Stored Procedure Recompilation Factors

September 6, 2021 by

In this article, we will explore in detail the factors that cause SQL Server stored procedures to be recompiled.

Introduction

SQL Server Stored procedures are the make-ready T-SQL codes that can be executed again and again by the database users to perform data manipulation commands and data definition commands. We can list the following 4 features as the benefits of the stored procedures that come to our mind first:

  • Performance
  • Code reuse
  • Maintainability
  • Security

The main performance advantage of a stored procedure is that they have the ability to reuse compiled and cached query plans. In the first execution of a stored procedure, its execution plan is stored in the query plan cache and this query plan is used in the next execution of the procedure. However, some factors can lead to recompilation of the cached stored procedure query plans and this process is called stored procedure recompilation. Recompilation of a stored procedure has some advantages and disadvantages. Such as, after the index, is rebuilt or statistics are updated a stored procedure query plan may be recompiled and this new plan will usually be more effective. On the other hand, redundant high recompilation operations can increase the CPU overhead and may affect the database engine performance negatively.

Prerequisites

In this article, we will use the Adventureworks sample database and we will also use a sample stored procedure. Through the following query, we can create this sample SQL Server stored procedure.

Monitoring the SQL Server stored procedure recompilation

To monitor the SQL Server stored procedure recompilation events we can use the sql_statement_recompile event of the Extended Events and this event helps to capture when a statement-level recompilation is performed by the query optimizer. The following query creates a new extended event that uses the sql_statement_recompile event and immediately starts it.

We can use the Watch Live Data option to use for viewing the data collected by the extended event. We expand the Extended Event folder in the SQL Server Management Studio (SSMS) and then we right-click on the created extended event.

Using the Watch Live Data option of the Extended Events

When we click the Watch Live Data menu, the Live Data screen will appear. In order to display only the captured events that belong to the Adventureworks database, we will filter the database name.

Filtering Extended Events

SQL Server stored procedure recompilation and indexes

Indexes are used to improve the query performance and after the creation of an index may lead to recompile the stored procedure. Now let’s demonstrate this point with an example. At first, we will enable the actual execution plan and then will execute the sample stored procedure. In the execution plan, we can see a missing index suggestion on the top of the execution plan.

Create missing index in an execution plan

We will right-click on the execution plan and select the Missing Index Details option. The missing index details will be shown in a new query window. We will give a proper name to the index and remove the multi-line comment signs. As of last, we execute the index creation query and create the index.

Create an index for performance

After the creation of the index, we will execute the same stored procedure. At this time, the stored procedure will be recompiled by the query optimizer because of the new index creation. This situation can be seen in the extended event with all details. Particularly, the recompile_cause shows the reason for the recompilation.

Schema changed event for query recompilations

SQL Server stored procedure recompilation and statistics

Statistics stores the distribution of the column data in histograms and also statistics are used by the query optimizer to estimate how many rows can be returned from a query. The data modifications cause to decrease in the accuracy of the statistics. At this point, the statistics can update automatically during the execution of the query if the Auto Update Statistics option is enabled. At the same time, the statistics can update manually. The statistics update operations cause recompilations. Now, we will understand this concept with an example. The following query will insert numerous rows into the SalesOrderDetail table.

After the data modification, we will execute the same stored procedure with the same parameter. During the execution of the query, the statistics will be updated therefore the stored procedure will be recompiled.

Statistics changed event for query recompilations

SQL Server stored procedure recompilation and Query Store

The Query Store feature of the SQL Server capture and store the executed queries execution plans and query runtime execution statistics. In the Regressed Queries report of the query shows the top 25 queries with regressed performance over time according to different metrics (duration, CPU, I/O, etc.).

Regressed Queries report in the Query Store feature

The Regressed Queries report show us that our sample stored procedure used different execution plans and these plans are performing different performance. At this point, we can force the sample stored procedure to any particular execution plan. So that, the stored procedure will use the same execution plan in its every execution. At first, we choose the query plan in the graph that we want to be forced to use each time the stored procedure is executed. We click the Force Plan button so that this plan will be used every execution of the stored procedure.

Regressed Queries graph in the Query Store feature

In the message box, we confirm the plan forcing operation.

Confirming plan forcing in Query Store

After forcing a query plan, the check sign will be shown on it.

Forcing a query plan in the query store

Now, we will execute our stored procedure with the same parameter.

When we force a stored procedure to use a query plan using the query store, the procedure will be recompiled in its first execution. The recompile_cause attribute indicates the Query Store plan to force policy changed explanation.

Schema changed event for query recompilations Query Store plan forcing policy changed

SQL Server stored procedure recompilation and temporary tables

Temporary tables are heavily used in the stored procedures to store data not permanently. When a stored procedure involves a temporary table,  the query involving the temp table is not even compiled and waits until the first time the query is executed. This property is called the deferred compile. For example, the following query will create a stored procedure that inserts some rows into a temporary table. In the first execution of this stored procedure, it will be recompiled by the optimizer, and in the next execution, the recompilations are not performed.

Deferred compile event for query recompilations

As we can see the above image clearly shows us that the part of the stored procedure where data is inserted into the temporary table is being recompiled. In some cases, the schema of the temporary table can modify in the stored procedure. However, this usage type has a drawback because every execution of the stored procedure will be recompiled. Now let’s examine how such an issue arises with an example. In the example procedure below, we will first create a temp table and then add a column.

Temp tables and query store

The extended event has captured all query recompilation events and recompilation reasons. Adding a new column into the temporary table is treated as schema-change so the stored procedure has recompiled in every execution.

SQL Server stored procedure recompilation and SET options

With help of the SET options, we can determine the behavior of SQL Server at the session level. Changing the set options will cause to recompile the stored procedures. For example, when we SET CONCAT_NULL_YIELDS_NULL is ON, a string and NULL value result yields a NULL result. Now, we enable this option inside a sample stored procedure and execute it.

Set options change event for query recompilations

Conclusion

In this article, we have learned various factors that lead to SQL Server stored procedure recompilations, and also we have learned how we can monitor this recompilation by using extended events. The factors are summarized as below:

  • Adding an index
  • Updating the statistics
  • Using SET options inside the stored procedure
  • Forcing stored procedure to use a different query plan
  • Using temporary tables

Esat Erkec
Latest posts by Esat Erkec (see all)
700 Views