Very Strange SQL Server Connection Pooling issue. RRS feed

  • Question

  • Hi there,


    I am having a bizarre issue with connection pools.  My environment SQL 2005 (32 bit), ASP.NET Web Service on a 64 bit 2003 test server, and a load test.


    Doing some basic load testing, I have two tests.  The first opens and closes about 3 queries and returns a response.  The second is simply an extension of the first with about 7 queries (4 more).  If I run the second test with 200 concurrent connections, I'm getting a good response time.  I look at the sql server and I can see a nice connection pool of about 7 connections, all being utilized and the CPU is about 60%.  My ASP.NET server is running at about 90% (it's doing some calculations as well) and this performance is well above what we require.


    After running this test for about 10 minutes.  I switch to the simpler first test and run it for several minutes.  It runs even better because it's simpler.  Now here is where it gets strange....sometimes if I go back to the complex test and run it, the performance drops significantly.  The reason, is that the connection pool for some reason decides only to use one of it's connections.  In Perfmon it says it has 7, if I look on the SQL Server I can see 7 processes running....but only 1 is being used.  Eventually, SQL Server times out the connections and I end up with essentially a connection pool of 1.


    Here is the even stranger part.  If I go back to my first test again, it starts a little slowly, but eventually all of the 7 items in the connection pool come back to life and are in use.  And, when I run test 2 again, it works perfectly with good performance.


    I have no idea what is causing this.  Is it possible that the connection pool has some intelligence that is tracking the commands that are being fired and re-using them?  Is my brute force load test causing a case that might be confusing this intelligence.  I'm quite concerned because a production system ending up in this state would be pretty useless.





    Thursday, August 30, 2007 11:18 PM

All replies

  • Your problem does sound a bit strange but without seeing the patient, I can't really provide more than a cursory advice. Perhaps my whitepaper on the connection pool will help.

    No, the connection pooling mechanism does not "inspect" the command text--it only inspects the connection object's connectionstring. Now, the SQL Server does "inspect" the command text (as you would expect). I would be more inclined to think that there was some blocking interaction between the processes that affected performance...



    Friday, August 31, 2007 4:27 AM
  • Thanks William, I figured as much, I'm kind of guessing at this point.  It's like I have the reverse problem that most people are posting when they run out of cursors.  I've always been dilligent in managing the connections so to avoid leaks.


    I hate to suggest it, but I'm wondering if maybe theres a bug in the connection pooling, perhaps for 64 bit that I'm running into.  I've had no luck replicating this issue on a 32 bit system but unfortunately the system is less powerful and doesn't load the pool as much.  I'm not sure what I could be doing to force a connection pool to stop pooling.   And if I'm doing something to damage the connections, why does the last one always stick around?  And why, when I change my load test, does the pool start working again?


    I've also had some strange issues trying to see the performance monitors.  I had to run perfmon in 32 bit mode, and they don't really seem to work that well. 






    Friday, August 31, 2007 4:48 AM
  • I have done some more investigation into this issue, which at least has proved useful.  I have found that once I get my connection pool into the "non-pooling" state, if I run my test for a long period of time it eventually sorts itself out and performance goes up.  This leads me to think that something is eventually timing out and returning the pool to it's working state.  I still have a few questions that are unanswered though:


    1.  If I have db connections that are blocking and can't be used by the pool, why isn't the pool creating additional connections?  I have the max pool size set to 100, but when this issue occurs no new connections are being added.


    2.  If indeed these connections are timing out and returning to a working state, what is this timeout?  I've read a few places that the maximum lifespan of these connections are 4-8 minutes by default.


    3.  IF indeed there is a problem with my code that causes the connection pool to function in this manner, what would that scenario look like?  If I wanted to create this state, how would I go about doing this? 


    Anyway, the fact that the pool eventually returns to normal is a positive one. 

    Friday, August 31, 2007 4:48 PM
  • You definitely need to read my whitepaper. A pooled connection never goes away until the process that created them ends or the process closes the connection (at which time the connectoid and the connection remain viable for 4-8 minutes). Yes, I would not doubt that there might be a 32 vs 64 bit error--I've seen several so far. Try running the application on the 64bit platform in "compatibility" mode (WOW) to see if it behaves differently. I'll bet it won't as this is a Database Engine issue...




    Friday, August 31, 2007 9:29 PM