We help IT Professionals succeed at work.

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
Comment
Watch Question

Miguel OzSenior Software Engineer
Top Expert 2009

Commented:
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.
Richard MancusoIndependent Software Developer

Author

Commented:
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>
Miguel OzSenior Software Engineer
Top Expert 2009

Commented:
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.
Richard MancusoIndependent Software Developer

Author

Commented:
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?
Miguel OzSenior Software Engineer
Top Expert 2009

Commented:
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.
Distinguished Expert 2019

Commented:
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.
Richard MancusoIndependent Software Developer

Author

Commented:
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;
        }
Independent Software Developer
Commented:
see last comment