Determining number of open connections in your connection pools

Posted on 3/19/2007 @ 3:13 AM in #Vanilla .NET by | Feedback | 19460 views

Okay, it's 3 AM, and this was just too intersting and useful to put down.

Frequently, you run into a situation where your application is leaking open connections. Wouldn't it be nice, if somehow you could keep a tab on the # of open connections your application has maintained?

No, I don't mean SqlConnections, I mean the actual physical database connections open at a given time.

Well, get ready for some super-bad-ass-code !!

First, I wrote a simple SQL query to sniff how many connections I really have open.

SELECT SPID,

          STATUS,

          PROGRAM_NAME,

       LOGINAME=RTRIM(LOGINAME),

          HOSTNAME,

          CMD

FROM  MASTER.DBO.SYSPROCESSES

WHERE DB_NAME(DBID) = 'TEST' AND DBID != 0 

Okay good.  Next I created my test application which looks like this -

 

Great. Now the idea being, that the user is going to specify a certain # of connections to open and KEEP open UNTIL the Close connections button is hit.

Here's the code for that -

private List<SqlConnection> connections = new List<SqlConnection>();

 

private void button1_Click(object sender, EventArgs e)

{

    for (int i = 1; i <= numConns.Value; i++)

    {

        SqlConnection conn =

            new SqlConnection(ConfigurationManager.ConnectionStrings["HomePc"].ConnectionString);

        conn.Open();

        connections.Add(conn);

    }

}

 

private void button2_Click(object sender, EventArgs e)

{

    foreach (SqlConnection conn in connections)

    {

        conn.Close();

        conn.Dispose();

    }

}

Great!

Now, if you test the App, go ahead and run it - and run the TSQL query I showed ya at the beginning of this post, you should see results similar to this -

Now as the app is running, change the NumericUpDown to a finite countable number like "5" or "10". I'm gonna change it to 5. And click "Open Connections".

Run the TSQL Query again, here is what the output should look like -

Now, click "Close Connections", and without any delay, quickly run the same TSQL query again, your results will look like this -

Now, if you do understand connection pooling - you wouldn't be surprised at all here. Because, even though SqlConnection.ConnectionState is closed - the physical database connect is still open. Heck 5 of 'em! Because when you opened 5 connections initially, you kept opening them without closing them first - so the framework's pooling mechanism had no choice but to open new physical connections for you.
(Note: the 6th connection you see up there is me logged in via Sql Server Mgmt. Studio)

Now the SQL Query gives me a good # of physical db connections open. But,

 - In a production scenario, I may not be able to run this, because I might not have access to the master DB.
 - This only gives me a count of physical DB conns open at the database level.
 - This gives me no insight of how many conns are contained in which pool, and which pool is on which machine?

So ideally, what you need is, an ability to check and see, how many connection pools do you have, and how many physical database connections do you really have open in your connection pool.

Here is the code simplified assuming there is a single conn. pool in my app (which can be easily extended to as many pools as you want):

private void button3_Click(object sender, EventArgs e)

{

    SqlConnection guineaPigConnection =

        new SqlConnection(ConfigurationManager.ConnectionStrings["HomePc"].ConnectionString);

 

    Type sqlConnType = typeof(SqlConnection);

    FieldInfo _poolGroupFieldInfo =
      sqlConnType.GetField("_poolGroup", BindingFlags.NonPublic | BindingFlags.Instance);

    object dbConnectionPoolGroup =
      _poolGroupFieldInfo.GetValue(guineaPigConnection);

    FieldInfo _poolCollectionFieldInfo =
      dbConnectionPoolGroup.GetType().GetField("_poolCollection",
         BindingFlags.NonPublic | BindingFlags.Instance);

    HybridDictionary poolConnection =
      _poolCollectionFieldInfo.GetValue(dbConnectionPoolGroup) as HybridDictionary;

 

    foreach (DictionaryEntry poolEntry in poolConnection)

    {

        object foundPool = poolEntry.Value;

        FieldInfo _objectListFieldInfo =

           foundPool.GetType().GetField("_objectList",
              BindingFlags.NonPublic | BindingFlags.Instance);

        object listTDbConnectionInternal =
           _objectListFieldInfo.GetValue(foundPool);

        MethodInfo get_CountMethodInfo =  

            listTDbConnectionInternal.GetType().GetMethod("get_Count");

        object numConnex = get_CountMethodInfo.Invoke(listTDbConnectionInternal, null);

        MessageBox.Show(numConnex.ToString(), "Number of Physical DB Conns open");

    }

}

As expected, this tells me exactly the # of physical DB connections open in my connection pool. So even after I hit "Close Connections", the above correct reports 5 connections open as shown below -

Now that, my dear friends, is some SERIOUS BAD-ASS code!! (and heckuva useful too frankly).

Damn, gotta sleep. G'nite!

