We are using Petapoco ORM to write the DAL and have some questions about efficient Connection pool usage.
Q1) If the connection gets closed and disposed on every Query or Command (same connection string), Is it still able to use the pool or is it creating a new connection every time?
Q2) How can I tell if connection pool is really working. I see in the Azure Management Portal that my concurrent connection
can jump to the 50s... I am skeptical that we have that many running concurrently.
Q3) Please review the following SharedConnectionOpen code, is this "fighting" the pool?
// Open a connection (can be nested)
public void OpenSharedConnection()
if (_sharedConnectionDepth == 0)
_sqlConnection = _factory.CreateConnection();
_sqlConnection.ConnectionString = _connectionString;
_sqlConnection = OnConnectionOpened(_sqlConnection);
_sharedConnectionDepth++; // Make sure you call Dispose
Thx in advance,
Edited byUri KlukFriday, November 16, 2012 5:34 PM
Edited byUri KlukFriday, November 16, 2012 5:35 PM
Closing connections from the application doesn't really close the connection to the database; it remains open on the local machine connection pool. This avoids reopening the connection the next time a database connection is needed. That's why connections
remain open from the database standpoint for a long time. To release the connection and make it available in the connection pool you need to close it; so to answer your first question, you need to close the connection often to make it available. However, I
am not sure about "disposing" the connection; you should not have to do that.
If you see too many connections opened on the database, it either means that something prevents the connection pool from working (such as a different connection string) or you have many concurrent users.
To answer your second question, just monitor your connections in SQL Database (dm_exec_connections) in a controlled environment, where you are the only one using the system. If your application uses more connections over time, your code isn't leveraging
To your third question, it's hard to tell without knowing what _factory.CreateConnection() does and without confirming that the connection is the same everytime. Also, try not disposing your connection objects; just close them properly. This link shows the
proper way to open and close a connection with the using keyword to ensure the connections get closed:
Herve Roggero, MVP Windows Azure Co-Author: Pro SQL Azure http://www.bluesyntax.net
Marked As Answer byUri KlukWednesday, November 21, 2012 11:55 PM