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