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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 stored procedure execution

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

Posted - 2014-09-08 : 16:38:34
sounds like parameter sniffing, but also check your statistics

https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/
http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options
Go to Top of Page

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
Go to Top of Page

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)
Go to Top of Page
   

- Advertisement -