68

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.

Exception Details:

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

Stack Trace: 

[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

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.17929

Reporter
  • 3,897
  • 5
  • 33
  • 47
Bolson32
  • 793
  • 1
  • 5
  • 7

11 Answers11

45

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

for example:

Protected Sub SqlDataSource1_Selecting(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
    e.Command.CommandTimeout = 60
End Sub
Jeff
  • 1,362
  • 14
  • 17
  • 2
    I just solved this problem on a web app where the query was indeed taking too long - because nobody had created an index on the DB for the field that was being queried! This should have been the first port of call :) – James McCormack Mar 08 '17 at 11:38
28

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.

exec sp_updatestats
Community
  • 1
  • 1
Sanjay Zalke
  • 1,331
  • 1
  • 18
  • 25
  • 20
    This isn't a generally applicable answer, i.e. queries can run longer than any given timeout regardless of whether index statistics have been updated recently. – Kenny Evitt Jun 24 '14 at 12:59
  • 1
    Be sure to read and understand the consequences of exec sp_updatestats before running. – brianc Mar 17 '15 at 15:17
  • Yes I am facing it in the VM. – Jeeva J Oct 10 '18 at 07:51
  • This isn't a bad suggestion but is overkill and requires context. If the CommandTimeout is being hit due to a slow query, this might fix it because it helps sql server find a better execution plan. However, this command updates ALL stats but the problem is likely that ONE table received enough updates to skew a plan (ie the plan assumes 1 row with a matching value but the reality is 100). You're best off finding the slow query with profiler, getting it's execution plan, and looking for a large difference between actual and estimated row counts to find the ONE table that needs stat updates. – b_levitt Nov 20 '19 at 20:08
  • @b_levitt, please understand the scenario, I was trying to save my VM at that time. VM was unresponsive so saving the work done was way important that thinking about the query performance. Once I had recovered the VM I tunned the DB with all that I could do. – Sanjay Zalke Nov 21 '19 at 07:58
  • yup, totally get it and i didn't downvote. I'm just saying when you use a sledgehammer in a pinch, you just need to point out that it IS a sledgehammer :). – b_levitt Nov 21 '19 at 14:41
21

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;

Where myDbContext is your DbContext instance, and 300 is the timeout value in seconds.

(Syntax current as of Entity Framework 6.)

Jon Schneider
  • 25,758
  • 23
  • 142
  • 170
  • Thanks, it helped me. Does it mean my queries are badly structured, too heavy or something? I checked resulting sql transactions in SQL Server Profiler and found nothing strange. – Sam Alekseev Mar 15 '19 at 09:06
7

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. Just add 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.

Adam
  • 6,041
  • 36
  • 120
  • 208
  • NOLOCK is a great solution when your server is doing a lot of multi-user work or a lot of long-running report stuff! – Kristen Waite Feb 02 '16 at 12:07
  • 6
    NOLOCK is usually a bad solution to most problems. (http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/): 1) "Dirty read" 2) Missing rows 3) Reading rows twice 4) Reading multiple versions of the same row 5) Index corruption 6) Read error and more. Just google 'NOLOCK bad idea'... (P.S. Snapshot Isolation could be your friend in some of the cases) – HansLindgren Mar 08 '16 at 09:02
  • 3
    I disagree that using NOLOCK is "usually a bad solution to most problems." It really depends on the issue at hand and that you understand what NOLOCK is doing. There are some scenarios where the overall perfection of the data isn't very important. There are also scenarios where it is - in which case, you should minimize query time/time spent with locked tables. – Panh Jun 27 '16 at 13:20
  • Not use NOLOCK ? – Kiquenet Dec 30 '20 at 10:05
5

I tried the other answers here as well as a few others. I even stopped and restarted the SQL services. Nothing worked.

However, restarting my computer did work.

MiniRagnarok
  • 959
  • 11
  • 23
  • 1
    This works for a time, but the issue comes back, and requires another restart, is there an actual permanent fix? – BlackICE Feb 23 '16 at 15:28
4

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.

http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/

This should be done regularly.

niico
  • 11,206
  • 23
  • 78
  • 161
2

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;"/>
Brett Bim
  • 3,190
  • 4
  • 28
  • 26
  • I had the same problem after upgrading from SQL Server 2012 to 2014. The SQL Server and Web server were on different machines but changing the Data Source from `SERVER` to `SERVER.domain.local` _seems_ to have fixed it. – Darren Mar 11 '16 at 10:39
2

I had the same issue, and by Running "exec sp_updatestats" the issue solved and works now

user1183021
  • 91
  • 3
1

My Table didn't have primary key then I had time out error. after set key sloved.

leyla azari
  • 913
  • 11
  • 20
0

EfCore version of @JonSchneider's answer

myDbContext.Database.SetCommandTimeout(999);

Where myDbContext is your DbContext instance, and 999 is the timeout value in seconds.

(Syntax current as of Entity Framework Core 3.1)

Dale K
  • 25,246
  • 15
  • 42
  • 71
Hamza Khanzada
  • 1,439
  • 1
  • 22
  • 39
0

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.

d219
  • 2,707
  • 5
  • 31
  • 36