I'm running an ASP.NET Web Pages page that upon initial load pulls a list of items from a SQL server. This query runs in a second or so and loads the page within 2 seconds. The return is about a 1000 records, give or take. I'm pulling Hostnames from a Service Manager SQL database along with some other information.
Within this page, I have a search built in that essentially runs the exact same query but runs it with a LIKE based on hostname. This loads the same page with all hostnames that are contain part of the search query. The query generally runs within SQL Management Studio in under a second, but loading the page takes substantially longer and sometimes it times out.
My question is, why does the parameter based search takes so much longer and sometimes timeout for no apparent reason. Are there any steps that can be taken to mitigate this timeout? Below is the full error.
Server Error in '/' Application.
The wait operation timed out
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
System.ComponentModel.Win32Exception: The wait operation timed out Source Error: Line 13: } Line 14: Line 15: var selectedData = db.Query(selectCommand, searchTerm); Line 16: Line 17: Source File: c:\Users\u0149920\Documents\My Web Sites\AppSupport\servers\default.cshtml Line: 15
[Win32Exception (0x80004005): The wait operation timed out] [SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1753346 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5295154 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +242 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1682 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +59 System.Data.SqlClient.SqlDataReader.get_MetaData() +90 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +365 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +1325 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +175 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41 System.Data.Common.DbCommand.ExecuteReader() +12 WebMatrix.Data.<QueryInternal>d__0.MoveNext() +152 System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +381 System.Linq.Enumerable.ToList(IEnumerable`1 source) +58 WebMatrix.Data.Database.Query(String commandText, Object parameters) +103 ASP._Page_servers_default_cshtml.Execute() in c:\Users\u0149920\Documents\My Web Sites\AppSupport\servers\default.cshtml:15 System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +197 System.Web.WebPages.WebPage.ExecutePageHierarchy(IEnumerable`1 executors) +69 System.Web.WebPages.WebPage.ExecutePageHierarchy() +151 System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage) +76 System.Web.WebPages.WebPageHttpHandler.ProcessRequestInternal(HttpContextBase httpContext) +114
Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.17929
The problem you are having is the query command is taking too long. I believe that the default timeout for a query to execute is 15 seconds. You need to set the CommandTimeout (in seconds) so that it is long enough for the command to complete its execution. The "CommandTimeout" is different than the "Connection Timeout" in your connection string and must be set for each command.
In your sql Selecting Event, use the command:
e.Command.CommandTimeout = 60
Protected Sub SqlDataSource1_Selecting(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) e.Command.CommandTimeout = 60 End Sub
To all those who know more than me, rather than marking it unhelpful or misleading, read it one more time. I had issues with my Virtual Machine (VM) becoming unresponsive due to all resources being consumed by locked threads, so killing threads is the only option I had. I am not recommending this to anyone who are running long queries but may help to those who are stuck with unresponsive VM or something. Its up-to individuals to take the call. Yes it will kill your query but it saved my VM machine being destroyed.
Serverstack already answered similar question. It solved my issue with SQL on VM machine. Please check here
You need to run following command to fix issues with indexes.
If you're using Entity Framework, you can extend the default timeout (to give a long-running query more time to complete) by doing:
myDbContext.Database.CommandTimeout = 300;
myDbContext is your DbContext instance, and
300 is the timeout value in seconds.
(Syntax current as of Entity Framework 6.)
I had the same issue. Running
exec sp_updatestats did work sometimes, but not always. I decided to use the
NOLOCK statement in my queries to speed up the queries.
NOLOCK after your FROM clause, e.g.:
SELECT clicks.entryURL, clicks.entryTime, sessions.userID FROM sessions, clicks WITH (NOLOCK) WHERE sessions.sessionID = clicks.sessionID AND clicks.entryTime > DATEADD(day, -1, GETDATE())
Read the full article here.
Look into re-indexing tables in your database.
You can first find out the fragmentation level - and if it's above 10% or so you could benefit from re-indexing. If it's very high it's likely this is creating a significant performance bottle neck.
This should be done regularly.
We encountered this error after an upgrade from 2008 to 2014 SQL Server where our some of our previous connection strings for local development had a Data Source=./ like this
<add name="MyLocalDatabase" connectionString="Data Source=./;Initial Catalog=SomeCatalog;Integrated Security=SSPI;Application Name=MyApplication;"/>
Changing that from ./ to either (local) or localhost fixed the problem.
<add name="MyLocalDatabase" connectionString="Data Source=(local);Initial Catalog=SomeCatalog;Integrated Security=SSPI;Application Name=MyApplication;"/>
In our case we were able to narrow the cause down to a number of views which had
WITH SCHEMABINDING on them. Although this is supposed to improve performance it was resulting in an awful query plan (doing a single record update on a table which was being referenced by these views was taking nearly 2 seconds of elapsed time). Removing
WITH SCHEMABINDING has meant all is running smoothly again and the "wait operation timed out" errors have gone.
User contributions licensed under CC BY-SA 3.0