Sp_recompile doesn't recompile!!!!

Many of us use the stored procedure "sp_recompile" to mark the stored procedure for recompilation, so that next time it runs we get a new plan. But does it actually recompile? Lets check.

Consider the stored procedure "usp_people_income"

Step 1: Lets mark the stored proc for recompilation

sp_recompile 'usp_people_income'

Step 2: Start a extended event trace for the event "sql_statement_recompile" to see if our call to the stored procedure actually results in recompilation

Step 3: EXEC usp_people_income 5000,5010

You would notice that the extended event trace didn't return any result indicating that the stored procedure didn't recompile. 

So, does "sp_recompile" really work?

Yes. It does.

How???

Though, it doesn't actually force a recompilation, it actually achieves the same effect by removing the plan from the cache. At a subsequent call, new plan is actually inserted to the cache. You may check the same in the following way.


Step 1: Start a extended event trace for the events

Turn on the extended events sp_cache_insert,sp_cache_remove events

Step 2: Mark the stored proc for recompilation again

EXEC sp_recompile 'usp_people_income'

Step 3: Run EXEC usp_people_income 5000,5010

You may notice the following events fired in order

1) sp_cache_remove event immediately after "sp_recompile" is executed
2) sp_cache_insert event just before the stored procedure is executed.

Refer to screenshot provided below.