Link to home
Start Free TrialLog in
Avatar of Richard Mancuso
Richard MancusoFlag for United States of America

asked on

How to resolve "Server Error in '/' Application"

I have an ASP.Net application with a SQL Server backend.  Occasionally one of my users will get an error "Server Error in '/' Application"
"The Wait Operation Timed Out". I can't figure out what is happening.  The underlying query runs in 1 second in SQL Management Studio.  I don't recall getting this error on my browser.
The Webserver and SQL Server are on the same machine.  Is this error at the machine level or does it have to do with the User's internet connection?
I need to make this error go away!
I've attached a screenshot of the error.
ApplicationError.png
Avatar of Miguel Oz
Miguel Oz
Flag of Australia image

When you say 1 second. Do you use the same query parameters that your user has used prior the error.
This type of timeout can have three causes;
1.Your user may have a very slow connection that it takes more than 30 seconds (typical timeout), you can try adjusting the SQL connection timeout and well as your IIS website timeout.
2. There's a deadlock somewhere
3. The database's statistics and/or query plan cache are incorrect
I will try adjusting timeouts first, for the others I will use activity monitor of your SSMS to check when your particular user is connected to your system.

If more help needed, please provide version details of:
- SQL server.
- .NET framework
- ASP.NET
- Server and IIS
- Grid component version
Also send your code  setting up the data source.
Avatar of Richard Mancuso

ASKER

Hi Miguel.

Thanks for your response.  I really appreciate it.  
The stored procedure in question runs reliably in 1 second in SQL Mgmt Studio.  I set the procedures to always recompile and to for IIS to use the
First the procedure is very efficient and no matter the parameters, it takes 1 second to run in SQL Mgmt Studio.  I set ARITHABORT to ON in the stored procedure to that .NET will be forced to use that option.

How can I adjust the SQL connection and IIS website timeouts?  (I never know the right place to do this.)

How do you know that this error is between the User's computer and the website and note between the website and the database?
How can I find out if there's a deadlock?  My queries select on tables with (NOLOCK).
The website uses a generic SQL User to call on the database.  I can't isolate a person's activity.

SQL Server 13
.Net Framework 4
ASP.Net 4.71
Windows Server 2016, IIS 10
Grid Version - Infragistics 17.1

Maybe the Data Source connection is old or at fault?  It's not clear to me how to tell the version I'm using or if it's up to date.  

<add name="ConnectionString" connectionString="Data Source=00.00.000.00;Database=MyDatabase;User ID=MyUser;Password=MyPassword" providerName="System.Data.SqlClient"

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    SelectCommand="GetWcWeekliesByBranch" SelectCommandType="StoredProcedure"
    CancelSelectOnNullParameter="False" >
    <SelectParameters>

        <asp:ControlParameter ControlID="WeekEnding" Name="WeekEnding" PropertyName="Value" Type="DateTime" />
        <asp:ControlParameter ControlID="Metric" Name="Metric" PropertyName="SelectedValue" Type="String" />
        <asp:ControlParameter ControlID="CustomerId" Name="CustomerId" PropertyName="SelectedValue" Type="Int32" />
        <asp:Parameter Name="DepartmentId" Type="Int32" />
        <asp:ControlParameter ControlID="RecruitedById" Name="RecruitedById" PropertyName="SelectedValue" Type="Int32" />
        <asp:ControlParameter ControlID="PlacedById" Name="PlacedById" PropertyName="SelectedValue" Type="Int32" />

    </SelectParameters>
If all the other users do not have any issues and it is just a particular user then it gives you some idea that it could be the problem.
How many rows are you returning?
Grid could be very slow for thousands of rows. You SQl execution may not be the issue but the rendering.
Gvien the fact you have only one SQl user you should add some log lines on your page in question.with a time stamp so that you can see how long does it take to execute your page.

See:
https://stackify.com/beyond-iis-logs-find-failed-iis-asp-net-requests/
https://docs.microsoft.com/en-us/dotnet/api/system.diagnostics.eventlog.writeentry?view=netframework-4.8

My other suggestion is always use code to setup components not markup, you have more control and it  could execute faster than  your third party code. Furthermore if your query is fast then you do not need to do any timeout.
HI Miguel,

I'm returning under 100 rows.  This error is usually happening on pages with heavy calculations.  As I mentioned earlier,I was thinking that maybe .Net was messing with the query plan so I set arithabort to ON.  

I will investigate logging.

I don't follow your last suggestion.  Are you saying to get rid of the Infragistics WebDataGrid or the SQLDataSource control?
Basically you are loading the grid using markup , like show here:

https://www.c-sharpcorner.com/article/data-binding-and-crud-operations-in-asp-net-webdatagrid/

but you can use for example a dataset to load your info: (More work of course but the SQL connection is not dependent of the grid component)
https://help.infragistics.com/Help/Doc/ASPNET/2011.1/CLR4.0/html/WebGrid_Updating_Data_with_a_Data_Set.html

I do not think it is .NET messing up is your grid control taking so long to fetch and prodcuing the grid, we can confirm that once you have log results.
Check your error logs.
What web app do you have / replaces index.aspx etc.

You can setup an error handler to notify you of an event that would provide you with details about the request and where the issue occurred.

From the error little can be said.
Thanks for all of your comments.  I looked at the logs and the error messages.  The problem here is that the Stored Procedure was timing out.,  This could be due to parameter sniffing.  There are 2 remedies.  I can improved performance of the stored procedure through Indexes or a Rewrite.  I can extend the Command Timeout property of the SQL Data Source.  

This snippet would remove any timeout.
protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
        {
            e.Command.CommandTimeout = 0;
        }
ASKER CERTIFIED SOLUTION
Avatar of Richard Mancuso
Richard Mancuso
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial