Here I Am

Sql connection pooling deep details

Most of the .Net developers uses Sql Server as a main database server. But how many of them are aware of the Ado.Net connection pooling? As I see, most don’t know anything about it, some know but with inadequate knowledge, only so little percent know details about it. I don’t want to explain connection pooling mechanism from beginning, but I want to give you some important informations by Q&A which may have been behind the scenes.

Q: Is only connection string used as a base criteria when pooling connections?

A: Nope, if you use integrated security in the connection, your connections will be pooled per the user who logs on. Also sql connections pooled per the transaction context. This means that when you open the same sql connection within the same transaction context again, connection pooler gives you the same enlisted sql connection. 

Q: What are the defaults?

A: Unless you specify any other things, connection pooling is enabled, 0 is the minimum pool size, 100 is the maximum pool size, 0 is the connection life time(minimize that value in the load balanced clustered environments, 0 means maximum here),

Q: What does “Blocking Period” mean?

A: When there is a severe connection error(login, timeout, etc) on reaching to the sql server, an exception will be raised immediately, then within five seconds after the first exception occurs, subsequent connection attempts will be automatically discarded and exceptions will be raised. This period is called “blocking period”. This blocking period can be extended to another period that is twice as long as the previos period if the subsequent connection attempt fail again after the blocking period ends. This value can be extended  to five minutes as maximum.

Q: Sql connections pooled on what type of scopes?

A: Sql connections pooled  per process, per application domain, per connection string and per windows identity, if integrated security is used.

Q: Ordering keywords in the connection string is effective?

A: Yes, if you want to utilize connection pooling, you have to give the same keyword order everytime. An exact match is very important.

Q: When the connections in the pool will be closed?

A: If the inactivity timeout happens, connections will be closed. Unfortunately, I cannot give you the exact value due to the lack of this info in related Msdn pages. But if minimum pool size is specified, the connections may not be closed.

Q: What happens when a new connection attempt is done, but the connections in the pool reach to the maximum number?

A: The connection request is queued. But there is a time limit(default 15 seconds) for a connection request to be queued. If the connection pooler does not give a connection from the pool within this duration, an exception will be thrown.

Q: What is the best practice when opening and closing sql connections?

A: Use “using” statements. And do not call close, dispose on sql connection in the finalize method of your class.

Q: Sql Profiler audit events are effected when using connection pooling?

A:Yes, remember that login and logout events will not be raised, because connections are not actually closed.

Q: How can I clear the pool? Is it possible?

A: Yes, with Ado.Net 2.0 you can clear the pool by calling ClearAllPools and ClearPool methods.

Q: What must we know about incorrect Sql connection pooling scenarios?

A: Pools can be so fragmented, if integrated security is used in the case of lots of sql logins. Secondly when there are lots of databases, you cannot utilize the sql connection pooling effectively.