25

I'm running SQL Server (2012) on a Hyper-V instance. It has plenty of resources and 25% reserved of the total resources, the VHD is placed on a very fast SSD drive for quick response times.

Every now and then when the applications that use the SQL Server haven't been accessed for a while they get the error "The wait operation timed out". When reloading or retrying to access the database it seems to have been "waken up" and is as fast as ever.

Is there any way to ensure that this soft sleep mode doesn't occur on this kind of environment?

Added

Exception Details: System.ComponentModel.Win32Exception: The wait operation timed out

4
  • 1
    One possibility to check is on the database options, make sure that Auto Close is set to False. You would be able to see the closing and opening events in the SQL log if this was occurring. Aug 22, 2012 at 16:58
  • Changing AutoClose didn't work, it's probably not the databases that slows down. The issue is most likely related to the Hyper-V & SQL Server Combination. Aug 22, 2012 at 21:22
  • If other answers do not work try stackoverflow.com/a/28626223/1290868 which tells to do what is on support.microsoft.com/en-us/kb/2605597 This may help.
    – myuce
    May 12, 2015 at 15:22
  • For Sql Server, the goal should be 100% reserved.
    – Joel Coel
    Sep 2, 2015 at 15:50

3 Answers 3

23

Try to execute this command:

exec sp_updatestats

It, incredibly, resolved the problem.

The code above its the error before the command has been executed.

[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) +1742110
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5279619
   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) +1434
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +61
   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) +1355
   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.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +316
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +86
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1482
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +138
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +30
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +79
   System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) +22
   System.Web.UI.Control.PreRenderRecursiveInternal() +83
   System.Web.UI.Control.PreRenderRecursiveInternal() +155
   System.Web.UI.Control.PreRenderRecursiveInternal() +155
   System.Web.UI.Control.PreRenderRecursiveInternal() +155
   System.Web.UI.Control.PreRenderRecursiveInternal() +155
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +974
4
  • 5
    Dont simply run this command without understand the consequences. sqlperformance.com/2013/07/sql-statistics/statistics-updates and stackoverflow.com/questions/23440770/…
    – Rosdi
    Jun 16, 2014 at 7:46
  • 2
    You should know the likely consequence(s) before running this command (in fact every command that you run). Aug 18, 2014 at 9:34
  • 4
    I'm genuinely interested as to your concern about the consequences of this? The post linked says "could actually do more damage that good, and is the least recommendable option." - but it seems the only problem is that it might do things your maintenance plans already do, or be inefficient - if the alternative is a SQL server instance that's completely broken - I'm not sure why you'd mind that it can be slow or redundant? May 14, 2015 at 8:19
  • 2
    I'm wondering the same thing as @IanGrainger...anyone care to elaborate why executing exec sp_updatestats is a bad idea?
    – Adam
    Sep 1, 2015 at 0:49
2

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.

1
1

I had the exact same problem and I found that it was caused by not enough memory allocation on the Hyper-V VM. I had memory set to dynamic but it was not scaling up as required - switching to a fixed amount of memory, in my case 32GB, solved the problem. The interaction between SqlBulkCopy and Sql Server does not seem able to get more memory when required??

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .