Post reply

Dropping Stored Procedures

  • March 19, 2008 at 12:38 am

    #121163

    Hi Experts

    I have to update a database with changes made to some stored procedures. Which is best way 1) dropping and re-creating all proceduers or 2) running the alter script of the changed procedures. Are there any performance or other issues with either of the approaches?

    What would happen to the execution plans of the procedures ?

    "Keep Trying"

  • March 19, 2008 at 1:20 am

    #791305

    When you do drop & recreate SP you need to reset the exe permission.

    While Alter SP will keep the previlages as it is.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • March 19, 2008 at 12:08 pm

    #791631

    Alter is much better.

    As the previous poster noted, it keeps the permissions settings. Plus it's faster and much better at preserving/repairing the dependencies.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • March 19, 2008 at 12:36 pm

    #791654

    Alter is good if you want to ensure existing security setting on SP otherwise it does not matter.

    [font="Verdana"]--www.sqlvillage.com[/size][/font]

  • March 20, 2008 at 3:03 am

    #791881

    Hi

    What i really wanted to know was about execution plans.

    I believe that dropping and recreating the stored procedures

    will drop the existing executions plans of the stored procedures and create new plans. Am i rt? Does this affect performance in any way ?

    "Keep Trying"

  • March 20, 2008 at 3:12 am

    #791883

    I don't know because I haven't tested, but I would guess that ALTER and DROP/CREATE would both cause the procedure to be recompiled at next execution. This is a good thing, because if you have changed the procedure then you don't want it using an out-of-date execution plan.

    John

  • March 20, 2008 at 3:23 am

    #791884

    Chirag (3/20/2008)

    Hi

    What i really wanted to know was about execution plans.

    I believe that dropping and recreating the stored procedures

    will drop the existing executions plans of the stored procedures and create new plans. Am i rt? Does this affect performance in any way ?

    The alter statement seems to drop the relevant execution plans for the statements in the stored procedures the same way as a drop statement. The best thing to do is to experiment a bit with this. (Use sys.dm_exec_query_stats and sys.dm_exec_sql_text) to see what is in the execution cache.

    Concerning performance cost. How long does it take to compile your stored procedure? How many times are you altering it? I.e. is it a one off cost or you are modifying your stored procedures very frequently?

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • March 20, 2008 at 11:16 pm

    #792343

    If you're scripting your changes manually I'd take a good look at some of the tools available from Redgate, Apex, etc. which will identify the differences between database A and database B and generate a change script automatically including permissions, etc.

    The redgate folks do a good job at not being too partisan (Andras, previouse poster) on the forums but I can be... I'm a big fan fan of redgate's tools, they have saved me thousands of hours over the years and even better, they keep me out of trouble/allow me to be very confident that my changes will work correctly the first time.

    Joe

  • March 24, 2008 at 2:54 am

    #792935

    Hi

    Thanks everyone...:) .

    My procedures get changed daily and i cant generate the alter script as iam using SQL 2005 RTM ( i cant change it).

    "Keep Trying"

  • March 24, 2008 at 8:29 am

    #793024

    Chirag (3/24/2008)

    My procedures get changed daily and i cant generate the alter script as iam using SQL 2005 RTM ( i cant change it).

    How does SQL 2005 RTM prevent you from generating the alter scripts?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • March 24, 2008 at 10:44 am

    #793118

    In addition to what was said above, by dropping/recreating you also loose all your changes history. Normally, you can use the query below to determine when a stored procedure was last modified. If you drop/create, you lose the history becuase the createdate and date modified will always be the same.

    SELECT name, create_date, datepart(dy,create_date) as CreatedDayOfYear,

    modify_date, datepart(dy,modify_date) as ModificationDayOfYear

    FROM sys.sql_modules

    JOIN sys.objects

    ON sys.sql_modules.object_id = sys.objects.object_id

    AND TYPE = 'P'

    order by datepart(yyyy,modify_date) asc,

    datepart(dy,modify_date) asc, name;


    My blog: http://jahaines.blogspot.com

  • March 25, 2008 at 12:33 am

    #793341

    Hi

    RbarryYoung - RTM version does not have a option where we can generate alter script for all the procs that have been modified. Rightclick on the database\Tasks\ Generatescripts has pptions only to Drop procs and Create procs. For that matter we cant create the drop and create in a single script.

    Adam - You are right about loosing the change history.

    Thanks Guys

    "Keep Trying"

  • March 25, 2008 at 7:41 am

    #793486

    Chirag (3/24/2008)

    My procedures get changed daily and i cant generate the alter script as iam using SQL 2005 RTM ( i cant change it).

    Chirag,

    I'd worry about that problem more than any... something is wrong if you have to change the procedures daily... real wrong. Must be some date or filename dependencies that could easily be calculated instead of the hardcoding you probably have in your procs.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 26, 2008 at 12:19 am

    #793868

    Chirag (3/25/2008)

    RbarryYoung - RTM version does not have a option where we can generate alter script for all the procs that have been modified. Rightclick on the database\Tasks\ Generatescripts has pptions only to Drop procs and Create procs. For that matter we cant create the drop and create in a single script.

    Good point. Easily fixed though: Generate the "CREATE" script into a new query window. Then, Ctrl+H to Search and Replace "Create Procedure " with "Alter Procedure".

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • March 26, 2008 at 1:11 am

    #793882

    Hi

    Jeff - we are still in the development phase of the project. What i meant was that daily there are some modifications to the procedures. This does not mean that all procedures are modified daily or the same set of procedures get modified daily.

    RBarryYoung - Thats a nice idea you pointed out.

    Some time we miss the simplest of things . Thanks :hehe:

    "Keep Trying"

Viewing 15 posts - 1 through 15 (of 36 total)

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