Unanswered: Issues with sp_recompile on store procs
I am planning on putting sp_recompile on some of my store procs. I know that with sp_recompile following thing happens:
In the create procedure statement, the optional clause with recompile comes just before the SQL statements. It instructs Adaptive Server not to save a plan for this procedure. A new plan is created each time the procedure is executed.
In the absence of with recompile, Adaptive Server stores the execution plan that it created. Usually, this execution plan is satisfactory.
HOwever I was wondering with the introduction of sp_recompile will there be any issues to the
a)performance of the db when concurrent users are there in the db.
b)cache gets affected by any means.
b)Any other known issues.
The only issue I can think of is that with the introduction of sp_recompile, the procedure will take longer to run i.e.
1. they will need to be recompiled
2. a new query plan will need to be created each time
3. then the final execution.
Only if your SPs return data from huge tables and the query returns different values based on search arguments, you should use "create SP with sp_recompile".