none
Time Outs

    Question

  • Hi Guys

    Wondering if I can spitball some ideas with you guys. We have a SQL server that is experiencing time outs from an application front end and also a network packet dropout. Below is the error message we are getting from our web front end error logging.

    The SQL server is a Virtual Machine running on the same host and same networking as other virtual machines that aren't having dropouts so I can safely assume, not a physical or virtual switch related issue. The server is on the same datastore as the web front end server, so once again I can assume that there are no issues with the physical host. This is the only computer that does this it is a SQL 2016 instance, it is running on a 2012r2 windows standard server.

    System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at KonektivaLibrary.ASPNetPortal.WorkBenchDB.Case_Updated(Int32 CaseId, Boolean Updated_tblActivity, Boolean Updated_tblActivityNote, Boolean Updated_tblCase, Boolean Updated_tblCase_WC, Boolean Updated_tblCase_WorkerEmployment, Boolean Updated_tblEntityCaseRole, Boolean Updated_tblInjury, Boolean Updated_tblLog_WorkStatus, Boolean Updated_tblPerson)
       at Konektiva.CaseEFAR_Edit.AddNewFactor_Click(Object sender, EventArgs e) in C:\Users\aminareci\Documents\Visual Studio 2012\Projects\KonektIT\Konektiva Case\Development\10.2.6\Source\Modules\Case\CaseEFAR_Edit.aspx.vb:line 215
       at System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
    ClientConnectionId:64359b28-cc67-4a3a-a91d-cb9a7b09fc21
       at System.Web.UI.Page.HandleError(Exception e)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
       at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
       at System.Web.UI.Page.ProcessRequest()
       at System.Web.UI.Page.ProcessRequest(HttpContext context)
       at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
       at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

    If you could advise some suggestions. Also I have cleared all offloading from the network card as I have seen that may cause an issue.

    Thank You

    Monday, February 27, 2017 11:11 PM

All replies

  • The most common reason for timeouts is blocking. Have you investigated blocking?

    You should be able to use the diagnostic stored procedure for this.

    https://www.mssqltips.com/sqlservertip/3198/monitoring-sql-server-2012--capture-and-parse-spserverdiagnostics-output/

    -- Blocked Process Reports select 'Blocked Process Report' as "Blocked Process Report", tbl.evt.query('.') as "Report XML" from #ServerStats CROSS APPLY data.nodes('/queryProcessing/blockingTasks/blocked-process-report') AS tbl(evt) where component_name like 'query_processing'

    Do you know the exact statement which is timing out?

    It looks like it can be found here:

    C:\Users\aminareci\Documents\Visual Studio 2012\Projects\KonektIT\Konektiva Case\Development\10.2.6\Source\Modules\Case\CaseEFAR_Edit.aspx.vb:line 215

    Try running it in the New Query window and see how it responds. Does it take less than 20 s?

    If you look at the execution plan - are there any indexes suggested?

    Tuesday, February 28, 2017 1:10 AM
  • In addition, the client throws time out error may also due to  CPU at 100%

    ---This first thing to check if CPU is at 100% is to look for parallel queries:


    -- Tasks running in parallel (filtering out MARS requests below):
    select * from sys.dm_os_tasks as t
     where t.session_id in (
       select t1.session_id
        from sys.dm_os_tasks as t1
       group by t1.session_id
      having count(*) > 1
      and min(t1.request_id) = max(t1.request_id));

    -- Requests running in parallel:
     select *
       from sys.dm_exec_requests as r
       join (
               select t1.session_id, min(t1.request_id)
              from sys.dm_os_tasks as t1
             group by t1.session_id
            having count(*) > 1
               and min(t1.request_id) = max(t1.request_id)
          ) as t(session_id, request_id)
         on r.session_id = t.session_id
        and r.request_id = t.request_id;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 28, 2017 6:53 AM