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 |
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. |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|