SQL Server Stored Procedure Recompilation Factors

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.

CREATE PROCEDURE GetSalesPersonSale

@LineTotal AS FLOAT

SELECT

soh.[SalesPersonID]

,e.[JobTitle]

,st.[Name] AS [SalesTerritory]

,soh.[SubTotal]

,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear]

FROM [Sales].[SalesPerson] sp

INNER JOIN [Sales].[SalesOrderHeader] soh

ON sp.[BusinessEntityID] = soh.[SalesPersonID]

INNER JOIN [Sales].[SalesTerritory] st

ON sp.[TerritoryID] = st.[TerritoryID]

INNER JOIN [HumanResources].[Employee] e

ON soh.[SalesPersonID] = e.[BusinessEntityID]

INNER JOIN [Person].[Person] p

ON p.[BusinessEntityID] = sp.[BusinessEntityID]

INNER JOIN Sales.SalesOrderDetail sd

ON sd.SalesOrderID = soh.SalesOrderID

WHERE sd.LineTotal>@LineTotal

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.

CREATE EVENT SESSION [MonitorStoredProcedureRecompile] ON SERVER

ADD EVENT sqlserver.sql_statement_recompile(SET collect_statement=(1)

ACTION(sqlserver.client_app_name,sqlserver.database_name,sqlserver.sql_text,sqlserver.username))

WITH (STARTUP_STATE=ON)

ALTER EVENT SESSION MonitorStoredProcedureRecompile

ON SERVER

STATE = START

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.

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.

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.

1	EXEC GetSalesPersonSale 10

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.

USE AdventureWorks2017

CREATE NONCLUSTERED INDEX IX_Linetotal

ON [Sales].[SalesOrderDetail] ([LineTotal])

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.

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.

INSERT INTO Sales.SalesOrderDetail

(SalesOrderID,CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID,UnitPrice,rowguid,ModifiedDate)

SELECT SalesOrderID,CarrierTrackingNumber,OrderQty,

ProductID,SpecialOfferID,UnitPrice,NEWID(),ModifiedDate

FROM Sales.SalesOrderDetail

GO 3

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.

1	EXECUTE GetSalesPersonSale 10

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.).

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.

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

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

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

1	EXECUTE GetSalesPersonSale 10

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.

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.

CREATE PROC TempTableCompileDemo

CREATE TABLE #TempSales

(SalesOrderID INT , CarrierTrackingNumber VARCHAR(100))

INSERT INTO #TempSales

SELECT TOP 100 SalesOrderID,CarrierTrackingNumber FROM Sales.SalesOrderDetail

DROP TABLE #TempSales

EXEC TempTableCompileDemo

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.

CREATE PROC TempTableEveryCompileDemo

CREATE TABLE #TempSales

(SalesOrderID INT , CarrierTrackingNumber VARCHAR(100))

INSERT INTO #TempSales

SELECT TOP 100 SalesOrderID,CarrierTrackingNumber FROM Sales.SalesOrderDetail

ALTER TABLE #TempSales ADD NewCol1 INT

UPDATE #TempSales SET NewCol1=0

EXECUTE TempTableEveryCompileDemo

GO 10

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.

CREATE PROC TempTableOptionCompileDemo

SELECT 'Hello World' + NULL

SET CONCAT_NULL_YIELDS_NULL OFF;

SELECT 'Hello World' + NULL

EXECUTE TempTableOptionCompileDemo

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

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

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