2

I have created a winforms application which pulls data from a database. During development, (using a SQL server on the same machine), but once released, there seems to be a significant delay retrieving the data from the remote server. The dev and release servers have almost identical data, so that shouldn't be an issue.

I think that I have narrowed the problem down to one call to a stored procedure.

Using the SQL Profiler on the release server, it shows that it takes almost 17 seconds to run the stored proc, when it is called from my application, but only a tiny fraction of this when run from SSMS:

enter image description here

My question is, what could be causing this behavior? Or what can I do next to try and diagnose the problem further?

Gravitate
  • 2,885
  • 2
  • 21
  • 37
  • 3
    Canonical - [Slow in the Application, Fast in SSMS?](http://www.sommarskog.se/query-plan-mysteries.html) – Alex K. Oct 01 '18 at 11:29
  • This one helped me with the same problem https://stackoverflow.com/questions/3995386/query-runs-fast-but-runs-slow-in-stored-procedure – GuidoG Oct 01 '18 at 11:37
  • @Larnu Yes! Thank you. That did help, BUT the answer which helped was this one: https://stackoverflow.com/a/2736682/1473412 , not the accepted answer. It looks like it is being caused by SMSS automatically setting ARITHABORT ON in the background. – Gravitate Oct 01 '18 at 11:45
  • It's not really caused by `ARITHABORT ON`. That just gives the query a different "fingerprint" which allows it to have a different query plan which just happens to be more efficient. – Nick.Mc Oct 01 '18 at 12:51
  • 1
    @Nick.McDermaid My apologies. My phrasing was poor. I just meant that the difference between the two occurrences of the query being called is that ARITHABORT is being set differently behind the scenes by SSMS. I understand that the difference in execution time is not caused directly by ARITHABORT. – Gravitate Oct 01 '18 at 13:19

0 Answers0