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

Eric Herlitz
  • 588
  • 2
  • 9
  • 19
  • 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. – Jason Cumberland Aug 22 '12 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. – Eric Herlitz Aug 22 '12 at 21:22
  • If other answers do not work try http://stackoverflow.com/a/28626223/1290868 which tells to do what is on https://support.microsoft.com/en-us/kb/2605597 This may help. – myuce May 12 '15 at 15:22
  • For Sql Server, the goal should be 100% reserved. – Joel Coel Sep 02 '15 at 15:50

3 Answers3

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
HopelessN00b
  • 53,385
  • 32
  • 133
  • 208
Jeferson Tenorio
  • 368
  • 1
  • 4
  • 12
  • 5
    Dont simply run this command without understand the consequences. http://sqlperformance.com/2013/07/sql-statistics/statistics-updates and http://stackoverflow.com/questions/23440770/what-actually-exec-sp-updatestats-will-do – Rosdi Jun 16 '14 at 07:46
  • 2
    You should know the likely consequence(s) before running this command (in fact every command that you run). – Sohail xIN3N Aug 18 '14 at 09: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? – Ian Grainger May 14 '15 at 08: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 01 '15 at 00: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.

Adam
  • 197
  • 1
  • 3
  • 15
  • As long as you understand the risks. https://stackoverflow.com/questions/1452996/is-the-nolock-sql-server-hint-bad-practice That link is timing out BTW. – Robert Brooker Nov 16 '20 at 07:02
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??

TRex
  • 11
  • 1