Manual recompile stored procedure SQL Server

Question

How can I recompile manually a stored procedure in SQL Server? Is just like a drop and create?

I know can do it with an option in the create statement (WITH RECOMPILE), and it will be recompiled every execution, and executing sp_recompile @procedureName, which will only recompile it the next time it executes, but how can I recompile it manually without these two approaches?

[ALTER PROCEDURE](http://technet.microsoft.com/en-us/library/aa225939(v=sql.80).aspx) — bummi, Sep 02 '14 at 11:58
DROP/CREATE also compiles the proc the next time it is executed so I think sp_recompile is what you want. — Dan Guzman, Sep 02 '14 at 12:11

score 3 · Answer 1 · answered Sep 02 '14 at 13:31

Stored Procedures are compiled when they are "first" executed. "First" meaning, they have no chached plan. All you can do is invalidate the cache. In SQL-Server, there is no way to force compilation other than calling it.

(BTW, this is unlike Oracle, where you may have gotten the idea from)

In our application, we maintain a script called "run_sample_statements.sql". This contains a number of representative queries and procedure calls to cause compilation and caching. We used that after maintenance and index-rebuilds to test/precache the system before we release it for the users again.

score 0 · Answer 2 · answered Sep 02 '14 at 12:21

Well, one way to do it is to find the plan handle by querying

Select st.Plan_handle 
from 
sys.dm_exec_cached_plans 
    CROSS APPLY 
sys.dm_exec_sql_text(plan_handle) st 
WHERE text like '% your proc name %'

Then DBCC FREEPROCCACHE (plan_handle)

This method is effectively the same as sp_recompile.

Manual recompile stored procedure SQL Server

2 Answers2