0

I have a Stored Procedure which I want to recompile. is it safe to do that in a Live environment ? Because there's continuous flow of data which is being processed by the Stored Procedure and the SQL Server 2008.

Ram Mehta
  • 449
  • 1
  • 6
  • 20

1 Answers1

3

Depending upon the method used (i.e. do not drop and recreate the procedure using the WITH RECOMPILE option), this is perfectly safe to execute on the Live environment.

If using sp_recompile, the stored procedure will simply be flagged to cause a new query plan to be calculated the very next time it is executed. This may cause a very small delay whilst the new query plan is compiled but it will be negligible:

exec sp_recompile N'<your procedure>'
Martin
  • 16,093
  • 1
  • 29
  • 48
  • So It would be safe to go with the command exec sp_recompile right ? – Ram Mehta Feb 20 '14 at 10:58
  • 1
    @RamMehta It will be safe to use this. As I say, it simply flags the procedure to be recompiled the very next time it is executed (which will happen automatically). – Martin Feb 20 '14 at 11:29