Understanding Recompiling Statements with SQL Server

By Pinal Dave on November 19, 2014

Recently, I heard a story that was quite compelling. A retired naval officer used to get to the top of a bridge and keep waving his hand everyday around 8 PM. The passer-by used to think the elderly man had gone nuts and, after awhile, ignored his actions. One fine day, a stranger saw this and was amused. Being curious, this young man made sure he stopped and asked this elderly man about his actions. He asked,“Sir, why do you come every day to the river and wave your hand so enthusiastically? You don’t seem to miss a single day. Why?” To this the elderly man replied, “Young man, I am just making sure the whales don’t turn up.” Laughing, the young man replied, “I have not seen a single whale here, sir.”  The elderly man replied, “And I am glad to hear that! I must be doing my job really well, then.”

There is a morale to this story: if we keep doing something day in and day out, we will surely become very good at it as we have mastered the art through lots of practice. This holds true even with software systems. SQL Server has its own way of optimizing repeated calls to the same stored procedure–it is called stored procedure caching. There are times when SQL Server thinks the cache is no longer valid and goes into a mode of recompilation. In this article, we will look into this process.

Before trying to understand recompiling, though, it’s important to understand compilation in SQL Server. The goal of compilation is to reuse the plan by caching it in memory. The query plans are cached because compilation of a query is a time consuming task. If SQL Server has already spent time in finding the optimal plan for a given query, why do it again? Compiled plans can be reused and precious CPU cycles saved if it doesn’t have to find the query plan again and again.

Understanding Recompilation

Once the plan is cached, there are situations when the optimizer feels something has changed and it has to compile/generate the query plan again, this is called Recompilation. Recompilation is generally caused by these events:

  1. Plan Stability related: If the plan is not recompiled, we might get unexpected results. Imagine if we have altered the table and added a new column. Then the plan referring the object would be invalidated.
  2. Plan optimality related: If the data in underlying tables is changed considerably. The query optimizer would detect such situations and would force a recompile.

There are various ways by which recompile can be captured. Easiest way is to capture profiler trace. There are two events available in profiler called “SP:Recompile” and “SQL:StmtRecompile”

Below is the query which can identify various possible reasons which is stored in profiler related catalog views. Note that both the above events have exactly same reasons:

SELECT      sv.subclass_name,


FROM        sys.trace_events AS e,

            sys.trace_subclass_values AS sv

WHERE       e.trace_event_id = sv.trace_event_id

AND         e.name = 'SP:Recompile'

AND         sv.subclass_value  < 1000

ORDER BY    sv.subclass_value;

Here is the output from this query:

subclass_name subclass_value
Schema changed 1
Statistics changed 2
Deferred compile 3
Set option change 4
Temp table changed 5
Remote rowset changed 6
For browse permissions changed 7
Query notification environment changed 8
PartitionView changed 9
Cursor options changed 10
Option (recompile) requested 11
Parameterized plan flushed 12
Test plan linearization 13
Plan affecting database version changed 14


These can be seen in the Profiler, under the “Event Subclass” column, whenever there is a recompile event. Let us quickly have a look at few causes of recompilation in action.

Scenario # 1 – Deferred compile

Create below stored procedure in the database.

use AdventureWorks2014


if OBJECT_ID('usp_Recompile') is not null

drop procedure usp_Recompile


create procedure usp_Recompile 

create table #TempTable (a int)

select * from #TempTable


If we execute the stored procedure usp_Recompile, we’ll see what is shown in Figure 1 below.

Pinal Dave SQL Server Recompilation-01

Figure 1

The SP:Recompile event will be raised directly following the SP:StmtStarting event of the stored procedure statement that caused the recompilation. After the recompile event is finished, we will see a repeat of the SP:StmtStarting event, indicating that the statement is executing with the newly generated plan.

Scenario # 2 – Schema Change

Here is the stored procedure which I have created for demo purposes.

use AdventureWorks2014


if OBJECT_ID('usp_DDLDML') is not null

drop procedure usp_DDLDML


create procedure usp_DDLDML


-- DDL

create table t1 (a int)

-- Select before index

select * from t1

-- DDL

create index idx_t1 on t1(a)

-- Select after index

select * from t1

-- drop

drop table t1



When we execute the procedure first time, we would see “Deferred compile” but second time we would see “Schema Changed,” as shown in Figure 2 below.


Figure 2

Here, too, we can see a statement level recompile. This is the feature which was introduced in SQL Server 2005 to avoid recompilation of all statemenst in procedure to save CPU cycles. Other reasons can also be found by capturing profiler.


Compilation is inevitable inside SQL Server, and some of the reasons for recompilations are reasons that are required for SQL Server to function properly. If a server has too many recompilations/sec,  it’s important to evaluate the causes. This article gives you a way to identify the possible cause of recompilations inside a stored procedure.


Related Posts

Leave a Reply