StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>In most cases connection pooling problems are related to "connection leaks." Your application probably doesn't close its database connections correctly and consistently. When you leave connections open, they remain blocked until the .NET garbage collector closes them for you by calling their <code>Finalize()</code> method.</p> <p>You want to make sure that you are <strong>really closing the connection</strong>. For example the following code will cause a connection leak, if the code between <code>.Open</code> and <code>Close</code> throws an exception:</p> <pre><code>var connection = new SqlConnection(connectionString); connection.Open(); // some code connection.Close(); </code></pre> <p>The correct way would be this:</p> <pre><code>var connection = new SqlConnection(ConnectionString); try { connection.Open(); someCall (connection); } finally { connection.Close(); } </code></pre> <p>or</p> <pre><code>using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); someCall(connection); } </code></pre> <p>When your function <strong>returns a connection from a class method</strong> make sure you cache it locally and call its <code>Close</code> method. You'll leak a connection using this code for example:</p> <pre><code>var command = new OleDbCommand(someUpdateQuery, getConnection()); result = command.ExecuteNonQuery(); connection().Close(); </code></pre> <p>The connection returned from the first call to <code>getConnection()</code> is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.</p> <p>If you use <code>SqlDataReader</code> or a <code>OleDbDataReader</code>, 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.</p> <hr> <p>This article "<a href="http://msdn.microsoft.com/en-us/library/aa175863(SQL.80).aspx" rel="noreferrer">Why Does a Connection Pool Overflow?</a>" from MSDN/SQL Magazine explains a lot of details and suggests some debugging strategies:</p> <ul> <li>Run <code>sp_who</code> or <code>sp_who2</code>. These system stored procedures return information from the <code>sysprocesses</code> system table that shows the status of and information about all working processes. Generally, you'll see one server process ID (SPID) per connection. If you named your connection by using the Application Name argument in the connection string, your working connections will be easy to find.</li> <li>Use SQL Server Profiler with the SQLProfiler <code>TSQL_Replay</code> template to trace open connections. If you're familiar with Profiler, this method is easier than polling by using sp_who.</li> <li>Use the Performance Monitor to monitor the pools and connections. I discuss this method in a moment.</li> <li>Monitor performance counters in code. You can monitor the health of your connection pool and the number of established connections by using routines to extract the counters or by using the new .NET PerformanceCounter controls.</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload