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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE EVENT SESSION [TrackStoredProcedureEvents] ON SERVER ADD EVENT sqlserver.query_post_compilation_showplan( ACTION(sqlserver.client_app_name,sqlserver.sql_text) WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC')), ADD EVENT sqlserver.sp_cache_hit( ACTION(sqlserver.client_app_name,sqlserver.sql_text) WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC')), ADD EVENT sqlserver.sp_cache_insert( ACTION(sqlserver.client_app_name,sqlserver.sql_text) WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC')), ADD EVENT sqlserver.sp_cache_miss( ACTION(sqlserver.client_app_name,sqlserver.sql_text) WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC')), ADD EVENT sqlserver.sp_cache_remove( ACTION(sqlserver.client_app_name,sqlserver.sql_text) WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC')) WITH (STARTUP_STATE=ON) GO ALTER EVENT SESSION TrackStoredProcedureEvents ON SERVER STATE = START |
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).
Now we will create a sample SQL Server stored procedure in Adventureworks database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE OR ALTER PROCEDURE GetProductionList @ProductIdNumber AS INT AS SELECT p.[ProductID] ,p.[Name] ,pm.[Name] AS [ProductModel] ,pmx.[CultureID] ,pd.[Description] FROM [Production].[Product] p INNER JOIN [Production].[ProductModel] pm ON p.[ProductModelID] = pm.[ProductModelID] INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx ON pm.[ProductModelID] = pmx.[ProductModelID] INNER JOIN [Production].[ProductDescription] pd ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID] WHERE p.ProductID =@ProductIdNumber |
After creating the stored procedure, we cannot see any event in the created extended event.
At the same time, the GetProductionList procedure query plan is not inserted into the query plan cache.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT * FROM ( SELECT cp.objtype AS [Plan Type], OBJECT_NAME(st.objectid,st.dbid) AS [Object Name], cp.refcounts AS [Reference Counts], cp.usecounts AS [Use Counts], st.TEXT AS [SQL Batch], qp.query_plan AS [Query Plan] FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st WHERE cp.objtype='Proc' ) AS TMP_TBL WHERE [SQL Batch] LIKE '%GetProductionList%' |
In this step, we will execute our sample stored procedure with a random parameter and will take a look at the extended event.
|
1 |
EXEC GetProductionList @ProductIdNumber = 757 |
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.
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.
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.
|
1 2 3 |
SET NUMERIC_ROUNDABORT ON GO EXEC GetProductionList @ProductIdNumber = 757 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT * FROM ( SELECT pa.attribute,pa.value,cp.objtype AS [Plan Type], OBJECT_NAME(st.objectid,st.dbid) AS [Object Name], cp.refcounts AS [Reference Counts], cp.usecounts AS [Use Counts], st.TEXT AS [SQL Batch], qp.query_plan AS [Query Plan] FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa WHERE cp.objtype='Proc' and pa.attribute = 'set_options' ) AS TMP_TBL WHERE [SQL Batch] LIKE '%GetProductionList%' |
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.
|
1 |
EXEC sp_recompile N'GetProductionList' |
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.
Now, we re-execute our sample SQL Server stored procedure and analyze its behavior using the extended event.
|
1 |
EXEC GetProductionList @ProductIdNumber = 757 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE OR ALTER PROCEDURE GetProductionList @ProductIdNumber AS INT WITH RECOMPILE AS SELECT p.[ProductID] ,p.[Name] ,pm.[Name] AS [ProductModel] ,pmx.[CultureID] ,pd.[Description] FROM [Production].[Product] p INNER JOIN [Production].[ProductModel] pm ON p.[ProductModelID] = pm.[ProductModelID] INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx ON pm.[ProductModelID] = pmx.[ProductModelID] INNER JOIN [Production].[ProductDescription] pd ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID] WHERE p.ProductID =@ProductIdNumber |
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.
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.
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.
|
1 |
EXEC GetProductionList @ProductIdNumber = 757 WITH RECOMPILE |
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
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023