StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>From the MSDN (<a href="http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx</a>):</p> <p><em>When a SqlConnection object is requested, it is obtained from the pool if a usable connection is available. To be usable, a connection must be unused, <strong>have a matching transaction context or be unassociated with any transaction context</strong>, and have a valid link to the server. <br><br> The connection pooler satisfies requests for connections by reallocating connections as they are released back into the pool. If the maximum pool size has been reached and no usable connection is available, the request is queued. The pooler then tries to reclaim any connections until the time-out is reached (the default is 15 seconds). If the pooler cannot satisfy the request before the connection times out, <strong>an exception is thrown</strong>.</em></p> <p><strong>Translation:</strong> Check your transaction contexts... if you have a pool size of 10 connections, and 10 connections have been created under different transactions, you're screwed.</p> <p><strong><em>Note that a severed connection can be detected only after attempting to communicate with the server</strong>. If a connection is found that is no longer connected to the server, it is marked as invalid. Invalid connections are removed from the connection pool only when they are closed or reclaimed.<br><br> If a connection exists to a server that has disappeared, <strong>this connection can be drawn from the pool even if the connection pooler has not detected the severed connection</strong> and marked it as invalid. This is the case because the overhead of checking that the connection is still valid would eliminate the benefits of having a pooler by causing another round trip to the server to occur. When this occurs, <strong>the first attempt to use the connection will detect that the connection has been severed, and an exception is thrown</strong>.</em></p> <p><strong>Translation:</strong> You can't really rely on a connection to be connected? The article doesn't really explain how to handle this...</p> <p>You could try manually clearing the pool occasionally using ClearAllPools and ClearPool, but this still sounds like a band-aid to me, makes me cringe.</p> <p>The article also discusses Security Contexts, saying: <br> <em>After a SQL Server application role has been activated by calling the sp_setapprole system stored procedure, the security context of that connection cannot be reset. However, if pooling is enabled, the connection is returned to the pool, and an error occurs when the pooled connection is reused.</em></p> <p>I'm starting to wonder why I use connection pooling...</p> <p>And finally:<br> <strong><em>Pool Fragmentation Due to Integrated Security</strong><br> Connections are pooled according to the connection string plus the user identity. Therefore, if you use Basic authentication or Windows Authentication on the Web site and an integrated security login, you get one pool per user. Although this improves the performance of subsequent database requests for a single user, that user cannot take advantage of connections made by other users. It also results in at least one connection per user to the database server.</em></p> <p>So if you're using integrated security on a web app, you can fill up your connection pool if you have enough users.</p> <p>Without knowing more specifics on your application, it's hard to zoom in on what might be tripping you up, but hopefully this gives you some ideas where to look.</p> <p>HTH</p>

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