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 |
|
rfnoteboom
Starting Member
4 Posts |
 Posted - 2002-10-18 : 05:29:48
|
| We are experiencing a very strange problem in several of our development/testing databases.A stored procedure that has been in the database for several months takes >30 seconds to execute; when I clone it (i.e. a copy with a new name) it takes <2 seconds. Does anyone have any ideas as to what could cause this problem? |
|
|
Max_rv
Starting Member
3 Posts |
Posted - 2002-10-18 : 05:56:29
|
| try this:sp_updatestats |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-18 : 08:59:11
|
| Also try recompiling it:EXECUTE myStoredProcedure @parameter=1 WITH RECOMPILEDo this after you run sp_updatestats. The original procedure is probably using an old plan that doesn't match the data changes since the last recompile. The copy would've been compiled using the latest stats. |
|
|
|
rfnoteboom
Starting Member
4 Posts |
Posted - 2002-11-07 : 07:25:52
|
| Unfortunately, your solutions didn't help. DBCC FREEPROCCACHE and a subsequent WITH RECOMPILE doesn't help either. (I must be doing something wrong)Only dropping the sp and recreating it has any effect. I've noticed that the (estimated) execution plans differ (visually - I'm no good at comparing them semantically) before and after.Could it be related to changes in in the underlying views and tables? |
|
|
|
|
|
|
Problem with SP Execution times