Multiple Simultaneous SQL Connection Timeouts In Multithreaded Windows Service

Question

I have a multithreaded Windows Service I've developed with VS 2010 (.NET 4.0) which can have anywhere from a few to a few dozen threads, each retrieving data from a slow server over the Internet and then using a local database to record this data (so the process is Internet-bound, not LAN or CPU bound).

With some regularity, I am getting a flood/flurry/burst of the following error from several threads simultaneously:

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

The call stack for this error is typically:

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

I'm not specifying a Connection Timeout in the connection string, and there are other applications and processes working in this database. Has anyone come across this kind of behavior and if so what was done to prevent it?

The most commonly-called method in my data access layer looks like this, and all my other DAL methods follow the same approach:

using (SqlConnection con = new SqlConnection(GetConnectionString()))
using (SqlCommand cmd = new SqlCommand("AddGdsMonitorLogEntry", con))
{
    cmd.CommandType = CommandType.StoredProcedure;

    /* setting cmd.Parameters [snipped] */

    // We have been getting some timeouts writing to the log; wait a little longer than the default.
    cmd.CommandTimeout *= 4;

    con.Open();

    cmd.ExecuteNonQuery();
}

Thanks very much!

EDIT

Given comments about this occurring in mirrored environments, I should indeed mention that the database in question is mirrored. It's marked in SSMS as "Principal, Synchronized", in "High safety without automatic failover (synchronous)" mode.

EDIT 5/26/11

I am seeing nothing in the SQL Server logs to indicate any problems. (I don't have access to the Windows Event Viewer on that server, but I've asked for someone to look for me.)

Solution 1

According to the MSDN Blog post just created today (hooray for Google!):

Microsoft has confirmed that this is a problem in the current release of ADO.NET. This issue will be fixed in ADO.NET version, ships with Visual Studio 2011.

In the meantime, we request to use the following workarounds:

1. Increase the connection string timeout to 150 sec. This will give the first attempt enough time to connect( 150* .08=12 sec)

2. Add MinPool Size=20 in the connection string. This will always maintain a minimum of 20 connections in the pool and there will be less chances of creating new connection, thus reducing the chance of this error.

3. Improve the network performance. Update your NIC drivers to the latest firmware version. We have seen network latency when your NIC card is not compatible with certain Scalable Networking Pack settings. If you are on Windows Vista SP1 or above you may also consider disabling Receive Window Auto-Tuning. If you have NIC teaming enabled, disabling it would be a good option.

The post itself is an interesting read, talking about a TCP/IP connection retry algorithm. And kudos to all the folks who said "hey this looks like it's related to mirroring..."! And note the comment about this being "because of slow response from SQL Server or due to network delays".

UGH!!!

Thanks to everyone who posted. Now we must all ask for a patch to the .NET Framework (or some other ADO.NET patching mechanism), so we don't have to wait for (and buy) Visual Studio 11...

Solution 2

Connection timeout is a different thing than command timeout. Command timeout applies to situation when you have connection established, but due to some internal reasons server cannot return any results within required time. Default command timeout is 30 seconds. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

Try to specify connection timeout in the connection string. Default value is 15 seconds what may be the reason of the issue you see. You can also specify connection timeout in code: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx

Solution 3

I get this every once in a while on this old database server that we have (coming up on 10 years old now). When it does happen though it's because something is hammering that thing with connections/queries constantly. My guess is that you'll find that when it happens the database server is under load (or a high number of connections or something along those lines) Anyway, in my experience if you can optimize the code, optimize the database, getting a beefier database server, etc. all helps. Another thing you can do, which Piotr suggests, is simply up the timeout for the connection. I'd still go through and optimize some stuff though (should help in the long run).

Comments

[ALEXintlsos]

I am thinking about increasing the connection timeout, but this error is only occurring on connections, not on stored procedure execution. Command timeout won't have anything to do with this. But my concern is more about why this is only happening intermittently, and why it affects so many attempted connections at the same time. I've got a DBA/developer person here who says it sounds like there may be something going on with the server side connection pool (and I have to admit I didn't know there was such a thing as a server side pool). But that's not been verified.

[Piotr Rodak]

If there is something wrong with the server connection pool, there would be some indications of it in the SQL Server error log. Can you verify this? I would think that if the network is slower at times, connections from all threads would suffer because of this. After all every connection goes through the same wires. We had similar issue recently - intermittent timeouts experienced by our application server. It turned out that the routing between servers was not correct and the network performance fluctuated a lot. Then our application would throw timeout exceptions from all threads.

[ALEXintlsos]

I'm afraid there's nothing in the SQL Server logs. Any recommendations on how to detect network performance fluctuations?