none
Sql timeout expired when loading datatable RRS feed

  • Question

  • I have a very perplexing problem I have been unable to resolve.  Whenever any of our applications (console, GUI, and service), they run through an initialization routine.  This initialization routing creates 8 threadpool threads and fires them off for execution.  The main thread then waits for all the threads to finish before proceeding.  This used to be done with WaitAny/WaitAll (depending on whether the main thread was STA or MTA) and ManualResetEvents.  However, due to the issue I am having, I tried switching to use a thread count and use the Interlocked.Decrement to reduce the thread count as each method completes.  When the count reaches zero, the main thread continues (basically, it's doing a while (0 < _threadCount) Thread.Sleep(500) loop.

    So, the issue I am having is that periodically, the very last threadpool thread that I initiate will throw a timeout exception during the DataTable.Load(SqlDataReader) method.  It's always the last one the kicks off, and it may happen once in a day, 5 times in a day, or not all for a day or multiple days in a row.  I think at one point, it went 7 days without failing.  And the kicker is that it only fails when executing in a certain batch console program.  It never fails from the GUI application.  The timeout appears to be caused by a ASYNC_NETWORK_IO wait.  Other console applications that run on the same server and use the exact same code never fail.  And, the program that does fail never failed previous to this past April.

    The stored procedures that are called select data from read only tables, and the greatest number of rows returned is around 3000, so the amount of data wouldn't be an issue.

    I've setup a number of perfmon sessions and the last one revealed the following for the threads in the app:

    Thread Instance Wait State Thread Wait Reason

    0 5 4 (Waiting for Execution Delay to be resolved)

    1 5 6 (Waiting for a user request)

    2 5 6

    3 5 6

    4 5 4

    5 5 16 (Waiting for an LPC Receive notice)

    6 5 4

    7 5 15 (Waiting for an event pair low)

    8 5 6

    9 5 6

    10 5 6

    11 5 6

    12 5 6

    13 5 6

    14 5 6

    15 5 6

    I'm not sure what these mean as I couldn't find detailed explanations, or if any of them matter.  I am just about at my wits end on trying to resolve this.  Any suggestions would be appreciated.

    Thanks

    • Moved by Ji.ZhouModerator Friday, August 6, 2010 2:53 AM (From:Visual C# General)
    Monday, July 26, 2010 9:54 PM

All replies

  • > When the count reaches zero, the main thread continues (basically, it's doing a while (0 < _threadCount) Thread.Sleep(500)

    Loop through each Thread object and call the Join method on each.

    - or -

    Use Thread.VolatileRead to access the _threadCount.

    I would consider the first way preferred.  There is a problem that the compiler, runtime (JIT), or maybe even the CPU can re-order the read of _threadCount before the while loop because it does not realize that another thread might be changing it.

    Also, hopefully you know about this, but it is a common error: make sure the threads are not sharing any SqlConnection objects.  Since SqlConnection is not thread-safe, each thread must have its own instance.

     

    Monday, July 26, 2010 11:42 PM
  • Thanks for the reply.  The main thread has no problem ending the loop, whether it ends exactly at the time it changes to zero or shortly thereafter.  The problem is the timeout that occurs inside one of the threads when trying to load a datatable from a datareader.

    Each thread has its own SqlConnection, they are not shared.

    Tuesday, July 27, 2010 1:51 AM
  • Hi Jonathan,

    Using ManualResetEvent.WaitAll method is better and elegant than using a loop wait, so the problem was not at this point.

    What does the main thread do and what are the worker threads' tasks?

    Timeout exception happened in main thread or worker threads?

    What if you increase the timeout value in the connection string?

    Thanks.


    Figo Fei
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, July 29, 2010 3:36 AM
  • Figo,

    I previously was using WaitAll but changed it as I'm trying to find the cause.  The main thread eventually does some processing but during initialization, it launches these background threadpool threads in order to load information that it needs.  Each thread executes a stored procedure to load data from a series of read-only tables.  Each stored procedure is executes using it's own SqlConnection/SqlCommand, ExecuteReader is the method used to return a DataReader, and then the records returned are loaded into a series of DataTables.  The timeout is a SqlTimeout that occurs when calling the DataTable.Load method on the final Threadpool thread.  The largest dataset returned by any of the procs is around 3000 records.  The timeout is set to 30 seconds and 99.9999% of the time, the proc that is in the failing thread returns in less than 200ms. 

    The exact same code is executed by hundreds of users during the day on machines running Windows XP and it has never failed once for a single user.  However, on one of our batch processors running Windows 2003, a console program executing this code fails randomly.  Sometimes once per day, sometimes multiple times per day, sometimes not for several days.  On the batch processor, we execute this program 4 times per hour, with a different command-line parameter.  Sometimes it's the first execution that fails.  Sometimes it's the last, and sometimes it's one in between.  Re-running the program nearly always works but occasionally, we will get the timeout on re-runs.  But again, only on the batch processor.  We are executing the stored procs on Sql Server 2008.

    Thursday, July 29, 2010 1:26 PM
  • > the proc that is in the failing thread returns in less than 200ms. 

    I've seen posts like this on the forums before, where an occasional timeout occurs and when it does the time elapsed is far below the timeout interval.  Unfortunately, I don't recall any of those posts being resolved.  However, your problem might be different.  Please post the complete exception message and stack trace.

    Thursday, July 29, 2010 11:28 PM
  • Hi Jonathan,

    Is this problem solved now? You may post exception message and stack trace for us for further investigation.

    Thanks.


    Figo Fei
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by leanhtan86 Monday, February 24, 2014 11:30 AM
    • Unproposed as answer by leanhtan86 Monday, February 24, 2014 11:31 AM
    Wednesday, August 4, 2010 5:42 AM
  • It is not solved.  I have to make some coding changes to get the stack trace which will be put into production tomorrow.  Once that is in place, I will post the exception and stack trace.

    Thanks,

    Jonathan

    Wednesday, August 4, 2010 1:31 PM
  • Here is the stack trace and message

    System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

    at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)

    at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)

    at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()

    at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()

    at System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)

    at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)

    at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)

    at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)

    at System.Data.SqlClient.SqlDataReader.ReadColumnData()

    at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)

    at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)

    at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)

    at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)

    at System.Data.ProviderBase.SchemaMapping.LoadDataRow()

    at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)

    at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)

    at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)

    at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)

    at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)

    at System.Data.DataTable.Load(IDataReader reader)

    Thursday, August 5, 2010 7:08 PM
  • Any ideas?  It sure seems to me as though there is some sort of multi-threading issue when multiple threads are executing stored procs and attempting to load datatables from datareaders in each of the threads.  The fact that it happens at random would seem to indicate a timing issue and that the issue occurs during the ReadSni method, which is will down the call stack.
    Monday, August 9, 2010 8:22 PM
  • I've found a few posts where people had this problem and database mirroring was enabled.

    Are you using mirroring?

    Please take a look at the following:

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/cf00c32f-ad62-43c9-8b1c-3dc10e0feba7

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/918e4a7f-1fc5-4679-958f-4c4f07b6ae76

     

    Monday, August 9, 2010 11:58 PM
  • Thanks.  We are not mirroring.  We do have a cluster setup for the database server.
    Tuesday, August 10, 2010 2:27 AM
  • Since this issue has been out here for awhile, and I've seen a number of very similar questions go unresolved on the forums in the past, you might really want to consider contacting Microsoft Product Support Services by phone.  And if you get it resolved, please post back with the resolution.

    Other than that, the last thing I could suggest is installing the latest .NET service pack, if you have not already.  However, I understand that is something that many people will not takely lightly, especially if you are unable to replicate the problem in a test environment to ensure that the service pack actually fixes it.

     

    Tuesday, August 10, 2010 2:40 AM
  • Hi Jonathan,

    As BinaryCoder touched on, your issue requires a more in-depth level of support. 

    Please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Thanks,

    Cathy Miller

    Microsoft Online Community Support

    Tuesday, August 10, 2010 2:20 PM
    Moderator
  • As a follow-up, I wanted to share our recent experience.  A little over a week ago, we move the code from one machine to another.  We have not, to date, experienced a single timeout.  As I've suspected all along, there is something specific to the machine it was running on.  The major difference between the old machine and the one it runs on now is the old machine was running Windows Server 2003 64 bit and the new machine is running Windows Server 2003 32 bit.  Otherwise, the machines are fairly identical.

    The executable has always been compiled with a Platform Target of X86.  However, the underlying assemblies that are referenced are compiled as Any CPU.  Now, the issue could be in the disparity between the referenced assemblies and the host exe having different target platforms, or there could be an issue with the underlying .NET framework running on 64 bit OS.

    I'm not sure how to go forward at this point but it seems likely MS will have to be involved in trying to diagnose the issue.

    Thursday, August 26, 2010 9:21 PM