.NET Connection Pooling issues



The below text has been taken from different websites, I have tried to put it all together in one place to simplify the understanding and resolution for connection pooling issues (Enjoy !)

1) What is connection pooling? Some basic concepts..
2) Common issues and some typical Fixes
i) How to properly close a connection
ii) When returning a connection from a class method
iii) Pool fragmentation
3) Short term fixes and workarounds Number 1
4) Short term fixes and workarounds Number 2
5) Permanent FIX
6) Monitoring Connection pooling counters
7) Some brief information about causes of connection pooling


1) What is connection pooling? Some basic concepts..
Opening a database connection is a resource intensive and time consuming operation. Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. Connection pool manager maintains a pool of open database connections. When a new connection requests come in, the pool manager checks if the pool contains any unused connections and returns one if available. If all connections currently in the pool are busy and the maximum pool size has not been reached, the new connection is created and added to the pool. When the pool reaches its maximum size all new connection requests are being queued up until a connection in the pool becomes available or the connection attempt times out.
Connection pooling behavior is controlled by the connection string parameters. The following are four parameters that control most of the connection pooling behavior:
  • 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.
2) Common issues and some typical Fixes
Connection pooling problems are almost always caused by a "connection leak" - 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 their Dispose method. Unlike old ADO, ADO.NET requires you to manually close your database connections as soon as you're done with them. If you think of relying on connection objects to go out of scope, think again. It may take hours until GC collects them. In the mean time your app may be dead in the water, greeting your users or support personnel with something like this:

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

i) How to properly close a connection
When you intend to close your database connection, you want to make sure that you are really closing it. The following code looks fine yet causes a connection leak:
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.

ii)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 seehttp://msdn.microsoft.com/library/en-us/cpguide/html/cpconprogrammingessentialsforgarbagecollection.asp.

iii)POOL FRAGMENTATION

3) Short term fixes and workarounds Number 1
Please note:
Both of these workarounds will cause heavy performance degradation.
What if you discovered the connection pooling issue in production and you cannot take it offline to troubleshoot? Turn pooling off. Even though your app will take a performance hit, it shouldn't crash! Your memory footprint will also grow. What if it doesn't crash all that often, and you don't want to take a performance hit? Try this:
conn = new SqlConnection();
try
{
conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;";     // Notice Connection Timeout set to only two seconds!
conn.Open();
}
catch(Exception)
{
if (conn.State != ConnectionState.Closed) conn.Close();
conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Pooling=false;Connect Timeout=45;";
conn.Open();
If I fail to open a pooled connection within two seconds, I am trying to open a non-pooled connection. This introduces a two second delay when no pooled connections are available, but if your connection leak doesn't show most of the time, this is a good steam valve.

4) Short term fixes and workarounds Number 2
Increase the Max Pool Size by another 100 connections
Use this in a try catch block
conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=200;Connect Timeout=2;";

5)Permanent FIX
Review your application,
In an efficient production system, typically the number of pools is low (1 to 10) and the total number of connections in use is also low (fewer than 12). An efficient query takes less than a second to complete and disconnect. So even if hundreds of customers are accessing your Web site simultaneously, relatively few connections can often handle the entire load


7) Some brief information about causes of connection pooling  (taken from Microsoft website)
Email discussion group participants often complain about how applications seem to work in testing but fail in production. Sometimes they report that their applications stop or bog down when about 100 clients get connected. Remember that the default number of connections in a pool is 100. If you try to open more than 100 connections from a pool, ADO.NET queues your application's connection request until a connection is free. The application (and its users) sees this as a delay in getting to the Web page or as an application lock-up. Let's look at how this problem arises.

In ADO.NET, the SqlClient .NET Data Provider gives you two techniques for opening and managing connections. First, when you need to manage the connection manually, you can use the DataReader object. With this method, your code constructs a SqlConnection object, sets the ConnectionString property, and uses the Open method to open a connection. After the code is finished with the DataReader, you close the SqlConnection before the SqlConnection object falls out of scope. To process the rowset, you can pass the DataReader to another routine in your application, but you still need to make sure that the DataReader and its connection are closed. If you don't close the SqlConnection, your code "leaks" a connection with each operation, so the pool accumulates connections and eventually overflows. Unlike in ADO and Visual Basic (VB) 6.0, the .NET garbage collector won't close the SqlConnection and clean up for you. Listing 1, which I walk through later, shows how I opened a connection and generated a DataReader to return the rowset from a simple query to stress the connection pool.

You can also run into problems when you use the DataAdapter object. The DataAdapter Fill and Update methods automatically open the DataAdapter object's connection and close it after the data I/O operation is complete. However, if the connection is already open when the Fill or Update method is executed, ADO.NET doesn't close the SqlConnection after the method completes. This is another opportunity to leak a connection.

In addition, you can also use COM-based ADO to create a connection from a .NET application. ADO pools these connections in the same way that ADO.NET does but doesn't give you a way to monitor the pool from your application as you can when you use the SqlClient ADO.NET Data Provider.

Indicting the DataReader
Orphaned connections and overflowing pools are serious problems, and judging by the number of newsgroup discussions about them, they're fairly common. The most likely culprit is the DataReader. To test the behavior of the DataReader, I wrote a sample Windows Forms (WinForms) application concentrating on the CommandBehavior.CloseConnection option. (You can download this application by entering InstantDoc ID 39031 at http://www.sqlmag.com.) You can set this option when you use the SqlCommand object's ExecuteReader method to execute the query and return a DataReader. My test application shows that even when you use this option, if you don't explicitly close the DataReader (or SqlConnection), the pool overflows. The application then throws an exception when the code requests more connections than the pool will hold.

Some developers insist that if you set the CommandBehavior.CloseConnection option, the DataReader and its associated connection close automatically when the DataReader finishes reading the data. Those developers are partially right but the option works this way only when you're using a complex bound control in an ASP.NET Web application. Looping through a DataReader result set to the end of its rowset (that is, when Dr.Read—the DataReader's Read method—returns false) isn't enough to trigger automatic connection closing. However, if you bind to a complex bound control such as the DataGrid, the control closes the DataReader and the connection—but only if you've set the CommandBehavior.CloseConnection option.

If you execute a query by using another Execute method (e.g., ExecuteScalar, ExecuteNonQuery, ExecuteXMLReader), you are responsible for opening the SqlConnection object and, more importantly, closing it when the query finishes. If you miss a close, orphaned connections quickly accumulate.
Hope this helps

Thanks
Regards
Huzeifa Bhai
Posted by
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)