Dropping Stored Procedures

Question

Post reply

Dropping Stored Procedures

ChiragNS

One Orange Chip

Points: 26137
More actions
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"

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

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

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."

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

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]

Mohan Kumar SSCertifiable Points: 5365 More actions · Answer 3

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]

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

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"

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 6

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

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 7

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 8

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"

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

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]

Adam Haines SSC-Insane Points: 23217 More actions · Answer 10

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 11

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"

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

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]

ChiragNS One Orange Chip Points: 26137 More actions · Answer 14

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"