CODEWITHSUNDEEP
c#asp.netsql-server-2012ado.netconnection-pooling
Venkataraman R
Venkataraman R

Reputation: 12969

Timeout error in ADO.NET with connection pooling

We are having legacy SOAP ASP.NET webservice, which connects to SQL Server database to retrieve resultset. Below is our connection string:

connectionString="server=ServerName;database=UserDBName;user id=UserNameRef;Password=myPassword;

We have command timeout set as 5 minutes. What we see is, we are getting frequent timeout issue errors happening for our SQL Server 2012 database with connection pooling. There are no major activities going on in the server or blocking issues happening.

PFB snapshot from sp_whoisactive tracing. If we see, the session 52 is getting time out after 5 minutes. We are not running any command in this session. Don't know, why it suddenly getting timing out.

How to fix these timeout errors ?


+---------+------------+-----------+----------+---------------------------+-------------+---------------------+-------------+------------+------------+-----------------+
| status  | session_id | wait_info | sql_text |        sql_command        | login_name  | blocking_session_id |  host_name  | start_time | login_time | collection_time |
+---------+------------+-----------+----------+---------------------------+-------------+---------------------+-------------+------------+------------+-----------------+
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:00 AM | 2:39:00 AM | 2:39:00 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:06 AM | 2:39:00 AM | 2:39:16 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:39:31 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:39:46 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:40:01 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:40:16 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:40:31 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:40:46 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:41:01 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:41:16 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:41:31 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:41:46 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:42:01 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:42:16 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:42:31 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:42:46 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:43:01 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:43:16 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:43:30 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:43:46 AM      |
| dormant |         52 | NULL      |          | sys.sp_reset_connection;1 | UserNameRef | NULL                | HostNameRef | 2:39:21 AM | 2:39:00 AM | 2:44:01 AM      | <== Post this timeout occurs
+---------+------------+-----------+----------+---------------------------+-------------+---------------------+-------------+------------+------------+-----------------+

Upvotes: 0

Views: 1456

Answers (3)

Venkataraman R
Venkataraman R

Reputation: 12969

The trace in the question was not related to timeout issues. Sorry for the confusion caused. The timeout issue was happening due to blocking issue, where this ASP.NET webservice was waiting for another long running SQL Agent job to complete.

As command time out was set as 5 minutes, the SQL agent job was writing to the background table and was blocking this ASP.NET webservice call. After being blocked for 5 minutes, the ASP.NET webservice times out, due to command time out of 5 minutes.

The fix applied:
We fixed this timeout issue, after analyzing the execution plan of long running bottleneck background job, by changing the existing non-clustered index to clustered index on two background tables, which resulted in faster data retrieval and less blocking happening. Now, the timeout issue is gone.

Upvotes: 0

Lucky Brain
Lucky Brain

Reputation: 1751

(moved to the end of the first answer as requested)

Upvotes: 0

Lucky Brain
Lucky Brain

Reputation: 1751

This smells like a resource that has not been disposed. Can I ask you "Are you disposing of the connection and command right after the operation execution?": I recommend a using block that guarantees the disposal at the end of the block as in:

string sqlConnectionString = "...(put the connection string here)...";
string commandText = "...(put the SQL command here)...";
using (var connection = new SqlConnection(sqlConnectionString))
{
    using (var command = new SqlCommand(commandText, connection))
    {
        return command.ExecuteNonQuery();
    }
}

A different issue could be related to the connection pooling algorithm which indicates that, to benefit from pooling, the connection string value is quite important:

Only connections with the same configuration can be pooled. ADO.NET keeps several pools at the same time, one for each configuration. Connections are separated into pools by connection string, and by Windows identity when integrated security is used. Connections are also pooled based on whether they are enlisted in a transaction.

I know you probably read it already but this is the link to the documentation: SQL Server Connection Pooling (ADO.NET)

Please tell me how it goes!

Upvotes: 1

Related Questions