Sql Connection Pooling in .Net

Sql Connection pooling is one of the hidden heros that not many developers I know will take time to look into.

Granted, ADO.NET covers up so much of whats really going on, and takes care of of the nitty gritty details, so as a .net developer, you should have to worry too much -> WRONG! When it comes to optimizing performance, you NEED to know whats really happening!

Sql Connection Pool Basics: 

Fact: Opening a database connection is a resource intensive and is a time consuming operation.

A connection pool is a way to cache sql connections that are maintained so that the connections can be reused when future requests to connect to a database is needed. By reusing active database connections,  the performance of .Net applications, be it Web or Windows,  is increased!

Connection Pools in .Net are managed and maintained by the Connection Pool Manager.  When a new connection request come in, the Connection Pool Manager will check if the current connection pool contains any unused connections. If there are connections available it will return it, otherwise if all connections currently in the pool are busy and the maximum pool size has not been reached, a new connection is created and added to the pool. When the pool reaches its maximum size, all new requests are queued until a connection in the pool becomes available or the connection attempt times out.

Controlling the connection pooling behavior can be done via the use of connection string parameters. Below are the most common parameters that control most of the connection pooling behavior:

• Connect Timeout - controls the wait period in seconds when a new connection is requested (As mentioned above), if this timeout expires, an exception will be thrown. The default connection timout is 15 seconds.
• Max Pool Size - specifies the maximum size of your connection pool. By default it is 100 connections. 
• Min Pool Size - specifies the initial number of connections that will be added to the pool upon its creation. By default, this value is 0.
• Pooling - controls whether or not to use connection pooling. The default value is true.

Most Common Issues and Resolutions

The following exception is most probably the most common issue:
"Exception: System.InvalidOperationException
Message: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
Source: System.Data  

at  System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
   at System.Data.SqlClient.SqlConnection.Open()"

This exception usually happens due to Connections Leaks - a condition where your application does not close its database connections correctly and consistently.

When you "leak" connections, they remain open until the garbage collector (GC) closes them for you by calling the Dispose method on your SqlConnection object. Unlike the old ADO, ADO.NET requires you to manually close your database connections as soon as you're done with them. As we cannont rely on the GC to do the work for us, as GC can take a very long time to collect, we have to do this in code. There are 3 ways to achieve this:

Using the Close() method:

SqlConnection conn = new SqlConnection(myConnectionString);

conn.Open();

//execute query

conn.Close();

Using a "Try...Finally" clause:

SqlConnection conn = new SqlConnection(myConnectionString);

try

{

   conn.Open();

   //execute query

}

finally

{

   conn.Close();

}

and using the "using()" statement:

using (SqlConnection conn = new SqlConnection(myConnectionString))

{

conn.Open();

//execute query

}

If you use SqlDataReader, OleDbDataReader, etc., close them. Even though closing the connection itself seems to do the trick, put in the extra effort to close your data reader objects explicitly when you use them!

For more information, you can read:

http://betav.com/blog/billva/2007/05/managing_and_monitoring_net_co.html

C#.Net - Waiting for Threads using WaitHandles and AutoResetEvents

There have been a few times where I had 1 main executing thread which needed to break huge tasks into smaller pieces, and then have the main thread wait for the smaller tasks to complete.For Example, and ASPX page.


The way Im going to show us makes use of WaitHandle objects and AutoResetEvents.


Review the code below and see how they are used. If you would like to discuss how they actually work, please leave a comment.


class Program{

 static void Main(string[] args)
 {
    WaitHandle[] waitHandles = new WaitHandle[5];
    Console.WriteLine("Starting Threads");

    for (int ii = 0; ii < 5; ii++)
    {
  //create the wait handle and set it not to be signaled. The means when its asked to wait,   no thread can carry on.
    waitHandles[ii] = new AutoResetEvent(false);
    Thread thread = new Thread(() => { DoWork(ii.ToString(), (AutoResetEvent)waitHandles   [ii]); });
    thread.Start();
    //wait 1 second before carrying on
    Thread.Sleep(1000);

    Console.WriteLine("Waiting for threads to complete");
    //Wait for the threads
    WaitHandle.WaitAll(waitHandles.ToArray());
    Console.WriteLine("Allthreads are complete!");
    Console.Read();

 }

 static void DoWork(string name, AutoResetEvent waitHandle)
 {
    try
    {
    Console.WriteLine(string.Format("Doing Work on thread {0}", name));
    //make the do work pretend its doing something improtant like updating a DB.
    Thread.Sleep(5000);
    Console.WriteLine(string.Format("Doing Done", name));
    }
    finally
    {
    //set the wait handle. This marks it as signaled and other threads can carry on.
    waitHandle.Set();
    }
 }

 When it comes to using these object, you MUST TAKE CARE. Its very easy to leave a thread in an infinate waiting loop. For example, if an exception was thrown in the DoWork() method, and I didnt have the AutoResetEvent set in the finally clause, you could see how the event may never be set. So take great care when it comes to multi threading!


}

Lambda and Threading C#

I have found a new way to start a thread that used lambda to point to the method that needs to be executed.
Below is a code snippet on how this can be done!

class Program
    {
        static void Main(string[] args)
        {
            Thread thread = new Thread(() => { Sum(2, 1); });
            thread.Start();
            Console.Read();
        }
 
        public static void Sum(int a, int b)
        {
            Console.Write(a + b);
        }
    }

What happens here is that when the code is compiled, and anonymous method is created which then points to the Sum method! Give it a go!

Welcome to Daves Take

Welcome to Daves Take. This blog will be mainly be used for programming tips and examples where we can all help each other. We can also have discussions on technologies and things to come in the programming world!