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
 General SQL Server Forums
 New to SQL Server Programming
 why it behaves like this?

Author  Topic 

allan8964
Posting Yak Master

249 Posts

Posted - 2013-04-08 : 12:24:47
Hi there,

I have a store procedure with 5 parameters. It runs slow. If I declare and init these 5 parameters inside the sp and highlight the scripts to run it, it runs fast, about 1 sec. But if I run it with statement as:

exec mysp p1, p2, p3 ..

it runs but takes 2 min to finish. not showing any error. What could be the reasons to cause this? Thanks in advance.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-08 : 12:29:50
Most likely the stored proc ends up using a stored execution plan that is not the most appropriate for the given set of parameters. People refer to this as "parameter-sniffing" problem - you will find references if you google for it. Examine the query plan and adding statement level recompilations at the approriate places may help.
Go to Top of Page

allan8964
Posting Yak Master

249 Posts

Posted - 2013-04-08 : 17:28:08
Thanks James, it's very helpful. I ran the query plan and found warnings showing MISSING INDEX ... then I did research on Google got a very good source at http://sqlfool.com/2009/04/a-look-at-missing-indexes/
... then problem solved, it runs FAST now.
THANKS again.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 00:02:21
https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -