June 5, 2013 at 12:43 pm
I have a stored procedure that's called by a web application. Every now and then, the web call will time out for no apparent reason. But I can still run the proc smoothly in SSMS. The way I stumbled on to resolve the timeout issue is to drop the proc and recreate it. Nothing has been changed, but the web app will be back to normal. Any thoughts on why this is happening?
June 5, 2013 at 12:53 pm
Michelle-138172 (6/5/2013)
I have a stored procedure that's called by a web application. Every now and then, the web call will time out for no apparent reason. But I can still run the proc smoothly in SSMS. The way I stumbled on to resolve the timeout issue is to drop the proc and recreate it. Nothing has been changed, but the web app will be back to normal. Any thoughts on why this is happening?
when an existing execution plan for a procedure starts timing out, i usually look at statistics first, and parameter sniffing second. dropping and recreating the proc builds anew execution plan, and the issue goes away...for a while.
look at the body of the procedure,and see what table(s) it's selecting/using;
if that /those table(s) gets updated a lot, and also has a lot of rows, i'd look at the stats, and probably add a process that updates the stats that that proc affects more often than the current (weekly?) maintenance is doing.
Lowell
June 5, 2013 at 1:06 pm
Thanks for the advice! The thing is, all the tables involved in the proc are very static. The updates happen once a while and the proc usually works fine after data updated. And they are not very big tables either.
June 5, 2013 at 1:09 pm
Michelle-138172 (6/5/2013)
Thanks for the advice! The thing is, all the tables involved in the proc are very static. The updates happen once a while and the proc usually works fine after data updated. And they are not very big tables either.
hmm, the rebuilding of the proc nicely dovetails with my first guess, but anyway
next, i'd start looking at blocking;maybe an insert or update is locking the table.
after that, i'd start looking at wait times, maybe the server/disk is very busy, and that's causing the timeouts...any chance of doing something resource intensive, like an index rebuild when the timeouts are happening?
Lowell
June 5, 2013 at 1:12 pm
No, none of that is happening. It happened both on my production and dev servers. There's nothing going on the dev when this happened. As soon as I dropped and recreated the proc, it worked again!
June 5, 2013 at 1:17 pm
Have you considered parameter sniffing? What happens if you add WITH RECOMPILE to the proc?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2013 at 1:50 pm
With Recompile didn't do anything.
June 5, 2013 at 1:57 pm
The main thing that puzzels me is why it only happens to web calling. There's never a problem running it in the Management Studio. My web dev guys can't figure out why either.
June 5, 2013 at 2:10 pm
Michelle-138172 (6/5/2013)
The main thing that puzzels me is why it only happens to web calling. There's never a problem running it in the Management Studio. My web dev guys can't figure out why either.
that's most likely because the SET options when called from the web are different than when you call it from SSMS;
different set options = different execution plan, which , for me, still points to stats or parameter sniffing.
find a spid for a connection from teh web, and look in select * from sys.dm_exec_sessions at the options like these, and compare thm to an SSMS connection
text_size
language
date_format
date_first
quoted_identifier
arithabort
ansi_null_dflt_on
ansi_defaults
ansi_warnings
ansi_padding
ansi_nulls
concat_null_yields_null
transaction_isolation_level
lock_timeout
Lowell
June 5, 2013 at 2:14 pm
Thanks! Will give it a try!
Lowell (6/5/2013)
Michelle-138172 (6/5/2013)
The main thing that puzzels me is why it only happens to web calling. There's never a problem running it in the Management Studio. My web dev guys can't figure out why either.that's most likely because the SET options when called from the web are different than when you call it from SSMS;
different set options = different execution plan, which , for me, still points to stats or parameter sniffing.
find a spid for a connection from teh web, and look in select * from sys.dm_exec_sessions at the options like these, and compare thm to an SSMS connection
text_size
language
date_format
date_first
quoted_identifier
arithabort
ansi_null_dflt_on
ansi_defaults
ansi_warnings
ansi_padding
ansi_nulls
concat_null_yields_null
transaction_isolation_level
lock_timeout
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply