May 20, 2008 at 5:37 am
we use alter statements for our stored procedures when rolling out a new version of our app. We have noticed that when viewing the stored proc in production it looks like the altered one, but when the app runs it, the changes are not taking effect. Has any one else experienced anything like this? It's like the old version is still being held on too.
To get around the problem we have been dropping and recreating the procedures.
Any help would be appreciated. Thanks!
May 20, 2008 at 8:49 am
Do you have WITH RECOMPILE line in ALTER PROCEDURE statements?
Try it, if it works then this is a compilation issue. Do not use this statement permanently because the procedure will be recompiled every thime it runs, but just for the test
Regards,Yelena Varsha
May 20, 2008 at 8:51 am
no.
May 20, 2008 at 8:53 am
You may also want to try
DBCC FREEPROCCACHE
to make SQL Server recompile everything
Regards,Yelena Varsha
May 20, 2008 at 9:03 am
i would just include the sp_recompile command at the end of your alter script (after a go command, you dont want it as part of the SP).
May 20, 2008 at 9:07 am
sp_recompile is good!
I suggested DBCC FREEPROCCACHE because I guessed that with the application update they may have many altered procedures and statements
Regards,Yelena Varsha
May 20, 2008 at 11:36 am
Altering a procedure automatically invalidates its cached plan.
Check to make sure you don't have 2 versions, one in the dbo schema and one in a different schema
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
May 20, 2008 at 11:50 am
Good thought!
That is exactly why I was interested if recompilation helps - if you explicitly recompile and it does not help, then it is a wrong server or a wrong database or a wrong owner or a cached application.
If the new results from the updated procedure were expected through the application, then the cached pages or classes may be the reason. I had it this week. The vendor sent us the application update, I installed it, something did not work correctly, I restarted the website to flush the cached pages, same result, nobody touched the thing and it is working correctly today. We did have this issue before with Java classes.
Regards,Yelena Varsha
May 20, 2008 at 11:55 am
I've seen that with web pages before. Very irritating.
With stored procs, as soon as the proc is modified, or any object that the proc depends on is modified, the cached plans will be flagged as invalid and the procedure will be recompiled next time it runs. There's no need to manually flush the cache or mark the proc for recompilation.
The wrong server/wrong database is also an option. Profiler's useful if you're not sure what's been called from where.
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
May 20, 2008 at 12:04 pm
Do we know for sure that the plan is being marked as invalid?
May 20, 2008 at 12:35 pm
Sue,
I think your issue is not in the stored procedure plan being cached.
You say when the application runs you are getting the old results. Please test the stored procedure itself using Query Analyzer or Management Studio Query window if it would return the correct result. Not the text of it, but how it runs. If it runs correctly, then you have to restart the web server or the application server, whatever you are using. Also check who is the owner of the stored procedure when you run Alter Procedure and when you re-create the procedure, Gail may be right here.
Regards,Yelena Varsha
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply