I ran into a situation where the sp takes several minutes to run in SSMS and fraction of a second in Application. The Application calls this SP using Entity Framework.
After seeing the profile only difference between the EF and SSMS is the variable
set arithabort this is off in EF and on in SSMS
in SSMS then I ran
set arithabort off
and then ran my sp and it went faster.
To continue debugging the real issue I created a new sp as a back up of my sp and it ran faster even with set arithabort on!!
Puzzled I just ran alter in my sp and did no changes and just saved it.
Now even my sp runs faster!!
Same issue happened in another environment in which SSMS is faster and applicaiton is slower.
Once again did an alter save and now the sp runs faster in both modes
After seeing the profile only difference between the EF and SSMS is the variable
set arithabort this is off in EF and on in SSMS
in SSMS then I ran
set arithabort off
and then ran my sp and it went faster.
To continue debugging the real issue I created a new sp as a back up of my sp and it ran faster even with set arithabort on!!
Puzzled I just ran alter in my sp and did no changes and just saved it.
Now even my sp runs faster!!
Same issue happened in another environment in which SSMS is faster and applicaiton is slower.
Once again did an alter save and now the sp runs faster in both modes