Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ThreePea
Yak Posting Veteran
83 Posts |
 Posted - 2003-09-12 : 11:11:36
|
| Just like the subject says - if I ALTER a procedure, is that procedure automatically marked for recompilation? Or should I be doing an sp_recompile every time I alter a proc?I have a sneaking suspicion that the procedure editor in Enterprise Manager marks the proc for recompilation when you Apply your changes. But I'm trying to figure out what I should do if I'm using Visual Interdev or Query Analyzer, both of which use ALTER PROCEDURE.Thanks.ThreePea==================================================Tolerance is the last virtue of an immoral society. -- G.K. Chesterton |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-12 : 13:00:53
|
BOL:quote:
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}RECOMPILE indicates that Microsoft® SQL Server™ does not cache a plan for this procedure and the procedure is recompiled at run time.
So if you don't specify the option, would the converse be true?Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-12 : 13:11:39
|
| I'm curious as to why you think that Enterprise Manager would do something different than Query Analyzer. Why are you even modifying stored procedures in Enterprise Manager in the first place? Query Analyzer is a much better editor and allows you to specify RECOMPILE.Tara |
|
|
|
ThreePea
Yak Posting Veteran
83 Posts |
Posted - 2003-09-12 : 13:19:38
|
quote:
Originally posted by X002548
BOL:quote:
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}RECOMPILE indicates that Microsoft® SQL Server™ does not cache a plan for this procedure and the procedure is recompiled at run time.
So if you don't specify the option, would the converse be true?
I would think so. But circumstances have led me to believe otherwise. See my next response for the dirty details.==================================================Tolerance is the last virtue of an immoral society. -- G.K. Chesterton |
|
|
|
ThreePea
Yak Posting Veteran
83 Posts |
Posted - 2003-09-12 : 13:25:48
|
quote:
Originally posted by tduggan
I'm curious as to why you think that Enterprise Manager would do something different than Query Analyzer.
We have seen stored procedures modified with ALTER PROCEDURE that didn't seem to have some performance gains that we expected. When we pulled those procs into EM and hit [Apply], the performance immediately improved. This implies that the procs didn't get marked for recompile until we modified them in EM.As Brett just said, I if you left off the "WITH RECOMPILE" option when you Altered the proc, that would imply that it doesn't recompile each time, which would also imply that SQL Server is using the execution plan from the previous version of the procedure.quote:
Originally posted by tdugganWhy are you even modifying stored procedures in Enterprise Manager in the first place? Query Analyzer is a much better editor and allows you to specify RECOMPILE.
Well, it wasn't my intention to have any sort of religious war on which editor to use. Besides, I never claimed that we used Enterprise Manager to edit procedures, did I.  ThreePea==================================================Tolerance is the last virtue of an immoral society. -- G.K. Chesterton |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-12 : 13:30:10
|
| You should run SQL Profiler to determine what happens when you hit apply in Enterprise Manager. SQL Profiler will show you the exact command.Tara |
|
|
|
|
|
|
|
|
Recompile happen with ALTER PROCEDURE?