Sound off but keep it civil:

Older comments..


On 3/19/2007 4:15:52 AM Frans Bouma said ..
There's also a performance counter for this, so you could also read that out, of course that would report the connections a single client would have, but still, it would be possible without using a physical connection. :)


On 3/19/2007 4:56:40 AM Vivek Prahlad said ..
Hey Sahil, your RSS feed is generating invalid XML - could you check? Vivek


On 3/19/2007 8:58:09 AM Sahil Malik said ..
Frans, but the above is really useful if your app is leaking open connections.


You could start splitting up stuff into multiple conn pools in debug mode, and see which one is rising consistently. :)


On 3/19/2007 8:58:33 AM Sahil Malik said ..
Vivek - really? Anyone else having the same issue?


On 3/19/2007 7:18:04 PM Liming Xu said ..
Great article! It's extremely helpful to my understanding of connection pooling. Thanks


On 3/19/2007 7:29:24 PM Tim Meers said ..
So is there anyways you could post up the source for this in doanload form? Looks to be a fantastic little tool that I would love to play with as I finish deploying my site.

Tim


On 3/19/2007 8:59:52 PM Sahil Malik said ..
Liming - I almost deleted your comment thinking "Another spam comment" until I actually saw your blog. LOL :)


On 3/19/2007 9:01:15 PM Sahil Malik said ..
Tim -

Yes and No.

I host only text on my blog, all images come from imageshack. If I start putting binaries on my blog, my bandwidth bills will kill me. I am already drawing about 30GB per month with compression :-/, so, if you can find me a place to put this app online, I'll do it.

SM


On 3/19/2007 9:02:59 PM Sahil Malik said ..
Tim,

For the meanwhile, it's literally copy pasting what I posted above, so if you try and peice the app together, it shouldn't be that hard.

SM


On 3/21/2007 5:13:09 PM Martin Kulov said ..
Hice work, dude!


On 3/21/2007 9:21:37 PM Sahil Malik said ..
Heheh :) Martin - glad you like it ! :)


On 3/25/2007 2:37:39 PM Tim Meers said ..
Ok well I tried to copy paste, and well it's just not the same as I'm used to. I primarly work with web stuff, it just makes more sense to me. But I did "remake" an app i've been working of as of late if you wanted to upload it. http://gettinlucky.dyndns.org/shared/ it just stores everything in an SQL DB as binary the I pull things from there as a link ie http://gettinlucky.dyndns.org/shared/get.aspx?id=1 and it pulls the image or downloads the zip or what ever it is. But I do think this is good work by the way.

Tim


On 4/19/2007 7:10:00 PM Jacky Chen said ..
Nice, thanks for sharing, Sahil.

A few days ago I ran into a problem that I think it's related to the sql connection pool;

The code is simple,

try


{


using( SqlConnection conn = new SqlConnection(connStr) )


{


try


{


conn.Open();


}


catch(SqlException e)


{


//report the error when connection cannot be esteblished


}


}


}

it works fine on a running Sql server instance and a not-running instance


but the problem is when i first test it against a running server then terminate the server instance, and this code will continue not to raise exception, in other words, the connection can be Open() fine!

Do you have any idea why this is happening? (it's on .net 1.1)

Thanks again for the nice blog.


On 7/10/2007 6:47:44 AM Sonal Patil said ..
Hi Sahil,


thanks for this nice article.


i used whatever SQL query u have given to calculate the number of connections in my project. It worked fine.

But i am facing one issue. i am executing this query after every 30 secs to get the updated number of connections, but i am not getting the updated values.

In SQL Server Enterprise Manager -> Management -> Current Activity -> Process Info, it is not shwoing the updated Process ID (number of connections) of that particular database. but if right click Current Activity and manually refresh it. it shows me the updated info under Process Info.


Can you please tell me how to get the updated values through SQL query?

thanks,


Sonal


On 5/8/2009 1:56:28 AM Jim said ..
Sahil

You say that this could be extended for multiple connection pools. Now if I wanted to do this in a MOSS scenario, that hosts multiple custom apps/web-parts as well, how would I go about doing this.

Jim


On 5/8/2009 4:45:41 PM Sahil Malik said ..
Jim - it'd be a matter of running the above app on the MOSS server. Nothing special that MOSS introduces.

S


On 11/17/2009 2:10:40 AM Shyam Shinde said ..
The article is nice.


On 12/22/2010 3:43:45 AM Sonalika said ..
Please tell me the code by which i can close the opened connection


Thanks in advance


On 2/2/2012 6:09:26 AM SL said ..
Hi,

i have to get the status of all connections in the connection pool on a jsp.. please suggest a java code for it..

thanks,


SL


On 4/2/2012 1:14:43 PM Brian said ..
Awesome! Just what I was looking for and better! Thanks for posting.