CODEWITHSUNDEEP
c#sql-server-2008database-connection
Fylix
Fylix

Reputation: 2713

Connection Pooling clear time... I don't really know what to call it :)

I have an client application that connects to the DB via the using clause.

  using (SqlConnection sqlConn = new SqlConnection(ConnectionString))
  {
      sqlConn.Open();
      //SQLcommand codes
  }

I know this will ensure the sqlConnn.Close() and sqlConn.Dispose() to be called. However, for each client after running this code I still see some of the SPID on SQLServer in sleeping mode such as:

60 0 sleeping sa AVENGER 0 Xmark AWAITING COMMAND 0 61 0 sleeping sa AVENGER 0 Xmark AWAITING COMMAND 0 62 0 sleeping sa AVENGER 0 Xmark AWAITING COMMAND 0

I know this is because I use connection pooling in my connection, that is why they are in sleep mode and to reuse again in further command. I see these processes get flushed out over time (after like 10 minutes or so) if I don't do anything with them.

My question is: Is there a setting either from C# or in SQLServer2008 that will reduce this time to like 2 minutes or so?

The problem I face is my max pooling connection limit reaches quickly if I have many clients connect to the Database around a short period of time. I realize I can fix it by increase my connection pooling, however I think that is like upgrade from a smaller to a bigger bowl to contain water from a leaked roof.

I searched on MSDN and came across the ClearPool() that will let me implicitly remove the SPID from the pool but I think that defeats the purpose of connection pooling and it is not really clean.

any help is greatly appreciated.

Upvotes: 0

Views: 1402

Answers (2)

Dinesh
Dinesh

Reputation: 3770

As you know the Sql server may not close the connection even when you call sqlconn.Close(), and will use the same connection for any other client connecting with the same connection string.

Ideally, Instead of looking to decrease this time, you should use a data access layer class that must act as helper class to create and manage connections.

Using such approach will avoid pooling issues and I have seen this approach used in good application architectures.

You should modify your code to this approach instead.

Upvotes: 1

Justin
Justin

Reputation: 2103

You are looking for the 'Idle Time-out' setting. It is configured for the application pool in IIS. Refer to this link

Upvotes: 0

Related Questions