SQL Server Performance Forum – Threads Archive
can I run sp_recompile for a live system?
Can I schedule a regular job to compile all of user-definedstored procs/trigs and UDFs? Is it true when I run this command against a proc,
this proc will be cleaned out of cache, and next time it
is executed, it will do three things:
(1) drop this proc;
(2) create this proc;
(3) exec this proc. So in theory, there should be no failed transaction because of
step (1)? Thanks.
No it won’t be dropped and re-created, simply the next time it’s run, its execution plan will be recreated from fresh. This will slow the SP down slightly on first use following recompile, but you may get subsequent benefits from having a more up-to-date plan. Tom Pullen
DBA, Oxfam GB
True and no issues running or scheduling against production servers, so far so good at my end. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
No issues, I am running it on live system weekly -Anup
It’s fine running against SPs with only queries in them. But, becareful running against SPs with update or insert statements in them. Because, if your recompile job runs while one of such SP is in the middle of it’s execution, the SP may fail with an error as below:
The definition of object <your SP> has changed since it was compiled.
]]>