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:
- 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.
- 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, sv.subclass_value 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
go
if OBJECT_ID('usp_Recompile') is not null
drop procedure usp_Recompile
go
create procedure usp_Recompile
as
create table #TempTable (a int)
select * from #TempTable
go
If we execute the stored procedure usp_Recompile, we’ll see what is shown in Figure 1 below.
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
go
if OBJECT_ID('usp_DDLDML') is not null
drop procedure usp_DDLDML
go
create procedure usp_DDLDML
as
-- 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
go
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.
Conclusion
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.
Leave a Reply