April 13, 2011 at 2:54 pm
I would appreciate some assistance / guidance in troubleshooting an (ASP.NET) application time-out issue with SQL server 2008.
This is now happening about once per week with my application, and I am very much a newbie when it comes to troubleshooting this type of issue.
At a high-level, the web-based application is pulling data from SQL via a stored procedure. When the issue occurs, the end-user experiences about a 15-20 second delay, then a basic error message. When I pull the error log from the application, this is the full error message I am getting:
DotNetNuke.Services.Exceptions.PageLoadException: Exception has been thrown by the target of an invocation. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at AdMakerToolTableAdapters.dnn_AMT__AdItemsListMasterTableAdapter.AMT__AdItemsListMaster(Nullable`1 UserID, Nullable`1 AdvertiserID, String Status, Nullable`1 AdTargetID, Nullable`1 PostingThreshold) --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) at System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) at System.Web.UI.WebControls.DataBoundControl.PerformSelect() at Telerik.Web.UI.GridTableView.PerformSelect() at Telerik.Web.UI.GridTableView.DataBind() at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() at System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) at Telerik.Web.UI.GridTableView.OnPreRender(EventArgs e) at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) --- End of inner exception stack trace ---
-----
The query (stored procedure) that is timing out is in the error message (AMT__AdItemsListMaster)
When this timeout occurs, any subsequent calls to the same query (for that specific userid parameter) - causes the same timeout error. However, I am able to run the same query with another userid parameter, and it doesn't appear time out.
When this happened the last couple times, I first turned on SQL server profiler - to capture data for this 'hung condition' - and have saved that data off - but not sure how to dissect that either.
I'm not sure what could be causing this - a deadlock?
First of all, when this occurs,- is there a way to possibly see if there is a hung query/thread/locks that can be terminated? If so, how would I go about finding that hung thread/query/locks?
After much frustration, the only way I have been able to resolve this issue is to restart both the IIS Web services and SQL server services - and then everything is fine. This is obviously not something I want to do (restarting those services during normal business hours) - especially if it is only affecting one user. I would have hoped if this query timed out, that would be it - and SQL would 'heal itself' the next time that query is called - but apparently that isn't happening...
Any assistance in this matter is greatly appreciated! Thank you!
Kevin
April 14, 2011 at 7:38 am
What is the timeout on the command object? How long does this query/proc take to run in SSMS? Basically what is happening is that the execution takes longer than the timeout on the sql command. This could be a sign of table locking but since you said it is 15-20 seconds my guess is that the proc just takes longer than your timeout length (which sounds like it is pretty short).
_______________________________________________________________
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/
April 14, 2011 at 8:02 am
Sean Lange (4/14/2011)
What is the timeout on the command object? How long does this query/proc take to run in SSMS? Basically what is happening is that the execution takes longer than the timeout on the sql command. This could be a sign of table locking but since you said it is 15-20 seconds my guess is that the proc just takes longer than your timeout length (which sounds like it is pretty short).
Thanks for the feedback...
I am using .NET table adapter for the command object, and I understand the default timeout to be 30 seconds.
Running the query in SSMS has an elapsed time of 00:00:00.433
I've only seen this error happen now about once for the last 3 weeks.
Since the data I am extracting from the query is 'non-critical' - I just added to the sproc the following
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
(I really don't want the table to be locked while reading this data - as far as I'm concerned, for this application locking is not necessary for this query)
If the table was being locked and causing this timeout, what would be the best way to troubleshoot that situation?
April 14, 2011 at 8:09 am
is your DNN instance local, on your network, or on some shared hosting server like ihostasp? I know my shared hsoting sometimes bogs down, but it's hard to diagnose a remote server you only have partial access to.
Lowell
April 14, 2011 at 8:20 am
Lowell (4/14/2011)
is your DNN instance local, on your network, or on some shared hosting server like ihostasp? I know my shared hsoting sometimes bogs down, but it's hard to diagnose a remote server you only have partial access to.
I am running the DNN instance on my own (virtual) server (4 vCPUs, 5GB RAM)
I have complete access/control to the environment. I am running both IIS and SQL on the same server - for the most part, the performance has been good (with the exception of this type of issue once a week).
April 27, 2011 at 11:13 am
Kevin, running quickly for one ID and not another usually ends up as parameter sniffing.
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.
The timeout is a connection side setting, so next troubleshooting step would be to run the proc with the two user IDs in SSMS and get timings on it. Preferably capture the actual execution plan while you run it, so we can take a look at what's going on under the hood.
The reason the restart is most likely fixing the issue is because it's recompiling the proc prior to parameter sniffing. A bit of googling should show you some common methods to deal with that problem. The easiest fix is to take your parameters, feed them to local variables, and then use the local variables from there on in. It's not always going to be optimal that way, but it will give you consistency.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 27, 2011 at 11:15 am
Heh, whoops, sorry, I didn't address one of your issues. Most likely the reason going to dirty reads got you out of this problem is that certain ID's probably need to scan the table, thus ending up in a blocked condition by other processes as it waits for access.
A deadlock would have stopped the process cold and issued a victim notification. This just sounds like a lock driven blocking chain.
EDIT: Grrr, I meant to edit, not double-post, sorry about that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply