June 16, 2011 at 10:32 pm
Hi gurus
have a question:
what happens if we alter a procedure when it is being executed?
I used the below process to test :
create proc timedelay
as
print 'start of run'
waitfor delay '000:00:20'
print 'end of run'
exec timedelay
alter proc timedelay
as
print 'start of run'
waitfor delay '000:00:30'
print 'altered'
print 'end of run'
--exec timedelay
I found that the proc executed the first version even when i have altered the proc.
Is it because the proc query plan was already put in the cache?
Would like to know the details.
June 17, 2011 at 1:29 am
If you alter a proc while it's running, the currently executing one uses the original definition, any new execution uses the new.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2013 at 8:32 am
Is there any documenation that explains current process continues to use the old procedure and new processes will use the new code?
I've searched for over an hour and this is the only post/documentation I've found.
Management wants the documentation reference before running an alter procedure (hot fix) in production.
October 24, 2013 at 8:51 am
I couldn't find any documentation either, but they need to understand how a stored procedures and queries work. When you execute a stored procedure a lot of stuff happens, but basically it will create an execution plan (if one doesn't already exists or it can't be reused). Once the execution plan has been created (or retrieved) that is what the engine uses from that point on. The stored procedure is done at this point until it is called again.
What happens if you are in the middle of generating a plan from the stored procedure when you apply the new version? Their is a shared schema lock placed on the stored procedure while a plan is being generated (or retrieved) that is not compatible with the schema modification lock that is needed by the alter command. So, the stored procedure won't be updated until the previous call has finished with it.
This is a very high overview of what happens, but hopefully it helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply