Alter procedure versus Create procedure

Question

Alter procedure versus Create procedure

Zohaib Anwar

Ten Centuries

Points: 1120
More actions
September 23, 2014 at 9:14 am

#296821

After performing an ALTER PROCEDURE, is it possible that any executions of that procedure would still be using the pre altered version?
I ask because I have seen a procedure run slowly until it was recompiled, but not being a DBA I wanted make sure I saw what I thought I saw.
Your answer will help us decide if we want to change our standard practices for updating procedures.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101126 More actions · Answer 1

Yes. If the procedure was already executing, that execution would still use the old code.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Zohaib Anwar Ten Centuries Points: 1120 More actions · Answer 2

THANKS for the reply.

So, what is the solution of this problem?

any help will be highly appreciated.

ScottPletcher SSC Guru Points: 101126 More actions · Answer 3

SQL won't automatically KILL running tasks.

Your best bet is to force them to recompile immediately. AFAIK, the only way to do that is to have it affect the entire db using the FLUSHPROCINDB command:

DECLARE @db_id int

SELECT @db_id = database_id

FROM sys.databases

WHERE name = 'your_db_name'

DBCC FLUSHPROCINDB ( @db_id )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Zohaib Anwar Ten Centuries Points: 1120 More actions · Answer 4

Zohaib Anwar

Ten Centuries

Points: 1120

September 26, 2014 at 5:00 am

#1748433

Thanks for the reply 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

You don't need to flush the cache.

An ALTER PROCEDURE invalidates the procedure's plan as it is. Wiping every single other query's and procedure's plan out of cache as well is just overkill.

Anyone calling the procedure after the ALTER has run will run the new procedure. The first such call will generate a new plan.

Anyone who is running the procedure (part way through execution) when the ALTER is run will finish running the version they started. Only way to do anything about that is to kill the sessions which are in the middle of running that procedure, but that could have adverse effects on the data, cause application errors, etc. Forcing a clear of the entire plan cache won't affect sessions which are already running the modified stored procedure.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass