{"id":254,"date":"2007-01-30T16:42:54","date_gmt":"2007-01-30T15:42:54","guid":{"rendered":"http:\/\/www.galhano.com\/blog\/?p=254"},"modified":"2008-02-07T18:38:34","modified_gmt":"2008-02-07T17:38:34","slug":"optimizando-o-pool-de-conneccoes-com-adonet-em-aplicacoes-aspnet","status":"publish","type":"post","link":"http:\/\/galhano.com\/blog\/?p=254","title":{"rendered":"Optimizando o pool de conec\u00c3\u00a7\u00c3\u00b5es com ADO.NET em aplica\u00c3\u00a7\u00c3\u00b5es ASP.NET"},"content":{"rendered":"\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Tuning Up ADO.NET Connection Pooling \t\t\tin ASP.NET Applications<\/font><\/td>\n<\/tr>\n
By Dmitri Khanine <\/font><\/td>\n<\/tr>\n
\t\t\tConnection Pooling Basics<\/strong><\/font><\/td>\n<\/tr>\n
Opening a database connection is a \t\t\tresource intensive and time consuming operation. Connection pooling \t\t\tincreases the performance of Web applications by reusing active database \t\t\tconnections instead of creating a new connection with every request. \t\t\tConnection pool manager maintains a pool of open database connections. \t\t\tWhen a new connection requests come in, the pool manager checks if the \t\t\tpool contains any unused connections and returns one if available. If \t\t\tall connections currently in the pool are busy and the maximum pool size \t\t\thas not been reached, the new connection is created and added to the \t\t\tpool. When the pool reaches its maximum size all new connection requests \t\t\tare being queued up until a connection in the pool becomes available or \t\t\tthe connection attempt times out.<\/font>Connection pooling behavior is controlled by the connection string \t\t\tparameters. The following are four parameters that control most of the \t\t\tconnection pooling behavior:<\/font><\/p>\n
    \n
  • Connect Timeout<\/strong> – controls the \t\t\t\twait period in seconds when a new connection is requested, if this \t\t\t\ttimeout expires, an exception will be thrown. Default is 15 seconds.<\/font><\/li>\n
  • Max Pool Size<\/strong> – specifies the \t\t\t\tmaximum size of your connection pool. Default is 100. Most Web sites \t\t\t\tdo not use more than 40 connections under the heaviest load but it \t\t\t\tdepends on how long your database operations take to complete.<\/font><\/li>\n
  • Min Pool Size<\/strong> – initial number of \t\t\t\tconnections that will be added to the pool upon its creation. \t\t\t\tDefault is zero; however, you may chose to set this to a small \t\t\t\tnumber such as 5 if your application needs consistent response times \t\t\t\teven after it was idle for hours. In this case the first user \t\t\t\trequests won’t have to wait for those database connections to \t\t\t\testablish.<\/font><\/li>\n
  • Pooling <\/strong>– controls if your \t\t\t\tconnection pooling on or off. Default as you may’ve guessed is true. \t\t\t\tRead on to see when you may use Pooling=false setting. <\/font><\/li>\n<\/ul>\n<\/td>\n<\/tr>\n
<\/td>\n<\/tr>\n
\t\t\tCommon Problems and Resolutions<\/strong><\/font><\/td>\n<\/tr>\n
Connection pooling problems are almost \t\t\talways caused by a “connection leak” – a condition where your \t\t\tapplication does not close its database connections correctly and \t\t\tconsistently. When you “leak” connections, they remain open until the \t\t\tgarbage collector (GC) closes them for you by calling their Dispose \t\t\tmethod. Unlike old ADO, ADO.NET requires you to manually close your \t\t\tdatabase connections as soon as you’re done with them. If you think of \t\t\trelying on connection objects to go out of scope, think again. It may \t\t\ttake hours until GC collects them. In the mean time your app may be dead \t\t\tin the water, greeting your users or support personnel with something \t\t\tlike this:<\/font><\/p>\n

\n
\nException: System.InvalidOperationException Message: Timeout expired. \t\t\tThe timeout period elapsed prior to obtaining a connection from the pool. \t\t\tThis may have occurred because all pooled connections were in use and \t\t\tmax pool size was reached. Source: System.Data at \t\t\tSystem.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString \t\t\toptions, Boolean& isInTransaction) at \t\t\tSystem.Data.SqlClient.SqlConnection.Open() …<\/span><\/font><\/p>\n

Exception: System.InvalidOperationException
\nMessage: Timeout expired. The timeout period elapsed prior to obtaining \t\t\ta connection from the pool. This may have occurred because all pooled \t\t\tconnections were in use and max pool size was reached.
\nSource: System.Data<\/font>\n<\/p><\/blockquote>\n


\nat \t\t\tSystem.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString \t\t\toptions, Boolean& isInTransaction)
\nat System.Data.SqlClient.SqlConnection.Open()<\/font><\/td>\n<\/tr>\n

 <\/td>\n<\/tr>\n
\t\t\tClosing your connections<\/strong><\/font><\/td>\n<\/tr>\n
When you intend to close your database \t\t\tconnection, you want to make sure that you are really closing it. The \t\t\tfollowing code looks fine yet causes a connection leak:<\/font><\/p>\n

\n \t\t\t\tSqlConnection conn = new \t\t\t\t\tSqlConnection(myConnectionString);
\nconn.Open();
\ndoSomething();
\nconn.Close();
\nIf doSomething() throws an exception – conn will never get \t\t\t\t\texplicitly closed. Here is how this can be corrected:
\nSqlConnection conn = new SqlConnection(myConnectionString);
\ntry<\/strong>
\n{
\nconn.Open();
\ndoSomething(conn);
\n}
\nfinally<\/strong>
\n{
\nconn.Close();
\n}
\n<\/span><\/font><\/p>\n

\t\t\t\tor<\/font><\/p>\n

\t\t\t\t
\nusing<\/strong> (SqlConnection conn = new SqlConnection(myConnectionString))
\n{
\nconn.Open();
\ndoSomething(conn);
\n}<\/span><\/font>\n<\/p><\/blockquote>\n

Did you notice that in the first \t\t\texample we called conn.Close() explicitly while in the second one we \t\t\tmake the compiler generate an (implicit) call to conn.Dispose() \t\t\timmediately following the using block? The C# using block guarantees \t\t\tthat the Dispose method is called on the subject of the using clause \t\t\timmediately after the block ends. Close and Dispose methods of \t\t\tConnection object are equivalent. Neither one gives you any specific \t\t\tadvantages over the other.<\/font><\/p>\n

When returning a connection from a class method – make sure you cache it \t\t\tlocally and call its Close method. The following code will leak a \t\t\tconnection:<\/font><\/p>\n

OleDbCommand cmd new OleDbCommand(myUpdateQuery, getConnection());<\/font><\/p>\n

intres = cmd.ExecuteNonQuery();<\/font><\/p>\n

getConnection().Close(); \/\/ The connection returned from the first call \t\t\tto getConnection() is not being closed. Instead of closing your \t\t\tconnection, this line creates a new one and tries to close it.<\/font><\/p>\n

If you use SqlDataReader, OleDbDataReader, etc., close them. Even though \t\t\tclosing the connection itself seems to do the trick, put in the extra \t\t\teffort to close your data reader objects explicitly when you use them.<\/font><\/p>\n

Last but not the least, never Close or Dispose your connection or any \t\t\tother managed object in the class destructor or your Finalize method. \t\t\tThis not only has no value in closing your connections but also \t\t\tinterferes with the garbage collector and may cause errors. For more \t\t\tinformation see http:\/\/msdn.microsoft.com\/library\/en-us\/cpguide\/html\/cpconprogrammingessentialsforgarbagecollection.asp.<\/font><\/td>\n<\/tr>\n

 <\/td>\n<\/tr>\n
\t\t\tTesting your changes<\/strong><\/font><\/td>\n<\/tr>\n
The only way to know the effect of \t\t\tyour changes on connection pooling behavior is to load-test your \t\t\tapplication. If you have existing unit tests – use them. Running your \t\t\tunit tests repeatedly in a loop may create a fair bit of stress on \t\t\tapplication. If you don’t, use the Web load testing tool. There are \t\t\tplenty of commercial load testing tools on the market. If you prefer \t\t\tfreeware, consider OpenSTA available at www.opensta.org. All you need to \t\t\tsetup your load test is to install the tool, bring up your Web \t\t\tapplication and click your way through. OpenSTA will record your HTTP \t\t\trequests into test scenarios that you can run as part of your load test.<\/font>Knowing that your application crashes under the load doesn’t often help \t\t\tto locate the problem. If the app crashes fairly quickly, all you may \t\t\tneed to do is run several load tests – one for each module and see which \t\t\tone has a problem. However, if it takes hours to crash you will have to \t\t\ttake a closer look.
\n<\/font><\/td>\n<\/tr>\n
\t\t\tMonitoring connection pooling behavior<\/strong><\/font><\/td>\n<\/tr>\n
Most of the time you just need to know \t\t\tif your application manages to stay within the size of its connection \t\t\tpool. If the load doesn’t change, but the number of connections \t\t\tconstantly creep even after the initial “warm-up” period, you are most \t\t\tlikely dealing with a connection leak. The easiest way to monitor the \t\t\tnumber of database connections is by using the Performance Monitor \t\t\tavailable under Administrative tools on most Windows installations. If \t\t\tyou are running SQL Server, add SQL Server General Statistics -> User \t\t\tConnections performance counter (The counter is available on the SQL \t\t\tServer machine so you may need to put its name or IP address into the \t\t\tSelect Counters From Computer box). The other way to monitor the number \t\t\tof database connections is by querying your DBMS. For example, on SQL \t\t\tServer run:<\/font><\/p>\n

\n \t\t\t\tEXEC SP_WHO<\/span><\/font><\/font><\/p>\n

Or on Oracle, run:<\/font><\/p>\n

SELECT * FROM V$SESSION WHERE PROGRAM IS NOT NULL<\/font><\/p>\n

.NET CLR Data performance counters<\/font>\n<\/p><\/blockquote>\n

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

 <\/td>\n<\/tr>\n
\t\t\tConclusion<\/strong><\/font><\/td>\n<\/tr>\n
In this article you’ve learned that \t\t\tthe most common cause of connection pooling issues is database \t\t\tconnections that are left open or not closed properly. You’ve learned \t\t\tthat when you type “conn.Close()”, you almost always want to put that in \t\t\tthe “Finally” block. You also learned not to interfere with the class \t\t\tdestructor unless you use unmanaged resources. You’ve learned how to \t\t\tmonitor your connection pool and diagnose a potential problem. You also \t\t\tlearned how to keep a system with a connection leak in production if you \t\t\treally have to, until the problem is resolved. I hope this article has \t\t\thelped you resolve your connection pooling issue. However, there is more \t\t\tto connection pooling that is not covered in this article. Check out \t\t\tBill Vaughn’s “Swimming in the .NET connection pool” at \t\t\t \t\t\thttp:\/\/www.winnetmag.com\/Article\/ArticleID\/38356\/38356.html<\/a>.<\/font><\/td>\n<\/tr>\n
 <\/td>\n<\/tr>\n
\t\t\tAbout the Author<\/strong><\/font><\/td>\n<\/tr>\n
Dmitri Khanine is senior web developer \t\t\tand architect working for a major Canadian Bank. His 10+ years of \t\t\texperience are mostly in backend and middle tier development of \t\t\tenterprise Web applications on Microsoft as well as J2EE platforms. \t\t\tIndustry experience includes Banking, Finance, Automotive and software \t\t\tconsulting. Dmitri’s areas of interest and expertise include rapid \t\t\tenterprise application development, MVC frameworks and code generation. \t\t\tDmitri can be contacted at Khanine@hotmail.com.<\/font><\/td>\n<\/tr>\n
 <\/td>\n<\/tr>\n<\/table>\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

Tuning Up ADO.NET Connection Pooling in ASP.NET Applications By Dmitri Khanine Connection Pooling Basics 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"close","ping_status":"close","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,40,9],"tags":[],"class_list":["post-254","post","type-post","status-publish","format-standard","hentry","category-aspnet","category-sql","category-sql-server","author-admin"],"_links":{"self":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/254","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=254"}],"version-history":[{"count":0,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/254\/revisions"}],"wp:attachment":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=254"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=254"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=254"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}