Saturday, February 18, 2006

Connection Pooling - Huh!

I got really frustrated after i got complaints continously about Connection pooling timeout expired problem in our web application. After some googling i found few solution for the problem.

Design Properly
The first and foremost thing in any kind of software development is to design it properly as it reduces lot of problems. Before using any new technology its always best to know what it is and what are the pros and cons and how to use it EFFECTIVELY.

Whenever you are using Data Access Layer the first thing to do is to know about the Architectural design which clearly defines how to use them effectively in our software development.

Connection Pooling - what it is?
When a connection is opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created.

Controlling Connection Pooling with Connection String Keywords
We can control the connection pooling using the connection string keywords.

Cnnection Lifetime - This specifies the life time of connection string [default=0]
Connection Reset - Determines whether the database connection is reset when being removed from the pool [default:true]
Enlist - When true, the pooler automatically enlists the connection in the current transaction context of the creation thread if a transaction context exists [default: true]
Max Pool Size - The maximum number of connections allowed in the pool [default:100]
Min Pool Size - The minimum number of connections maintained in the pool [default: 0]
Pooling - When true, the connection is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool. [default:true]

Connection Leak? - How can i find?
It was very hard to figure out if you were leaking connections in v1.0 and v1.1. Now microsoft have added new performance counters. With ADO.NET 2.0 if you see the NumberOfReclaimedConnections performance counter go up you know that your application is leaking connections.

The following table lists the connection pooling counters that can be accessed in Performance Monitor under the ".NET CLR Data" performance object.

Counter | Description
SqlClient: Current # of pooled and non pooled connections | Current number of connections, pooled or not.
SqlClient: Current # pooled connections | Current number of connections in all pools associated with the process.
SqlClient: Current # connection pools | Current number of pools associated with the process.
SqlClient: Peak # pooled connections | The highest number of connections in all pools since the process started. Note: this counter is only available when associated with a specific process instance. The _Global instance will always return 0.
SqlClient: Total # failed connects | The total number of connection open attempts that have failed for any reason.

How to fix the leak?
You need to guarantee that the connection close _OR_ dispose gets called. The easiest way is with the “using” construct

public void DoesNotLeakConnections()


Using (SqlConnection sqlconnection1 = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5")) {


SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

sqlcommand1.CommandText = "raiserror ('This is a fake exception', 17,1)";

sqlcommand1.ExecuteNonQuery(); //this throws a SqlException every time it is called.

sqlconnection1.Close(); //Still never gets called.

} // Here sqlconnection1.Dispose is _guaranteed_


How "using" construct works?
The Using construct is equivalent to a Try/…/Finally{ .Dispose() ). Even when ExecuteNonQuery tries to throw out of the execution scope we guarantee that the code in the Finally block will get called

Some DONT'S in connection string
a) Do not do Pooling = False.
b) Do not do Connection Lifetime = 1;
c) Do not do Connection Timeout = 40000;
d) Do not do Max Pool Size = 40000;

Hope this gives some information about connection pooling.


No comments: