December 30, 2011 at 8:43 am
SQL queries/calls are running terribly slow from .Net Application
but when we run the same query on SSMS its fine.
I ran profiler but didnt get much clue, Tlog is OK as well.
No open transactions. What else basic stuff I should be checking ?
December 30, 2011 at 8:45 am
WangcChiKaBastar (12/30/2011)
SQL queries/calls are running terribly slow from .Net Applicationbut when we run the same query on SSMS its fine.
I ran profiler but didnt get much clue, Tlog is OK as well.
No open transactions. What else basic stuff I should be checking ?
Identify your worst performing query.
Trace it twice... once executed from .Net and once executed on SSMS; compare exec plans as well as trace files looking into wait events.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 30, 2011 at 9:02 am
ok
Its actually an iterative call to a Stored Proc from .Net
and the proc runs super fast on SSMS. every call it fetches 2 to 3 rows and
returns data to front end.
December 30, 2011 at 10:27 am
What worked for me was windows update. Thanks
December 30, 2011 at 10:37 am
WangcChiKaBastar (12/30/2011)
okIts actually an iterative call to a Stored Proc from .Net
and the proc runs super fast on SSMS. every call it fetches 2 to 3 rows and
returns data to front end.
I'm not sure if this is the best way to get the data.... Have you tried query all data at once?
December 31, 2011 at 3:36 am
This is actually a fairly common problem. The default ANSI set options are different for .NET than they are for SSMS. the same query, submitted with different ANSI set options, will have two query execution plans saved for it.
The query execution plan which is stored for the .NET query is apparently less than optimal. That can happen if the stored procedure happened to get called with an unusual parameter the first time.
The quick fix is to use sp_recompile on this stored procedure. That will cause a new query plan to be compiled the next time the stored procedure is called.
Erland Sommarskog published an outstanding article about this last year. http://www.sommarskog.se/query-plan-mysteries.html
It explains this particular problem as well as other situations that may cause a query to execute slowly in the application when it runs quickly from SSMS.
December 31, 2011 at 3:56 am
David has good point but I suspect something else.
When you say SQL run slow, you might be referring to time taken to render the page (on web) or window (on desktop) applications. It’s cumulative time of network latency & application’s own processing time. I believe there are few diagnostic tools (also freeware) that can give you this breakup of time in one task request.
I won’t suspect database unless & until Front End Team prove they are fine on their stand. I found their faults in most of the cases I handled Performance SLAs.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply