locked
What happen when a store procedure is update in a server and someone is using it? RRS feed

  • Question

  • Hello

    In case that, for instance 100 persons are using a database system and we need to upload a store procedure by emergency.

    It's posible upload the store procedure when the application is executing the procedure.

    What happen exactly?

    Saturday, April 2, 2016 5:17 AM

Answers

  • Sure why not.

    Sessions that are under execution will continue to use old compiled plan (Unaffected because the code is already under execution)  for that SP. The moment changes are applied to this SP, SQL Server will flush out the old plan and start using the new one.


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by neonash Monday, April 4, 2016 3:33 AM
    Saturday, April 2, 2016 9:44 AM

All replies

  • Hello,

     What do you mean "happen"?  There are too many factors to just say, this, that, and then, response.

    I am sure your server has documents that explain standard procedures for setup, maint., etc.

      Please provide more details about the scenerio you are asking about.

    Thanks :)


    Saturday, April 2, 2016 5:49 AM
  • Hi neonash,

    Could you please be more specific?

    In the meantime, as far as I know, when you execute one sp, system takes it into memory, analyses and after parsing and binding all the underlying objects, Optimizer produces the best plan possible. I mean, all the stuff is loaded in memory so that you can alter that object.

    In any case, other experts here could explain all of this deeper


    Saturday, April 2, 2016 8:36 AM
  • Sure why not.

    Sessions that are under execution will continue to use old compiled plan (Unaffected because the code is already under execution)  for that SP. The moment changes are applied to this SP, SQL Server will flush out the old plan and start using the new one.


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by neonash Monday, April 4, 2016 3:33 AM
    Saturday, April 2, 2016 9:44 AM
  • You mean a new version ? You may get  a schema lock it the sp is currently running

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, April 3, 2016 5:22 AM
    Answerer
  • The question is not fully clear. Can you be more specific?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, April 3, 2016 7:12 AM
    Answerer
  • Greetings Neonash,

    although I might be a tad too simplistic in the answer (i'll explain why);

    When SQL-server starts an execution it will interpret the commands given and create a queryplan.
    this plan will be executed.

    So if you have a stored procedure that runs for an hour...
    In the first minute person A starts it and gets a plan and that plan's interpretation starts to run.

    If you then update the stored-procedure, SQL will ask for a schema-lock for that SP and after getting it will create the new logic. Note; as executions are always done by the created queryplan it will not wait for running/old executions.

    So if person B starts the procedure after this...and person A gets the old procedure's results and  person B gets the new Procedure's results.

    Now as I said its a fairly simplistic version, of course it can happen that a plan is so complicated SQL will interpret only parts and not the full query...(or you have With Recompile, temp-tables with indexes etc) in which, depending on the timing, the query will rehash parts of the new query and that may result in odd results.

    Normally the statement is to create a maintenance window to update business-logic for applications (and databases). But to say 'what happens', it mostly depends on the length of execution (longer executions --> more risk), the logic used and of course the impact of the change.

    By rule of thumb;
    If it's sever enough to be done by an hot-fix; its sever enough to ask for a 5 minutes period of none-use.
    Lunch is a wonderful time to work!

    With kind regards,

    Sebastian

    Sunday, April 3, 2016 10:52 AM
  • Yes !! I have tested Manu answer is correct !!

    -- Created Procedure

    CREATE

    PROCEDUREtest2 AS

    begin

    SELECT

    *FROMdbo.emp e



    WAITFOR

    DELAY'00:00:30'


    END

    -- executing procedure in session -1

    EXEC Test2

    In another session altering procedure and executing even first one is running

    Alter

    PROCEDUREtest2 AS

    begin

    SELECT

    *FROM  dbo.employeeDataValid edv



    WAITFOR

    DELAY'00:00:30'


    END



    exec

    test2

    So its altered successfully and executed successfully!!


    Regards Vikas Pathak

    Wednesday, April 6, 2016 1:28 PM