Galhano //127.0.0.1

• Connect Timeout – controls the wait period in seconds when a new connection is requested, if this timeout expires, an exception will be thrown. Default is 15 seconds.
• Max Pool Size – specifies the maximum size of your connection pool. Default is 100. Most Web sites do not use more than 40 connections under the heaviest load but it depends on how long your database operations take to complete.
• Min Pool Size – initial number of connections that will be added to the pool upon its creation. Default is zero; however, you may chose to set this to a small number such as 5 if your application needs consistent response times even after it was idle for hours. In this case the first user requests won’t have to wait for those database connections to establish.
• Pooling – controls if your connection pooling on or off. Default as you may’ve guessed is true. Read on to see when you may use Pooling=false setting.

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() …

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()

SqlConnection conn = new SqlConnection(myConnectionString);
conn.Open();
doSomething();
conn.Close();
If doSomething() throws an exception – conn will never get explicitly closed. Here is how this can be corrected:
SqlConnection conn = new SqlConnection(myConnectionString);
try
{
conn.Open();
doSomething(conn);
}
finally
{
conn.Close();
}

or

using (SqlConnection conn = new SqlConnection(myConnectionString))
{
conn.Open();
doSomething(conn);
}

Did you notice that in the first example we called conn.Close() explicitly while in the second one we make the compiler generate an (implicit) call to conn.Dispose() immediately following the using block? The C# using block guarantees that the Dispose method is called on the subject of the using clause immediately after the block ends. Close and Dispose methods of Connection object are equivalent. Neither one gives you any specific advantages over the other.

When returning a connection from a class method – make sure you cache it locally and call its Close method. The following code will leak a connection:

OleDbCommand cmd new OleDbCommand(myUpdateQuery, getConnection());

intres = cmd.ExecuteNonQuery();

getConnection().Close(); // The connection returned from the first call to getConnection() is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.

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.

Last but not the least, never Close or Dispose your connection or any other managed object in the class destructor or your Finalize method. This not only has no value in closing your connections but also interferes with the garbage collector and may cause errors. For more information see http://msdn.microsoft.com/library/en-us/cpguide/html/cpconprogrammingessentialsforgarbagecollection.asp.

EXEC SP_WHO

Or on Oracle, run:

SELECT * FROM V$SESSION WHERE PROGRAM IS NOT NULL

.NET CLR Data performance counters

In documentation you may run into .Net CLR Data performance counters. They are great if you know what they can and cannot do. Keep in mind that they do not always reset properly. The following KB article sheds some light on the problem but in my opinion does not cover all the issues: http://support.microsoft.com/default.aspx?scid=kb;en-us;314429. Another thing to keep in mind is that IIS unloads app domains under stress so don’t be surprised when your number of database connections has dropped to zero while your min pool size is five!