After spending few hours (of sadness) I have come to know that at times I need to set ArithAbort ON in SP so that it can run fast.
Problem was, I wrote a SP that ran fast in the SSMS/query analyzer. However, when the same sp was called from the application it was way to slow. But if I set ArithAbort ON (in the sp) it is quick. what is the meaning of this??
Can anyone tell me "low level" details why that happens?
I have hundrands of Sps but this problem happened to me for the first time. almost every sp uses indexes (so this is a rare problem in sql server). looks like I need to explicitly set "arithabort on" in the sp.