Esat Erkec
sp_cache_insert event

SQL Server Stored Procedure Recompile Options

September 15, 2021 by

This article intends to give comprehensive details on how we can use the recompilation options of SQL Server stored procedures and how they behave when we use these recompilation options.

What is a SQL stored procedure?

A stored procedure is a ready T-SQL code that can be reused again and again. The most essential benefits of using stored procedures can be listed as follows:

Performance: After the first execution of the stored procedure, the query optimizer creates an execution plan and this plan is stored in the query plan cache. So that, all next executions of the same SQL Server stored procedure will use this cached stored procedure. This methodology aims to avoid an unnecessary query compilation process.

Code reuse: The SQL stored procedure helps to avoid rewriting the same codes again and again.

Maintainability: Using stored procedures simplifies code maintenance. Such as, a stored procedure can have wide usage in different applications but only changing the stored procedure will easily affect all applications.

Security: SQL stored procedures help us to get rid of dealing with security settings of different database objects. It is enough to give the only permission to the stored procedure.

When is a stored procedure compiled?

Contrary to the notion that stored procedures are compiled during their initial creation, stored procedures are compiled on their first execution. Now, let’s analyze this working mechanism of the stored procedures and learn in which phase their first compilation occurs with an example. To monitor these steps in SQL Server, we will use two different tools:

  • Dynamic Management Views are the special system views that store various information about the SQL Server performance counters and other metrics
  • Extended Events is a very advanced monitoring tool that helps to capture and reports the various events that occur in the SQL Server. Extended Events uses fewer system resources, has advanced filtering and grouping options, and offers numerous events to monitor. Therefore, we will create an extended event to monitor what goes on behind the scenes of the query plan compilation process of SQL Server stored procedures. The extended event that we will create will include the following events:
    • query_post_compilation_showplan: This event captures the initially compiled plan. At the same time, this event returns the estimated execution plan of when the query is compiled
    • sp_cache_hit: This event occurs when a stored procedure plan is fetched from the procedure plan cache by the query optimizer
    • sp_cache_insert: This event occurs when a stored procedure is placed into the procedure cache
    • sp_cache_miss: This event occurs when a stored procedure is not found in the procedure cache
    • sp_cache_remove: This event occurs when a stored procedure is deleted from the procedure cache

At the same time, we will determine a filter that will create only captures the events of the stored procedures. In order to enable this filter, we will add a filter expression to object_type with the database name filter.

After creating and starting the extended event, we will click the “Watch Live Data” option of the created extended event. So, we can monitor captured events live in SQL Server Management Studio (SSMS).

SQL Server extended event

Now we will create a sample SQL Server stored procedure in Adventureworks database.

After creating the stored procedure, we cannot see any event in the created extended event.

Result of the SQL Server stored procedure extended event

At the same time, the GetProductionList procedure query plan is not inserted into the query plan cache.

sys.dm_exec_cached_plans  usage details

In this step, we will execute our sample stored procedure with a random parameter and will take a look at the extended event.

Result of a SQL Serverquery_post_compilation_showplan event

After the first execution stored procedure, two events is captured and these are :

The query_post_compilation_showplan event indicates that the stored procedure was compiled after its first execution. At the same time, the query_post_compilation_showplan captures the estimated execution plan and we can find out this plan in the query plan tab. The sp_cache_insert event indicates that the execution plan is inserted into the query plan cache. These two events prove that SQL Server stored procedures are compiled in their first execution. Also, we can see that after the first execution of the stored procedure, its query plan is inserted into the query plan cache.

Monitor the SQL Server query plan cache

In the second execution of the stored procedure, we will only see the sp_cache_hit event because the query plan is found and retrieved from the plan cache.

sp_cache_hit event

Another point about SQL Server stored procedure execution plan recompilation is related to the connection options. SQL Server allows changing some settings of the session connections with help of the SET options. However, these options cause the creation of a new execution plan. For example, before executing our sample query, we change the NUMERIC_ROUNDABORT as ON and then execute it.

sp_cache_insert event

The extended event indicates that the query optimizer has decided to generate a new execution plan for the same stored procedure. We can find out the difference between these two execution plans using the dm_exec_plan_attributes view.

Monitoring execution plan attributes

As seen clearly in the illustration, the set options values are different and these different causes generate a new execution plan generation.

SQL stored procedure and sp_recompile

sp_recompile is a system procedure that is used to recompile the stored procedures. The usage of this procedure is very simple, we only pass the procedure name into this system procedure.

EXEC sp_recompile N’ProcedureName’

When we execute this system procedure, the query plan of the procedure that we passed as a parameter will be removed from the cache immediately. Now, we execute sp_recompile for our sample procedure.

Usage details of the sp_recompile procedure

After executing the sp_recompile, it has returned the message “Object ‘GetProductionList’ was successfully marked for recompilation”. Actually, it means that the procedure execution plan has been removed from the plan cache. This event can be seen in the extended event.

SQL Server stored procedure and recompile options

Now, we re-execute our sample SQL Server stored procedure and analyze its behavior using the extended event.

Usage of the WITH RECOMPILE hint in SQL Server

After the execution of the sample stored procedure, two events have occurred:

query_post_compilation_showplan has been performed because the cached execution plan of the stored procedure has been removed when we have executed the sp_recompile. In this case, the query optimizer can not find any appropriate execution plan in the plan cache and then re-create a new query plan for the executed stored procedure. sp_cache_insert has occurred because the procedure execution plan is inserted into the plan cache.

How to use WITH RECOMPILE hint with SQL Server stored procedures

In some cases (parameter sniffing), we need to generate a fresh query plan for every execution of the SQL Server stored procedure. In these cases, we can add the WITH RECOMPILE hint into the SQL Server stored procedure so the query optimizer generates a new query plan in every execution of the stored procedure. Through the following query, we can add the WITH RECOMPILE hint and alter the stored procedure.

Altering a stored procedure causes the query plan entry for the stored procedure to be removed from the plan cache. This situation can be understood to see the sp_cache_remove event.

Usage details of the WITH RECOMPILE hint

In each execution of the GetProductionList store procedure, the query optimizer will recompile the procedure because of the WITH RECOMPILE hint. As a result, using the RECOMPILE hint inside a stored procedure causes to:

  • Recompilation of every execution of the stored procedure
  • Does not insert the stored procedure query plan into the query plan cache

When we enable the Actual Execution Plan and then look at the select operator properties. The RetrievedFromCache attribute will be shown as false. When we execute any query with the RECOMPILE hint this attribute indicates the false value in the query plan.

RetrievedFromCache  attribute in execution plan

We can use the WITH RECOMPILE hint to adding at the end of the stored procedure so that we don’t require to change the source code of the SQL Server stored procedure.

In this usage method, we don’t lose the cached execution plan of the stored procedure only a fresh is used.

Conclusion

In this article, we have learned two options and their details that can help to recompile the SQL Server stored procedures. Consequently, we can use the following methods causing to generate a fresh execution plan for the stored procedures:

  • Using sp_recompile system procedure
  • Using WITH RECOMPILE hint
  • Altering the stored procedure
Esat Erkec
168 Views