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 |
|
laddu
Constraint Violating Yak Guru
332 Posts |
 Posted - 2014-09-08 : 16:30:26
|
| Hi,Stored procedure runs and completes in 30 sec for the first time. If I run the same SP with same parameter for the second time it is taking more than 30 min and not completes..If I drop and recreates the SP it is completing in 30 sec for the specific parameter1. But I apply different parameter2 it is running forever. If i drop and recreate again it is completing in 30 sec for parameter2 but running forever for parameter1. Weird situation...anyone encountered this type of issue? Please advise.I tried restarting SQL and also applied missing indexes but NO luck. |
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-09-08 : 16:39:58
|
| not sure why the same parameter the second time you run is taking 30 minutes unless there is some blocking, but the rest sound slike parameter sniffing. exec sp_who2 |
 |
|
|
laddu
Constraint Violating Yak Guru
332 Posts |
Posted - 2014-09-09 : 11:16:18
|
| Thanks MichaelJSQL! NO blocking when I ran the same parameter second time...However issue is resolved.I have dropped and recreated the SP “WITH RECOMPILE” option. Now SP is running fine with all parameters (execution time is less than 10 sec) |
|
|
|
|
|
|
stored procedure execution