0

I have an asmx web service that has a series of functions that call stored procedures from a database. One of those functions is randomly timing out and I can't figure out why.

What's odd is that it usually times out on the first or second try for the day or after rebuilding the app. But if you run it again right away, it returns sub-second. It also never happens with any other function, just this one, which is strange considering how similar it is to the others. I can't seem to reproduce the issue from SQL Management Studio (although it's difficult to reproduce in general) so I'm inclined to think it's a deeper issue than the stored procedure simply taking its time. It's intermittent but will generally happen to me 2 or 3 times per day.

Any idea what could be causing the time out?

TheIronCheek
  • 1,077
  • 2
  • 20
  • 50
  • probably you need to optimize the stored procedure. the 2nd/3rd time it is faster probably because SQL cached the results. – urlreader Feb 21 '17 at 20:24
  • @urlreader - the stored procedure takes a few parameters and only returns a couple rows. If it were a caching issue, shouldn't it slow down every time I pass in different parameters? Also, I find it odd that returning half a dozen rows would take that long without caching assistance. – TheIronCheek Feb 21 '17 at 20:34
  • @Andrew Morton - Yes. I call .Close() after its only use in the code. – TheIronCheek Feb 21 '17 at 20:39
  • @TheIronCheek Close is not the same as Dispose. – Andrew Morton Feb 21 '17 at 21:03
  • And calling Dispose isn't enough, unless you're doing that inside a `finally` block. – mason Feb 21 '17 at 21:52
  • @AndrewMorton - I can adjust the code to look more like http://stackoverflow.com/questions/6021779/how-do-you-completely-close-an-sqlconnection-string-in-vb-net – TheIronCheek Feb 21 '17 at 22:02
  • @AndrewMorton - Would not disposing of the SQL connection properly cause my issue? Where long periods of inactivity cause it to time out? – TheIronCheek Feb 21 '17 at 22:03
  • @TheIronCheek You should follow the pattern of the accepted answer in the question you linked to with regard to the `Using` constructs. `Using` will ensure that `.Dispose()` is called whatever else happens, as mason alluded to. – Andrew Morton Feb 21 '17 at 22:18
  • @TheIronCheek Also, you may want to disable AutoClose of the database: [Worst Practice: Allowing AutoClose on SQL Server Databases](http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases). – Andrew Morton Feb 21 '17 at 22:19
  • @AndrewMorton - This didn't solve the problem. It timed out again this morning. Not on the first try like it has before but after rebuilding the app instead. – TheIronCheek Feb 22 '17 at 16:27
  • It also doesn't explain why the timeout only occurs with the one function instead of all the functions that dispose of the SQL connection the same way. – TheIronCheek Feb 22 '17 at 19:28
  • @TheIronCheek I was assuming that it is the call to SQL Server timing out - is it the call to the web service? If it is the latter, then setting the application pool recycling to a fixed time, like 03:00, instead of every few hours, might help. Also: [Warming up an IIS Application Pool automatically?](http://serverfault.com/q/373548) – Andrew Morton Feb 23 '17 at 18:14
  • @AndrewMorton - I'm trying to figure out the answer to that now. Since the problem is so intermittent and unpredictable, I'm having a hard time isolating where the issue resides. I don't know if it's the calling application, the web service, or the stored procedure that's hanging. – TheIronCheek Feb 23 '17 at 19:13

0 Answers0