recompiling stored procedures 1

[icemel]

Hi,

Is it ok to recompile stored procedures on a live, production database?

That is, what happens if someone is in the middle of calling the old stored procedure (probably no effect) or is about to call the stored procedure, but it is in the middle of a recompile?

Is this a problem.

Thanx

 

[druer]

My understanding is that there shouldn't be a problem. If someone is still in the middle of running the old version they are using it from the compiled cache. While the new one is being recompiled (albeit really fast.) As far as I can guess something would happen after the recompile completes to mark the currently cached version obsolete so the next call to use it (one that hadn't already started) would force it to load the new version to cache and then it would use the new version.

 

[mrdenny]

When you mark a procedure for recombile any one that is currently using it will continue to use the cashed version. The next person to execute the procedure will have to wait the split second while the execution plans are compaired and the correct plan is selected.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com